Report with multiple totals (1 Viewer)

cloudsurfer

New member
Local time
Today, 13:40
Joined
Mar 23, 2020
Messages
18
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

Well-known member
Local time
Today, 08:40
Joined
Apr 9, 2015
Messages
4,337
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

Banishment Pending
Local time
Today, 07:40
Joined
May 11, 2011
Messages
11,611
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

New member
Local time
Today, 13:40
Joined
Mar 23, 2020
Messages
18
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

Super Moderator
Staff member
Local time
Today, 08:40
Joined
Jan 23, 2006
Messages
15,361
I mocked up your requirement
tblFlightInfo

FlightInfoIdPersonFlightDateFlighthours
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
Code:
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

personlast365last90last30
A
23​
17​
14​
B
22.5​
14.5​
4.5​
C
19​
14.5​
9.5​

ReportFlightHoursByPerson.PNG

Hope it's helpful.
 

HiTechCoach

Well-known member
Local time
Today, 07:40
Joined
Mar 6, 2006
Messages
4,357
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.
 
Last edited:

Ranman256

Well-known member
Local time
Today, 08:40
Joined
Apr 9, 2015
Messages
4,337
simplify the union by using 3 queries. (as my example)
don't put all the sql in 1 union.
 

cloudsurfer

New member
Local time
Today, 13:40
Joined
Mar 23, 2020
Messages
18
I mocked up your requirement
tblFlightInfo

FlightInfoIdPersonFlightDateFlighthours
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
Code:
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

personlast365last90last30
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

New member
Local time
Today, 13:40
Joined
Mar 23, 2020
Messages
18
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

Super Moderator
Staff member
Local time
Today, 08:40
Joined
Jan 23, 2006
Messages
15,361
Yes
FlightHoursWithTotalsDesign_View
FlightHoursWithTotalsDESIGN_VIEW.PNG



FlightHoursWithTotals
FlightHoursWithTotals.PNG
 
Last edited:

cloudsurfer

New member
Local time
Today, 13:40
Joined
Mar 23, 2020
Messages
18
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

Super Moderator
Staff member
Local time
Today, 08:40
Joined
Jan 23, 2006
Messages
15,361
You are very welcome. Happy to help. Good luck with your project.
 

Users who are viewing this thread

Top Bottom