need flat file of four 1:M tables

[Theresa via AccessMonster.com]

Hello,
I hope this is the correct group. I didn't see an exporting group.
I am using Access 2002. I have 4 tables (Infants (1), Clinic Visits
(many), HIV tests (many), ARV Therapy (1)). The Infants table is the main
(primary) table, the other three have either a many or one relationship with
the main table. I need to create a flat file (so the researcher can import
that into SAS to analyze) from the above tables, with one line for each
infant. I need to sort the visits and tests by date. I don't know the
maximum number of clinic visits or HIV tests. Ideally there would only be
one test per infant, but if the blood was lost, or unreadable, then there
would be at least two tests.
Some example lines would be: [Is my example correct?]

infant1, visit 1, test1, visit 2, test 2,, arv
infant2, visit 1, test1, visit 2, test 2, visit 3,
infant3, visit 1, test1, visit 2,,, arv
infant4, visit 1, test1, visit 2, test 2,,

Does anyone have some advice for me on how to accomplish this?

Thanks!
Theresa

 

[Ronald Roberts]

Create a query collecting all of the information your require
in the format required.
Then export the query.
This came from help. Lookup TransferText.

DoCmd.TransferText acExportDelim, "Standard Output", _
"External Report", "C:\Txtfiles\April.doc"

HTH
Ron

 

[Roger Carlson]

You will probably need to use code to do this. On my website
(www.rogersaccesslibrary.com) is a small sample database called
"NormalizeDenormalize.mdb" which shows how to do this. You'll want to use
the Denormalize example.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Theresa via AccessMonster.com]

Roger,
I think your Denormalize code will work for me. TransferText won't
work as I can't get my query to work correctly. Anyway, your code should
with a lot of modification. In my example lines, visit 1 is actually a
record with 10 fields, test 1 is a record with 22 fields, and ARV is a record
with 6 fields.
I'm trying to change your function MaxNumberOfFields, but I can't figure
out how to use it with a table that has a 2 field primary key. Can I use
"GROUP BY Visits.InfantID, Visits.Twin "?

Thanks!
Theresa