R
Robert Blair via AccessMonster.com
I have an Access 2000 database with two critical tables, tblClients and
tblChildren. I am trying to generate a query which will produce a one
record recordset which contains the parents' (tblClients) data and their
children's names and DOBs (tblChildren) for use with Word 2000 Mail Merge
documents. The desired recordset would include the needed client
information and the names and DOBs of each child as individual fields which
can be merged properly. Each client in tblClients only has one record (17
fields), each child in tblChild only has one record (4 fields) and they are
linked on the field, FileNumber. My problem is that some clients have no
children, others have many, just like in the real world.
My Select query ends up like this:
File Client Address Child DOB
5001 George 123 Main Harry 12/04/85
5001 George 123 Main Wanda 10/12/91
5001 George 123 Main Sally 07/22/93
How do I generate a query so that, if I enter FileNumber as my query
parameter, I only get one record in my recordset(as opposed to the number
of records equal to the number of children)? Also, I need to be able to
count the children because my Word document needs to know how many there
are.
In other words, I need the result of the query to be:
File Client Address Child DOB Child DOB Child DOB
5001 George 123 Main Harry ... Wanda ... Sally ...
Also, does the "join property" affect the result of a SELECT query?
tblChildren. I am trying to generate a query which will produce a one
record recordset which contains the parents' (tblClients) data and their
children's names and DOBs (tblChildren) for use with Word 2000 Mail Merge
documents. The desired recordset would include the needed client
information and the names and DOBs of each child as individual fields which
can be merged properly. Each client in tblClients only has one record (17
fields), each child in tblChild only has one record (4 fields) and they are
linked on the field, FileNumber. My problem is that some clients have no
children, others have many, just like in the real world.
My Select query ends up like this:
File Client Address Child DOB
5001 George 123 Main Harry 12/04/85
5001 George 123 Main Wanda 10/12/91
5001 George 123 Main Sally 07/22/93
How do I generate a query so that, if I enter FileNumber as my query
parameter, I only get one record in my recordset(as opposed to the number
of records equal to the number of children)? Also, I need to be able to
count the children because my Word document needs to know how many there
are.
In other words, I need the result of the query to be:
File Client Address Child DOB Child DOB Child DOB
5001 George 123 Main Harry ... Wanda ... Sally ...
Also, does the "join property" affect the result of a SELECT query?