I am writing my first little Access 2003 application. I have two queries that I cannot figure out how to combine. What I am looking for is to get all the routes numbers in a date range, count the total rows per route in the date range, and count how many were late. I have these two queries that get me part of the way there, just can't figure out how to combine them in an Access query.
PARAMETERS StartDate DateTime, EndDate DateTime;
SELECT [ROUTE #], Count(*) AS Total
FROM [On time performace audits]
WHERE ((([On time performace audits].DATE) Between [StartDate] And [EndDate]))
GROUP BY [Route #];
PARAMETERS StartDate DateTime, EndDate DateTime;
SELECT [Route #], Count(*) as Late
FROM [On time performace audits]
WHERE ([On time performace audits].DATE Between [StartDate] And [EndDate]) AND ( Minute( [ACTUAL TIME]- [SCHEDULED TIME]) >=6)
GROUP BY [Route #];
I'm not much of a SQL guy, more of a LINQ guy, and writing a quick and dirty app in Access 2003 after being used to VS2010 is a bit painful. Anyways, hope you can help. Thanks.