Exporting Data From a One to Many Relationship

[Yoon]

I'm using access 2000 and I have the following problem.

I have data in two tables linked with a one to many
relationship. TBL1 contains the member information. TBL4
contains the journal information and can have up to 27
records for one member. We use an invoice number to link
the two tables.

I need to include several fields from TBL1 And all the
matching records by invoice number from TBL4.

The problem is that I need to export the data as a flat
table, I can't figure out how to get all of the data in
one record per member.

Please help!

 

[John Nurick]

Hi Yoon,

If you want the exported table to contain the fields from TBL1 plus 27
fields for journal information, start by building a crosstab query on
TBL4 that tabulates the journal information against MemberID (or
whatever is the key field). Then build a second query that joins TBL1
and the crosstab query on MemberID to return the fields from TBL1 plus
the fields from the crosstab.

If you want the exported table to contain one field into which all the
information from TBL4 is concatenated, use the fConcatChild() function
at http://www.mvps.org/access.

If you need something else, start with one or other of the above.