Query syntax needed (1 Viewer)

[shacket]

I have the two tables:

tblFamilys lists last names of various families (key is FamID)

tblIndividuals lists first names of individuals linked to their last name (in tblfamilys) through FamID

Some FamIDs have two people in tblIndividuals (a married couple) and some only have one (a single person). I need to create a list of each family, i.e.:

John and Jane Doe
Richard Smith
Sam and Deanna Casper

I had previously done this by creating a query of married people (with two tblIndividuals - thereby eliminating single people), then another query of individuals (by listing people that are not in the query of married people) and then unioning those two queries together.

What I am wondering is if there is a way to do this in one query (SQL statement) - what the SQL would be.

Any help would be greatly appreciated.

 

[Rob.Mills]

Do you have to put the first names in a 2nd table?

Reason I'm asking is I've setup many tables where I had two fields; one for each first name. Then I set a field in a query like this:

First1 & " " & iif(isnull(First2),"","and " & First2 & " ") & Last

That would create the result you're looking for. I'm not sure how to do it in split tables.

 

[shacket]

Yes, I do. The reason is that I am tracking a lot of information that is individual-specific. In other words, there are many fields in tblIndividuals that track information regarding each individual - which are generally different even between husband and wife. (Kids are actually listed in tblIndividuals as well, but that's another story!)

Any other thoughts?

 

[pottera]

Don't know how you would do this in an SQL statement.

When you create, or modify, these records could you create an index file - eg:

FamID, FirstName1, FirstName2, FirstName3,.....

may require a bit more maintenance, but would make that particular report easier.

 

[Rob.Mills]

In the case that you need two tables the only way I could think of is to use VBA. And I'm not totally sure how to go about it. I've got an idea but I'll have to work with it myself. But don't hold out for me.