Combine multiple recs into one row for export HOW?

[BKE]

I'm working with two tables - the primary table having one
record per patient. The foreign key table has multiple
records (lab work) for each patient. Some patients have
just 1-2 records in the lab table, some have 10-20-30+. We
need to analyze this data in SPSS (statistical software)
and I need to put all of a patient's lab records into one
row for this analysis.

Example:
Table looks like this (ID#, lab date, lab test, results)
#11, 10/15/2003, ALT, 1000
#11, 10/15/2003, Viral Load, 1000
#11, 11/4/2003, ALT, 2000
#11, 11/4/2003, Viral Load, 2000
#14, 10/31/2003, ALT, 1500
#14, 11/3/2002, Viral Load, 1600

I want a query to produce rows like these:
#11, 10/15/2003, ALT, 1000, 10/15/2003, Viral Load, 1000,
11/4/2003, ALT, 2000, 11/4/2003, Viral Load, 2000

#14, 10/31/2003, ALT, 1500, 11/3/2002, Viral Load, 1600

How would I go about doing this? Thanks.
Billie

 

[Duane Hookom]

There is a small sample database at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane that
shows how to concatenate multiple records.

 

[BKE]

Fantastic. Thanks for sharing the link. I'll check it out.

Billie

=======================================

 

[Carlton]

I have a table that is the exact opposite. The table has
records with multiple date fields and currency values
next to each one. How would I query the table to put the
date fields in one column and the currency value field
next to them. The table has more fields than the 6 shown
below.

table: 11/1/03 10.00 11/2/03 10.32 11/3/03 10.61

what I want query to do:
11/1/03 10.00
11/2/03 10.32
11/3/03 10.61

 

[Duane Hookom]

You may need to use a union query.
SELECT Date1 as DateOf, Amt1 as Amount
FROM tblSpreadsheet
UNION ALL
SELECT Date2, Amt2
FROM tblSpreadsheet
UNION ALL
....until you have all your pairs of fields.