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