"Collate records" - One report - two tables

[kptasteve]

I have two tables : one "SOAP" and another "SoapQuick" both tables have a field [soapdate] and a relation field [SubscriberID]. However the two tables have different text fields that contain descriptions of patient encounters. I need to be able to print a comprehensive list of encounters by combining the two tables, but not into the same row of data. Example

Table Soap:
SubscriberID = 3
soapdate = 03-13-2003
Encounterdata = Patient went to MD today

Table SoapQuick
SubsciberID = 3
SoapDate = 03-14-2003
QuickData = Patient was discharged

I would like to have the report layout then display as follows:

Begin Report

SubscriberID 3
Soapdate 03-13-2003 EncounterData Patient went to MD today
Soaddate 03-14-2003 QuickData Patient was discharged

End of report

Thanks

Steve Marcum PT
Programmer

 

[GingerR]

use a UNION query (i'm making this up without testing so you may have to tweak):

"Select Soap.SubscriberID, Soap.SoapDate, "Encounter Data" as Title, Soap.EncounterData as Action UNION 
Select SoapQuick.SubscriberID, SoapQuick.SoapDate, "QuickData" as Title, Soap.QuickData as Action;"

then you will end up with 4 columns:
SubID SoapDate Title Action
3 03-13-2003 Encounter Data Patient went to MD today
3 03-14-2003 Quick Data Patient was discharged

seemed like 'Encounter Data' and 'Quick Data' was important to you so i included it.

now you can easily use this query in all other queries, reports, etc.

g