Query taking too long to load, crashing

[diddydustin]

Hey guys, I'm trying the following query:

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];

I am very confused on why the query causes Access to crash. It is a complex query-- basically I am trying to combine three queries I used to create three seperate reports into one. Is there a cleaner approach to doing this? If not, why does this not work?

Thank you
Dustin

 

[JonFer]

Please show the SQL for the 3 underlying queries. Also, are the tables internal or linked? If linked, what DB are they in?


John

http://www.interautomateconsulting.com

 

[diddydustin]

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_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_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;


All tables are linked to EMR_BE.mdb.  All of the underlying queries work, however, when I try to run the main query it sits and thinks forever and eventually I have to kill Access. 

Thanks for the help
Dustin

 

[JonFer]

1. You can remove the unnecessary WHERE clause in EMR_TimeUnion - it is the same as the ON criteria for the 2nd INNER JOIN

2. Add indexes - make sure the fields that you are joining on are indexed.

3. Check the space available on your C:\ drive or whichever drive the TEMP folder is located. MS Access writes temporary files there and will just wait for space to become available if there isn't any. You can change the temp folder to another drive if this is the problem.



John

http://www.interautomateconsulting.com

 

[cheerio]

On a point of detail I would change

Sum(EMR_TimeUnion.[Sum Of Time_Total]/3600) AS [SumOfSum Of Time_Total]

to

Sum(EMR_TimeUnion.[Sum Of Time_Total])/3600 AS [SumOfSum Of Time_Total]

The distincion is that you divide the figure for every row by 3600 and SUM whereas I would SUM all the rows and divide the answer once by 3600.