append data to field based on related table

[Amanda Byrne]

I have a Household table that holds one record for each address and a People
table with a related Household ID field so that each person has an associated
address, and multiple people have the same address- this way I don't have to
worry about duplicates when I'm preparing a mailing.

I need to populate 2 fields in the Household table based on the related
data in the People table- Long Salutation and Short Salutation. These fields
will be used as the header line and greeting line for mailings. The values
for the salutation fields will vary a bit based on the names from the People
table (i.e. the Long Salutation might read "John and Jane Smith" if they
share a last name, or "John Smith and Jane Doe" if they don't)

but not the slightest clue how to begin...

help much appreciated, Amanda

 

[Michel Walsh]

Hi,


Assuming your table is like:


HouseholdID, FirstName, LastName ' fields
1010 John Smith
1010 Jane Smith
1011 John Doe
1012 John Smith
1012 Jane Doe 'data sample




SELECT a.FirstName & " " & ïif(b.LastName Is Null OR b.LastName <>
a.LastName, a.LastName, Null)
& iif(b.LastName Is Null, Null, " and " ) & (b.firstName + " " +
b.lastName)
FROM mytable AS a LEFT JOIN myTable As b
ON a.householdID = b.householdID
AND a.FirstName < b.FirstName


should do.

If the table design is different, that may make the SELECT clause much much
painful, but the idea would be the same: make an inner join on the
householdid and with an inequality on the name.

That also assumes there is no more than 2 records for one householdID (may
have just one record, though).



Hoping it may help,
Vanderghast, Access MVP