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
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