Report with multiple totals

[cloudsurfer]

Hi all,
I've used access for many years and now I want something out of it that I can't get 1-2-3.
I have a huge datebase where flight hours are logged.
Now I want a report that shows a total of flight hours: since the beginning , last 365 days, last 90 days, last 30 days.
example.
01-01-2020 A flown 1,2 hours
01-01-2020 B flown 1,0 hours
03-01-2020 A flown 0,5 hours
04-01-2020 C flown 1,0 hours
10-02-2020 A flown 1,0 hours
21-03-2020 C flown 1,0 hours

Report
Total: 365 days 90 days 30 days
A 2,7 hours 2,7 1,0 0,0
B 1,0 hours 1,0 0,0 0,0
C 2,0 hours 2,0 1,0 1,0

I know I can do it with queries but then the order of ABC wil be different since in for example the 30 day query the result will only give a total for C.

Any ideas?

Chris
P.s. this is not for commercial purpose, purely a hobby.

 

[Ranman256]

make 3 queries, 360,. 90, 60.
each will pull from their date ranges. Be sure to add the Caption in the query: "90 Days" as Caption

then for the final report, it will pull from a union query that pulls all 3 queries:
select * from qsFlightHrs360
union
select * from qsFlightHrs90
union
select * from qsFlightHrs60

 

[plog]

Make a query with a field for each range you want to keep track of.

Last30: Iif(YourDateField>=(Date() - 30), HoursField, 0)

You can then SUM those fields and get your totals.

 

[cloudsurfer]

I've tried 'union' but that didn't give me what I was looking for.
SQL:

SELECT DISTINCTROW totaaltabel.Type, Sum(totaaltabel.Duration) AS SomVanDuration
FROM totaaltabel
WHERE (((totaaltabel.Date)>Date()-90))
GROUP BY totaaltabel.Type
HAVING (((totaaltabel.Type)<>"FNPT2"));

union

SELECT DISTINCTROW totaaltabel.Type, Sum(totaaltabel.Duration) AS SomVanDuration
FROM totaaltabel
WHERE (((totaaltabel.Date)>Date()-30))
GROUP BY totaaltabel.Type
HAVING (((totaaltabel.Type)<>"FNPT2"));

result is totals in 1 column.
A 8
A 5
B 3
C 2
which should be 2 result colum
A 8 5
B 3 3
C 2 1
where the first colum shows the totals from previous 90 days and the 2nd column shows totals of previous 30 days.

 

[jdraw]

I mocked up your requirement
tblFlightInfo

FlightInfoId	Person	FlightDate	Flighthours
1​	A	08-Oct-19​	3.5​
2​	A	18-Oct-19​	2.5​
3​	B	18-Oct-19​	8​
4​	C	18-Dec-19​	4.5​
5​	A	15-Jan-20​	3​
6​	B	23-Jan-20​	6​
7​	B	28-Jan-20​	4​
8​	C	12-Feb-20​	5​
9​	C	10-Mar-20​	4​
10​	A	17-Mar-20​	10​
11​	A	20-Mar-20​	4​
12​	B	04-Mar-20​	2​
13​	B	19-Mar-20​	2.5​
14​	C	19-Mar-20​	5.5​

With this query

SELECT person
    ,sum(hr365) AS last365
    ,sum(hr90) AS last90
    ,sum(hr30) AS last30
FROM (
    SELECT tblFlightInfo.Person
        ,Sum([flighthours]) AS Hr365
        ,0 AS hR90
        ,0 AS hR30
    FROM tblFlightInfo
    WHERE flightdate <= DATE ()
        AND flightdate >= DATE () - 365
    GROUP BY tblFlightInfo.Person
   
    UNION
   
    SELECT tblFlightInfo.Person
        ,0 AS Hr365
        ,Sum([flighthours]) AS hR90
        ,0 AS hR30
    FROM tblFlightInfo
    WHERE flightdate <= DATE ()
        AND flightdate >= DATE () - 90
    GROUP BY tblFlightInfo.Person
   
    UNION
   
    SELECT tblFlightInfo.Person
        ,0 AS Hr365
        ,0 AS hR90
        ,Sum([flighthours]) AS hR30
    FROM tblFlightInfo
    WHERE flightdate <= DATE ()
        AND flightdate >= DATE () - 30
    GROUP BY tblFlightInfo.Person
    )
