How do I create single report using multiple queries with some of the same IDs?

[jjz0512]

I'm trying to create a report that take data from 3 queries that represents the same Ids or some with the same IDs.

The report need to be built based on this query;
SELECT DISTINCT [ITXM BLOOD Data].MRN, [ITXM BLOOD Data].SubLocation AS [Sub Location], [ITXM BLOOD Data].ProductName AS [Product Name], [ITXM BLOOD Data].DIN AS [Unit #], [ITXM BLOOD Data].ProductCode AS [Product #], Format([ITXM Blood Data].[OrderDate]+[ITXM Blood Data].[OrderTime],"mm/dd/yy hh:nn") AS [Issued D/T]
FROM [ITXM BLOOD Data] INNER JOIN [H1PD XRAY Data] ON [ITXM BLOOD Data].MRN = [H1PD XRAY Data].MRN
WHERE ((([ITXM Blood Data].OrderDate+[ITXM Blood Data].OrderTime)>=#6/7/2015 12:0:0# And ([ITXM Blood Data].OrderDate+[ITXM Blood Data].OrderTime)<=#6/8/2015 23:59:0#) And (([H1PD XRAY Data].OrderDate+[H1PD XRAY Data].OrderTime)>=#6/8/2015 0:1:0# And ([H1PD XRAY Data].OrderDate+[H1PD XRAY Data].OrderTime)<=#6/8/2015 23:59:0#) And ((DateDiff("n",[ITXM BLOOD Data].OrderDate+[ITXM BLOOD Data].OrderTime,[H1PD XRAY Data].OrderDate+[H1PD XRAY Data].OrderTime))>=0 And (DateDiff("n",[ITXM BLOOD Data].OrderDate+[ITXM BLOOD Data].OrderTime,[H1PD XRAY Data].OrderDate+[H1PD XRAY Data].OrderTime))<721))
ORDER BY [ITXM BLOOD Data].MRN, Format([ITXM Blood Data].[OrderDate]+[ITXM Blood Data].[OrderTime],"mm/dd/yy hh:nn"), [ITXM BLOOD Data].ProductName, [ITXM BLOOD Data].DIN;

I need to add the records from these two queries joined by MRN when available.
SELECT DISTINCT [ITXM BLOOD Data].MRN, Format([H1PD XRAY Data].[OrderDate]+[H1PD XRAY Data].[OrderTime],"mm/dd/yy hh:nn") AS [CXR Order D/T]
FROM ([ITXM BLOOD Data] INNER JOIN [H1PD XRAY Data] ON [ITXM BLOOD Data].MRN = [H1PD XRAY Data].MRN) INNER JOIN ITxM_Data ON [H1PD XRAY Data].MRN = ITxM_Data.MRN
WHERE ((([ITXM Blood Data].OrderDate+[ITXM Blood Data].OrderTime)>=#6/7/2015 12:0:0# And ([ITXM Blood Data].OrderDate+[ITXM Blood Data].OrderTime)<=#6/8/2015 23:59:0#) And (([H1PD XRAY Data].OrderDate+[H1PD XRAY Data].OrderTime)>=#6/8/2015 0:1:0# And ([H1PD XRAY Data].OrderDate+[H1PD XRAY Data].OrderTime)<=#6/8/2015 23:59:0#) And ((DateDiff("n",[ITXM BLOOD Data].OrderDate+[ITXM BLOOD Data].OrderTime,[H1PD XRAY Data].OrderDate+[H1PD XRAY Data].OrderTime))>=0 And (DateDiff("n",[ITXM BLOOD Data].OrderDate+[ITXM BLOOD Data].OrderTime,[H1PD XRAY Data].OrderDate+[H1PD XRAY Data].OrderTime))<=721))
ORDER BY [ITXM BLOOD Data].MRN, Format([H1PD XRAY Data].[OrderDate]+[H1PD XRAY Data].[OrderTime],"mm/dd/yy hh:nn");

SELECT DISTINCT [ITXM BLOOD Data].MRN, Format([IMGC IMAGE Data].[ImageDate]+[IMGC IMAGE Data].[ImageTime],"mm/dd/yy hh:nn") AS [CXR Image D/T]
FROM ([ITXM BLOOD Data] INNER JOIN [IMGC IMAGE Data] ON [ITXM BLOOD Data].MRN = [IMGC IMAGE Data].MRN) INNER JOIN ITxM_Data ON [IMGC IMAGE Data].MRN = ITxM_Data.MRN
WHERE ((([ITXM Blood Data].OrderDate+[ITXM Blood Data].OrderTime)>=#6/7/2015 12:0:0# And ([ITXM Blood Data].OrderDate+[ITXM Blood Data].OrderTime)<=#6/8/2015 23:59:0#) And (([IMGC IMAGE Data].ImageDate+[IMGC IMAGE Data].ImageTime)>=#6/8/2015 0:1:0# And ([IMGC IMAGE Data].ImageDate+[IMGC IMAGE Data].ImageTime)<=#6/8/2015 23:59:0#) And ((DateDiff("n",[ITXM BLOOD Data].OrderDate+[ITXM BLOOD Data].OrderTime,[IMGC IMAGE Data].ImageDate+[IMGC IMAGE Data].ImageTime))>=0 And (DateDiff("n",[ITXM BLOOD Data].OrderDate+[ITXM BLOOD Data].OrderTime,[IMGC IMAGE Data].ImageDate+[IMGC IMAGE Data].ImageTime))<=751))


ORDER BY [ITXM BLOOD Data].MRN, Format([IMGC IMAGE Data].[ImageDate]+[IMGC IMAGE Data].[ImageTime],"mm/dd/yy hh:nn");

Here's the report I'm trying to get;

|------------------------------ Data from query 1 --------------------------------|--|---- Query 2 ----|-|---- Query 3 ----|
MRN...SubLocation..Product Name....Unit #........Product #...Issued D/T.........CXR Order D/T......CXR Image D/T
3040..3F................Plasma..............W00254 6...E2720V00...06/08/15 09:51...06/08/15 11:45.....06/08/15 11:50
3040..3F................Plasma..............W00257 1...E2720V00...06/08/15 09:51
3424..CLHA...........Red Blood Cells..W092091...E0382V00...06/07/15 17:23...06/08/15 04:01
3424..CLHA...........Red Blodd Cells..W092051...E0382C00...06/08/15 09:52...06/08/15 10:21
3561..10W............Platelets.............W092187 ...E0937V00...06/08/15 08:21...06/08/15 12:54.....06/08/15 14:21

thanks, Jay

[June7]

Did you consider report/subreport arrangement?

Did you try building another query that joins the 3 queries?

[jjz0512]

No, I haven't. don't know how to build a report/subreport arrangment. I built a seperate report for each query, but don't know how to link them together. I'nm not sure how to build a query either that link the 3 of them together when query 2 & 3 don't have data for query 1 that the base of the report. Each query does have related MRN's in them, but not all that are in query 1.

Hope this helps. Your help is much appreciated !

Thanks agian,
Jay

[IrogSinta]

You can use a single report for this. Just combine your queries using Left Joins so that even though Query2 or Query3 do not have data for a particular MRN, your records from Query1 will still show. Paste the following SQL string in the recordsource property of your report. Just change the names of the queries accordingly:

SELECT Query1.*, Query2.[CXR Order D/T], Query3.[CXR Image D/T] FROM (Query1 LEFT JOIN Query2 ON Query1.MRN = Query2.MRN) LEFT JOIN Query3 ON Query1.MRN = Query3.MRN;

If you look at the query design of the report's RecordSource (by clicking on the ellipses), it would look like this:



With the above as your recordsource, you can manually add all the fields to your report. Or if you prefer, you can use the same SQL statement as the source for a 4th query and use that to create your Report.

Ron

[jjz0512]

Thanks IrogSInta, that would be great, but I previously tried this and it brings back extra rows that support each MRN record in each table. For example, I only have 2 records for an MRN in Query 1, but have 3 records in both Query 2 & 3, so I should only see a total of 3 records. With this sql statement, i get 18 records.

Jay

[IrogSinta]

Apparently what you tried was a Cartesian Join. This is where you just added these 3 queries without joining them. So compared to the diagram I gave you, yours would have had the 3 queries but without the lines and arrows between them. Whenever you do that you come up with a Cartesian Product which would give you the various combinations of all the tables or queries in your join. In your case, it would have returned 2 x 3 x 3 = 18 records.

Ron

[June7]

Does one of these queries have all possible MRN values? If so, use that as the 'parent' dataset and join the other two to it. If not, and you want to show all MRN values, need a master dataset of all MRN values and join the 3 queries to that.

[jjz0512]

IrogSinta,
I had the query as you showed in your diagram. Can I create a query that will give me the result I need?

June7,
Query 1, has all the records i want to find, or parent. Query 2 & 3 have supporting records for some of the records found in Query 1. I then create a new query like what IrogSinta said. I tried to show in my original post, how I need to see the data.

[June7]

You said 2 records in query1 and 3 records in each query2 and query3 and query1 is the 'parent'.

If MRN is unique in each query, this means the query that joins query2 and query3 each to query1 on only the MRN fields should show only 2 records.

If MRN is not unique in any of the queries, the query (with joins on MRN fields) could show up to 6 records.

If multiple 1-to-many relationships are involved, probably should do report/subreports arrangement. Build the same way as form/subform.

[jjz0512]

June7,
I have all the queries have unique MRN in all 3, but returning 18 records for MRN 784168942, in stead of only 3. 2 records in query 1, 3 records are found in query 2 & 3. Here's a copy of the output for all 3 queries;

MRN	Sub Location	Product Name	Unit #	Product #	Issued D/T	CXR Order D/T	CXR Image D/T
784168942	CT11	Red Blood Cells (Crossmatch RBC)	W084915001763	E0382V00	06/09/15 06:40	06/09/15 08:20	06/09/15 08:20
784168942	CT11	Red Blood Cells (Crossmatch RBC)	W084915001763	E0382V00	06/09/15 06:40	06/09/15 09:10	06/09/15 09:10
						06/09/15 10:03	06/09/15 10:03

Each query is based on a table that supports each. Here' an example of how I'm using these 3 queries in 1 query.

SELECT [ITXM BLOOD Data].MRN, [ITXM BLOOD Data].SubLocation AS [Sub Location], [ITXM BLOOD Data].ProductName AS [Product Name], [ITXM BLOOD Data].DIN AS [Unit #], [ITXM BLOOD Data].ProductCode AS [Product #], Format([ITXM Blood Data].[OrderDate]+[ITXM Blood Data].[OrderTime],"mm/dd/yy hh:nn") AS [Issued D/T]

FROM [ITXM BLOOD Data] INNER JOIN [H1PD XRAY Data] ON [ITXM BLOOD Data].MRN = [H1PD XRAY Data].MRN

WHERE ((([ITXM Blood Data].OrderDate+[ITXM Blood Data].OrderTime)>=#6/8/2015 12:0:0# And ([ITXM Blood Data].OrderDate+[ITXM Blood Data].OrderTime)<=#6/9/2015 23:59:0#) And (([H1PD XRAY Data].OrderDate+[H1PD XRAY Data].OrderTime)>=#6/9/2015 0:1:0# And ([H1PD XRAY Data].OrderDate+[H1PD XRAY Data].OrderTime)<=#6/9/2015 23:59:0#) And ((DateDiff("n",[ITXM BLOOD Data].OrderDate+[ITXM BLOOD Data].OrderTime,[H1PD XRAY Data].OrderDate+[H1PD XRAY Data].OrderTime))>=0 And (DateDiff("n",[ITXM BLOOD Data].OrderDate+[ITXM BLOOD Data].OrderTime,[H1PD XRAY Data].OrderDate+[H1PD XRAY Data].OrderTime))<721))

ORDER BY [ITXM BLOOD Data].MRN, Format([ITXM Blood Data].[OrderDate]+[ITXM Blood Data].[OrderTime],"mm/dd/yy hh:nn"), [ITXM BLOOD Data].ProductName, [ITXM BLOOD Data].DIN;

[June7]

I am not seeing 3 datasources in that query - only 2. Is that just a repeat of one of the queries from original post?

Show your SQL statement that includes the 3 queries.

If you want to provide db for analysis, follow instructions at bottom of my post.

[jjz0512]

How can I resolve, through a report/subreport arrangement? Will I be able to align Reports for Query 2 & 3 to the corresponding MRNs from QUery 1? Like in my example report that shown in the post at 1:30pm?

[June7]

Subforms and subreports have properties (Master/Child Links) that will synchronize related records.

[IrogSinta]

I don't see why you would get 18 record if you are doing the join properly. Can you post the SQL string you are using to join your 3 queries?

[jjz0512]

IrogSinta,
Here's my SQL; SELECT ITxM_Data.*, H1PD_Data.[CXR Order D/T], IMGC_Data.[CXR Image D/T]
FROM (ITxM_Data LEFT JOIN H1PD_Data ON ITxM_Data.MRN = H1PD_Data.MRN) LEFT JOIN IMGC_Data ON ITxM_Data.MRN = IMGC_Data.MRN;

I'm getting repeated records for each queries result set.