need flat file of four 1:M tables

  • Thread starter Thread starter Theresa via AccessMonster.com
  • Start date Start date
T

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
 
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,
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
 
Back
Top