Union queries in VBA

shacket

Registered User.
Local time
Today, 03:31
Joined
Dec 19, 2000
Messages
218
I have a tblFamilys table (with Last Name, Address, etc.) and a tblIndividuals table (with First name and a whole bunch of info specific to each person) that are linked by FamID (primary key to tblFamilys). I can determine by a field in tblIndividuals whether the person is an adult or child and another tells me if they are male or female.

To make lists of families (including "John and Jane Doe") I use a query to find all married couples (2 tblIndividuals entries with the same FamID that are adults and opposite sex), then another query to grab all of the adults that are not in the first query (i.e. the single adults), and then a union query to mix them together.

What I want to be able to do is do the same thing in VBA. In other words, create a SQL statement in VBA that finds all people with X characteristic, joins husbands and wives into one entry ("John and Jane Doe") and creates a data set upon which I could base a report.

Any suggestions about how to do this? I am open to restructuring my information (i.e. my tables) if that would make things easier.

Thanks!

Dave Shacket
 
when my query statements get really complex, I just create the query in design view and then copy the SQL statement that is generated. So I would suggest create both queries and then your SQL statement will be the sql statement from the first query union the sql statement from the second (without the ";" at the end of the first query). Does this make sense?
 

Users who are viewing this thread

Back
Top Bottom