GROUP BY tblFlightInfo.Person

to get this result

Query59

person	last365	last90	last30
A	23​	17​	14​
B	22.5​	14.5​	4.5​
C	19​	14.5​	9.5​

Hope it's helpful.

 

[HiTechCoach]

Make a query with a field for each range you want to keep track of.

Last30: Iif(YourDateField>=(Date() - 30), HoursField, 0)

You can then SUM those fields and get your totals.

I agree.

I t can easily be done with a single select query using calculated fields. that are summed up.

No needs to create multiple queries the overhead of the additional UNION query.

I do it all the time in account systems to get aged receivables and payables reports that are broken down in 30 days buckets for 6 months or a year. All with a single select query with calculated fields that are summed. No UNION query required

NOTE: When dealing with large datasets, UNION queries can get really slow.

 

[Ranman256]

simplify the union by using 3 queries. (as my example)
don't put all the sql in 1 union.

 

[HiTechCoach]

simplify the union by using 3 queries. (as my example)
don't put all the sql in 1 union.

Why do yoy need a UNION query when a totaling query with calcuated field can do it?

 

[cloudsurfer]

I mocked up your requirement
tblFlightInfo

FlightInfoId	Person	FlightDate	Flighthours
1​	A	08-Oct-19​	3.5​
2​	A	18-Oct-19​	2.5​
3​	B	18-Oct-19​	8​
4​	C	18-Dec-19​	4.5​
5​	A	15-Jan-20​	3​
6​	B	23-Jan-20​	6​
7​	B	28-Jan-20​	4​
8​	C	12-Feb-20​	5​
9​	C	10-Mar-20​	4​
10​	A	17-Mar-20​	10​
11​	A	20-Mar-20​	4​
12​	B	04-Mar-20​	2​
13​	B	19-Mar-20​	2.5​
14​	C	19-Mar-20​	5.5​

With this query

SELECT person
    ,sum(hr365) AS last365
    ,sum(hr90) AS last90
    ,sum(hr30) AS last30
FROM (
    SELECT tblFlightInfo.Person
        ,Sum([flighthours]) AS Hr365
        ,0 AS hR90
        ,0 AS hR30
    FROM tblFlightInfo
    WHERE flightdate <= DATE ()
        AND flightdate >= DATE () - 365
    GROUP BY tblFlightInfo.Person
  
    UNION
  
    SELECT tblFlightInfo.Person
        ,0 AS Hr365
        ,Sum([flighthours]) AS hR90
        ,0 AS hR30
    FROM tblFlightInfo
    WHERE flightdate <= DATE ()
        AND flightdate >= DATE () - 90
    GROUP BY tblFlightInfo.Person
  
    UNION
  
    SELECT tblFlightInfo.Person
        ,0 AS Hr365
        ,0 AS hR90
        ,Sum([flighthours]) AS hR30
    FROM tblFlightInfo
    WHERE flightdate <= DATE ()
        AND flightdate >= DATE () - 30
    GROUP BY tblFlightInfo.Person
    )
GROUP BY tblFlightInfo.Person

to get this result

Query59

person	last365	last90	last30
A	23​	17​	14​
B	22.5​	14.5​	4.5​
C	19​	14.5​	9.5​

View attachment 80070
Hope it's helpful.

That is exactly what I need.
Thank you. I will try and put it in my access DB.

 

[cloudsurfer]

Jdraw,
I've got it working in my Dbase, works quick and perfect.
Is it possible to get a total under the 365 90 and 30 day column?
thx

 

[jdraw]

Yes
FlightHoursWithTotalsDesign_View

FlightHoursWithTotals

 

[cloudsurfer]

Thx again jdraw, perfect.
I should have managed this one myself as I have many reports build like that, was too focussed on doing it in a Query :-(.

 

[jdraw]

You are very welcome. Happy to help. Good luck with your project.