The SQL gets a little knarly, but here's the idea: Add the individualtable twice, filtering
once for each ordinal name (1 and 2), aliasing the tables as i1 and i2.
The use of the Plus (+) operator will cause the ampersand to dissappear when there is no
second name.
SELECT i1.FirstName & (" & " + i2.FirstName) & " " & familytable.FamilyName As FullName
FROM (familytable INNER JOIN [select FamilyID, FirstName from individualtable
where IndividualnameID=1]. AS i1 ON familytable.FamilyID = i1.FamilyID)
INNER JOIN [select FamilyID, FirstName from individualtable
where IndividualnameID=2]. AS i2 ON familytable.FamilyID = i2.FamilyID
I ran this on test tables and it works, but it assumes that the data in the Individual
table looks like this ...
IndividualnameID FamilyID FirstName
1 1 Danny
2 1 Masha
1 2 Lamont
2 2 Birtha
etc.
--
Danny J. Lesandrini
dl*********@hotmail.com http://amazecreations.com/datafast/
"Kevin" <kz********@marcct.org> wrote ...
Hello,
How do I concatenate two fields in the same table... For example, I
have two tables, first one is called familynametable that has a
familyID and familyname field. This table is connected to a
individualnametable that contains three fields: individualnameID,
FamilyID (to link to familytable) and FirstName.
In the familytable, FamilyID=1, FamilyName=Doe,
In the individualtable there are two records:
IndividualnameID=2, FamilyID=1, and FirstName=Jane
IndividualnameID=2, FamilyID=1, and FirstName=John
We are looking for a way to join by FamilyID the two first names for
mailing purposes:
Jane & John Doe
I have been able to concatenate the fields but I am getting the
following result:
Jane,John Doe
Which is not good for mailing purposes...
Any suggestions?
Thanks
Kevin
