Combing three reports, query/report crashes-- what's best method?

[diddydustin]

Hey guys,

I have three reports: EMR_TimeReport, EMR_SheetsReport, EMR_UPSH.

EMR_TimeReport uses this query:
EMR_Time_Report:
TRANSFORM Val(Nz(Sum([Sum Of Time_Total]),0))/3600 AS TheValue
SELECT EMR_TimeUnion.Employee,
EMR_TimeUnion.Machine,
EMR_TimeUnion.Date,
EMR_TimeUnion.Shift,
EMR_TimeUnion.Time_Code, Sum([Sum Of Time_Total])/3600 AS AllTimes
FROM EMR_TimeUnion INNER JOIN EMR_TimeCodeList ON EMR_TimeUnion.Time_Code = EMR_TimeCodeList.Code
GROUP BY EMR_TimeUnion.Employee,
EMR_TimeUnion.Machine,
EMR_TimeUnion.Date,
EMR_TimeUnion.Shift,
EMR_TimeUnion.Time_Code
PIVOT IIf(EMR_TimeUnion.[Time_Code]<100,"Downtime",EMR_TimeCodeList.[Desc]);

EMR_TimeUnion:

SELECT EMR_Base.Employee,
EMR_TotalGraphicTime.[Sum Of Time_Total], EMR_TotalGraphicTime.Time_Code,
EMR_TimeCodeList.Desc,
EMR_Base.Machine,
EMR_Base.Date,
EMR_Base.Shift
FROM (EMR_Base INNER JOIN EMR_TotalGraphicTime ON EMR_Base.Time_ID = EMR_TotalGraphicTime.Time_ID) INNER JOIN EMR_TimeCodeList ON EMR_TotalGraphicTime.Time_Code = EMR_TimeCodeList.Code
WHERE (((EMR_TotalGraphicTime.Time_Code)=[EMR_TimeCodeList].

));

EMR_UPSH uses:

SELECT EMR_SheetsUnion.TotalSheets, 
Sum(EMR_TimeUnion.[Sum Of Time_Total]/3600) AS [SumOfSum Of Time_Total], 
EMR_TimeUnion.Machine, 
EMR_TimeUnion.Shift, 
EMR_TimeUnion.Date, 
EMR_TimeUnion.[Sum Of Time_Total], 
EMR_TimeUnion.Time_Code, 
EMR_Time_Report.Downtime, 
EMR_Time_Report.Makeready, 
EMR_Time_Report.[Run Time]
FROM EMR_Time_Report 
INNER JOIN (EMR_TimeUnion INNER JOIN EMR_SheetsUnion ON EMR_TimeUnion.Machine = EMR_SheetsUnion.Machine) ON EMR_Time_Report.Machine = EMR_TimeUnion.Machine
GROUP BY 
EMR_SheetsUnion.TotalSheets, 
EMR_TimeUnion.Machine, 
EMR_TimeUnion.Shift, 
EMR_TimeUnion.Date, 
EMR_TimeUnion.[Sum Of Time_Total], 
EMR_TimeUnion.Time_Code, 
EMR_Time_Report.Downtime, 
EMR_Time_Report.Makeready, EMR_Time_Report.[Run Time];
SELECT EMR_SheetsUnion.TotalSheets, 
Sum(EMR_TimeUnion.[Sum Of Time_Total]/3600) AS [SumOfSum Of Time_Total], 
EMR_TimeUnion.Machine, 
EMR_TimeUnion.Shift, 
EMR_TimeUnion.Date, 
EMR_TimeUnion.[Sum Of Time_Total], 
EMR_TimeUnion.Time_Code, 
EMR_Time_Report.Downtime, 
EMR_Time_Report.Makeready, 
EMR_Time_Report.[Run Time]
FROM EMR_Time_Report 
INNER JOIN (EMR_TimeUnion INNER JOIN EMR_SheetsUnion ON EMR_TimeUnion.Machine = EMR_SheetsUnion.Machine) ON EMR_Time_Report.Machine = EMR_TimeUnion.Machine
GROUP BY 
EMR_SheetsUnion.TotalSheets, 
EMR_TimeUnion.Machine, 
EMR_TimeUnion.Shift, 
EMR_TimeUnion.Date, 
EMR_TimeUnion.[Sum Of Time_Total], 
EMR_TimeUnion.Time_Code, 
EMR_Time_Report.Downtime, 
EMR_Time_Report.Makeready, EMR_Time_Report.[Run Time];

EMR_Sheets_Union:

SELECT EMR_Base.Employee, 
Sum(EMR_Graphic_Hour_Detail_JobID.[Sum Of Load_Count]) AS TotalSheets, 
EMR_Base.Date, 
EMR_Base.Shift, 
EMR_Base.Machine
FROM EMR_Base INNER JOIN EMR_Graphic_Hour_Detail_JobID ON EMR_Base.Job_ID = EMR_Graphic_Hour_Detail_JobID.Job_ID
GROUP BY EMR_Base.Employee, 
EMR_Base.Date, 
EMR_Base.Shift, 
EMR_Base.Machine;

And EMR_SheetsReport uses just the EMR_SheetUnion.

I have been asked to take these three reports and combine them into one, easy to read format. Basically, I want a report of the Machine's production-- the sheets produced, the times recorded, and the UPSH (unit per scheduled hour, simply sheets / total time) in one report. I have tried just putting all three queries basically into one and the query would simply crash on load every single time. I posted about this in the queries forum ([URL unfurl="true"]http://www.tek-tips.com/viewthread.cfm?qid=912596)[/URL] but really had no luck.

How should I be approaching this problem? In combining the queries, is there something I am missing? Also, currently the three reports I have are queried by Employee-- I want to rid of this and only return the results by Machine for the "one report".

Thanks
Dustin

 

[diddydustin]

Note:

A sample query for EMR_UPSH returns around 10,000 records.

 

[diddydustin]

Err, SORRY--

A sample query for EMR_UPSH returns around 400 records.

 

[diddydustin]

Okay, currently I am working with something like this that does not crash-- it is combining the EMR_UPSH and the EMR_TimeReport:

SELECT EMR_UPSH.Machine, EMR_UPSH.Shift, EMR_UPSH.Date, EMR_UPSH.SumOfTotalSheets, EMR_UPSH.[SumOfSum Of Time_Total], EMR_Time_Report.Downtime, EMR_Time_Report.Makeready, EMR_Time_Report.[Run Time], EMR_Time_Report.Time_Code
FROM EMR_UPSH INNER JOIN EMR_Time_Report ON EMR_UPSH.Machine = EMR_Time_Report.Machine;


However, in the report the total number of sheets for each machine will be repeated multiple times with each time. I want a report that shows: for this day, the machine gave x many sheets, x downtime, x runtime, and xmakeready time. Why am i getting some many results back with my query, and the data is not summed properly?