Combine queries or making 1 query

I am currently attempting to create a database for work, I have never used Access before. So far I have managed to create a data entry form that feeds a _be table.

What I am trying to do now is be able to query the total number of records in 3 formats (YTD,MTD,WTD) and then group by "airplane model" which i have managed to accomplish separately. The problem is when I try to put that data on a report I have to create subreports to include all 3 queries.

Is there a way to put all of this in 1 query or merge the queries I have without compromising data? So far when I attempt to use Union or Join the data doesn't feed correctly. The fields I am querying off of are the same for each query, just the dates are criteria differently.

YTD SQL

SELECT [BlueStreak Tracking1].[Airplane Model], Count(*) AS [This Year]
FROM [BlueStreak Tracking1]
WHERE ((([BlueStreak Tracking1].[Issue Date]) Between DateSerial(Year(Date()),1,1) And DateSerial(Year(Date()),12,31)))
GROUP BY [BlueStreak Tracking1].[Airplane Model]
ORDER BY [BlueStreak Tracking1].[Airplane Model];

MTD SQL

SELECT [BlueStreak Tracking1].[Airplane Model], Count(*) AS [This Month]
FROM [BlueStreak Tracking1]
WHERE (((Year([Issue Date]))=Year(Date())) AND ((Month([Issue Date]))=Month(Date())))
GROUP BY [BlueStreak Tracking1].[Airplane Model]
ORDER BY [BlueStreak Tracking1].[Airplane Model];

WTD SQL

SELECT [BlueStreak Tracking1].[Airplane Model], Count(*) AS [This Week]
FROM [BlueStreak Tracking1]
WHERE ((([BlueStreak Tracking1].[Issue Date]) Between DateAdd("d",-((Weekday(Date())-1)),Date()) And Date()))
GROUP BY [BlueStreak Tracking1].[Airplane Model]
ORDER BY [BlueStreak Tracking1].[Airplane Model];
728x90

1 Answers Combine queries or making 1 query

You should be able to do it in a Union All. See if this works:

SELECT [BlueStreak Tracking1].[Airplane Model], 'This Year' as [TimeFrame], Count(*)
FROM [BlueStreak Tracking1]
WHERE ((([BlueStreak Tracking1].[Issue Date]) Between DateSerial(Year(Date()),1,1) 
And DateSerial(Year(Date()),12,31)))
GROUP BY [BlueStreak Tracking1].[Airplane Model]

UNION ALL

SELECT [BlueStreak Tracking1].[Airplane Model], 'This Month', Count(*) 
FROM [BlueStreak Tracking1]
WHERE (((Year([Issue Date]))=Year(Date())) AND ((Month([Issue Date]))=Month(Date())))
GROUP BY [BlueStreak Tracking1].[Airplane Model]

UNION ALL

SELECT [BlueStreak Tracking1].[Airplane Model], 'This Week',  Count(*) 
FROM [BlueStreak Tracking1]
WHERE ((([BlueStreak Tracking1].[Issue Date]) Between DateAdd("d",- 
((Weekday(Date())-1)),Date()) And Date()))
GROUP BY [BlueStreak Tracking1].[Airplane Model]
ORDER BY [BlueStreak Tracking1].[Airplane Model];

1 weeks ago