Exporting relational data to a flat file

[UHNSTrust]

I have a query that produces data on many lines due to a one to many relationship in the table design. What I have been asked to produce is a flat file with the 'many' part of the relationships as additional fields on the end of the record. They want it in Excel or CSV file.

This should maker it clearer (I hope);

Query data:
Name DOB AttendanceDate Treatment
Fred Smith 06/07/1973 01/02/09 Q23
Fred Smith 06/07/1973 01/02/09 Q24
Fred Smith 06/07/1973 01/02/09 Q25
Fred Smith 06/07/1973 01/02/09 Q26
Jimmie Jones 01/02/1945 02/02/09 S22
Jimmie Jones 01/02/1945 02/02/09 S23

What is required;
Name DOB AttendanceDate Treatment1 Treatment2 Treatment3 Treatment4
Fred Smith 06/07/1973 01/02/09 Q23 Q24 Q25 Q26
Jimmie Jones 01/02/1945 02/02/09 S22 S23

I am not sure how to do this. Can anyone help with advice, a link to something similar or code?

Thanks in advance

Jonathan

 

[HarleyQuinn]

faq701-3499 should help to get you started.

Hope this helps

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[SkipVought]

Hi,

If it were me, I'd do the entire thing in Excel.

First, using Data > Import External Data... I'd make the query to import the data into Sheet Data, as you have in your first table example. Use Insert > Name > Create - Create names in TOP row.

Then on Sheet Report, I'd do another query of Distince values of Name, DOB, AttendanceDate

Then I'd use this formula in D2; drag across and down
[tt]
D2: =INDEX(OFFSET(Data!$A$1,MATCH($A2,Name,0),3,COUNTIF(Name,$A2)),COLUMN()-3,1)
[/tt]
Use Conditional Formatting to "hide" the #REF! errors where the formula exceeds the name occurence.



Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[dhookom]

If you want separate columns, first create a ranking query like:

SELECT qselJonathan.Name, qselJonathan.DOB, qselJonathan.AttendanceDate, qselJonathan.Treatment, "Treatment" & Count([qselJonathan].[Name]) AS Trtmnt
FROM qselJonathan INNER JOIN qselJonathan AS qselJonathan_1 ON (qselJonathan.AttendanceDate = qselJonathan_1.AttendanceDate) AND (qselJonathan.DOB = qselJonathan_1.DOB) AND (qselJonathan.Name = qselJonathan_1.Name)
WHERE (((qselJonathan_1.Treatment)<=[qselJonathan].[Treatment]))
GROUP BY qselJonathan.Name, qselJonathan.DOB, qselJonathan.AttendanceDate, qselJonathan.Treatment;

qgrpForCrosstab
[tt]
Name DOB AttDate Treatment Trtmnt
Fred 6/7/1973 1/2/2009 Q23 Treatment1
Fred 6/7/1973 1/2/2009 Q24 Treatment2
Fred 6/7/1973 1/2/2009 Q25 Treatment3
Fred 6/7/1973 1/2/2009 Q26 Treatment4
Jimmie 1/2/1945 2/2/2009 S22 Treatment1
Jimmie 1/2/1945 2/2/2009 S23 Treatment2
[/tt]
Then create a crosstab with this SQL

TRANSFORM First(qgrpForCrosstab.Treatment) AS FirstOfTreatment
SELECT qgrpForCrosstab.Name, qgrpForCrosstab.DOB, qgrpForCrosstab.AttendanceDate
FROM qgrpForCrosstab
GROUP BY qgrpForCrosstab.Name, qgrpForCrosstab.DOB, qgrpForCrosstab.AttendanceDate
PIVOT qgrpForCrosstab.Trtmnt;

Duane
Hook'D on Access
MS Access MVP

 

[UHNSTrust]

Thank you all for your responses.

HarleyQuinn - Thanks for this. It needs some work to develop this because of the unknown amount of treatments.

Skip - That is very useful for other stuff. I need to automate this so it might get a bit technical.

Duane - This seems very simple. Thanks for that. I will have a go and feed back.

Thanks again.

Jonathan

 

[dhookom]

There is another function at faq701-4233 which combines all of the treatments into a single expression.

Duane
Hook'D on Access
MS Access MVP