I have a database that is a pilot logbook. I have one table, it contains a record for each flight I have made. The first column is the date, and the rest of the columns are simple decimal columns which represent several breakdowns of a flight in terms of hours, i.e. Total duration of flight, Night flight, Day flight, Pilot in Command (PIC), Second in Command (SIC), etc. Here's a screenshot of a sample record from the database:

http://fermata.lrehosting.com/files/Picture 1.png

I would like to get an output of a bunch of these totals aggregated by date range. For example, I want a report that will tell me the Sum of Day, Sum of PIC, Sum of SIC within the past 30 days, 60 days, 90 days, 120 days, etc. I can do this by making a bunch of queries with subtotals for each of the required breakdowns, but I want it all in one output, here's a screenshot of what I mean (with some dummy data):

http://fermata.lrehosting.com/files/Picture 2.png

This is what I want, basically a spreadsheet-like output with all of the breakdowns.

Is there an easy way to do this? It almost looks like a Pivottable could work but I tried messing around with it and I couldn't find a way to aggregate based on custom date ranges. Thanks a lot.

I think you might can do this with a pivot table but you should probably start with a base query and then base the pivot table on that. Here's a base query (I'll assume your table is called Pilots. (I rushed this - so may be some syntax errors).

SELECT SUM(Pilots.PIC) as PIC, SUM(Pilots.Sic) as SIC, SUM(Pilots.XC) as XC, SUM(Pilots.Night) as Night, 'Last 30 Days' as Span
FROM Pilots
WHERE [Date] >= Date() - 30

UNION ALL

SELECT SUM(Pilots.PIC) as PIC, SUM(Pilots.Sic) as SIC, SUM(Pilots.XC) as XC, SUM(Pilots.Night) as Night, 'Last 60 Days' as Span
FROM Pilots
WHERE [Date] >= Date() - 60

UNION ALL

SELECT SUM(Pilots.PIC) as PIC, SUM(Pilots.Sic) as SIC, SUM(Pilots.XC) as XC, SUM(Pilots.Night) as Night, 'Last 90 Days' as Span
FROM Pilots
WHERE [Date] >= Date() - 90

UNION ALL

SELECT SUM(Pilots.PIC) as PIC, SUM(Pilots.Sic) as SIC, SUM(Pilots.XC) as XC, SUM(Pilots.Night) as Night, 'Last 120 Days' as Span
FROM Pilots
WHERE [Date] >= Date() - 120

Thanks, that works!

Is there any way to add an additional condition - I would like to make a column that is Night where PIC is greater than 0... in essence, return records where PIC time AND Night time has been logged.... in which we count only the time that is Night AND PIC. Ie. we have one flight that was PIC=2 and Night=1.2, so we only count the 1.2 (because it is both PIC and Night) and add it up like the other columns. Can we do this within the same SQL query? Thanks again

Is there any way to add an additional condition - I would like to make a column that is Night where PIC is greater than 0... in essence, return records where PIC time AND Night time has been logged.... in which we count only the time that is Night AND PIC. Ie. we have one flight that was PIC=2 and Night=1.2, so we only count the 1.2 (because it is both PIC and Night) and add it up like the other columns. Can we do this within the same SQL query? Thanks again I am not entirely sure this request is intelligible to a database.
The query I gave you already has a WHERE clause, it returns records where that condition is met. Now you seem to be speaking of a different WHERE clause which would seem to return a different set of records, possibly contradicting your first request. I'm feeling confused.

Maybe what you wanted is to add another section like this:

UNION ALL


SELECT SUM(Pilots.PIC) as PIC, SUM(Pilots.Sic) as SIC, SUM(Pilots.XC) as XC, SUM(Pilots.Night) as Night, 'WHEREVER PIC > 0 ' as Span
FROM Pilots
WHERE PIC > 0

Yes, you are understanding correctly. That creates another row with only records where PIC > 0, but I would like to have instead of a row, a column (so that the date ranges are still factored in), see image:

http://fermata.lrehosting.com/files/Picture 6.png

Thanks a lot for your help!

Maybe I'm beginning to understand. I gave you four queries UNIONED. And I think you added one (for "Last Year") giving a total of five.

In that case add five queries resulting in 10 unioned queries. With the first five, the WHERE claus was like this:

WHERE [Date] >= Date() - 120

With the last five, the WHERE clause should probably be this:

WHERE [Date] >= Date() - 120 AND Pic > 0

Can you append it to what I have already? I'm not sure what you mean by the "last five" and "first five"

The four queries I gave you are these:
30 days
60 days
90 days
120 days

You just uploaded a picture which has a fifth item
30 days
60 days
90 days
120 days
Last year
So I can only assume you added a fifth query. No?

Yeah, you're right. I'll be adding a ton more of those queries. We can just consider it 4 for now to keep with the original example (I should be able to expand on it as necessary) right now I'm just trying to get that extra breakdown of Night where PIC > 0. To keep with the original example, here's what i'm looking for at this point:

http://fermata.lrehosting.com/files/Picture 6.png

Yes, then, as I said, union for more queries to the four I gave (only the WHERE clause will chaneg, as I said). Maybe I'll do it for you...

Or do I have that wrong. I'm getting confused again (as usual).

It's hard to do all this without sample data since I'm not all that bright. I usually get it right only by trial and error. I'll let you know if I can figure this out in my head.

I am guessing I gave you the right answer in post #8. If you don't follow me, maybe Ill write it out for you in full.

Here is the output of the original code you gave me (Post #2), if that helps:

http://fermata.lrehosting.com/files/Picture 7.png

I just want to add another column that has Night where PIC > 0. So basically, we are constraining that information twice - across with the date ranges, and down with specifiying only records that have both PIC and Night.

Post 8 may be right, I'd try it if you could write it for me, I am really new to SQL! D'oh!

Okay I think I was wrong. I think you need an inner join to add that column. (Join two queries). So I'll just write a second query and then I'll give you a suggestion on how to inner join the two queries.

Okay here's the second query

SELECT SUM(Pilots.Night) as [Night WHERE Pic > 0] , 'Last 30 Days' as Span
FROM Pilots
WHERE [Date] >= Date() - 30 AND Pic > 0

UNION ALL

SELECT SUM(Pilots.Night) as [Night WHERE Pic > 0] , 'Last 60 Days' as Span
FROM Pilots
WHERE [Date] >= Date() - 60 AND Pic > 0

UNION ALL

SELECT SUM(Pilots.Night) as [Night WHERE Pic > 0] , 'Last 90 Days' as Span
FROM Pilots
WHERE [Date] >= Date() - 90 AND Pic > 0

UNION ALL

SELECT SUM(Pilots.Night) as [Night WHERE Pic > 0] , 'Last 120 Days' as Span
FROM Pilots
WHERE [Date] >= Date() - 120 AND Pic > 0

Suppose you named the first query qrySumAll and this last one qrySumPicsAboveZero, you can now write a new query that joins them.

SELECT * FROM qrySumAll as Q1
INNER JOIN qrySumPicsAboveZero as Q2
ON Q1.Span = Q2.Span

The "Span" column returns twice, hope that's not a problem. I could eliminate that if necessary.

Just realized I could have done the whole thing in one query:

SELECT SUM(Pilots.PIC) as PIC, SUM(Pilots.Sic) as SIC, SUM(Pilots.XC) as XC, SUM(Pilots.Night) as Night, SUM(IIF(Pic > 0, Night, 0)) as [Night WHERE Pic > 0], 'Last 30 Days' as Span
FROM Pilots
WHERE [Date] >= Date() - 30

UNION ALL

SELECT SUM(Pilots.PIC) as PIC, SUM(Pilots.Sic) as SIC, SUM(Pilots.XC) as XC, SUM(Pilots.Night) as Night, SUM(IIF(Pic > 0, Night, 0)) as [Night WHERE Pic > 0], 'Last 60 Days' as Span
FROM Pilots
WHERE [Date] >= Date() - 60

UNION ALL

SELECT SUM(Pilots.PIC) as PIC, SUM(Pilots.Sic) as SIC, SUM(Pilots.XC) as XC, SUM(Pilots.Night) as Night,SUM(IIF(Pic > 0, Night, 0)) as [Night WHERE Pic > 0], 'Last 90 Days' as Span
FROM Pilots
WHERE [Date] >= Date() - 90

UNION ALL

SELECT SUM(Pilots.PIC) as PIC, SUM(Pilots.Sic) as SIC, SUM(Pilots.XC) as XC, SUM(Pilots.Night) as Night,SUM(IIF(Pic > 0, Night, 0)) as [Night WHERE Pic > 0], 'Last 120 Days' as Span
FROM Pilots
WHERE [Date] >= Date() - 120

The second method returns an error "Subqueries cannot be used in the expression (Sum(IIF(Pic>0,Night,0)))" is it just a syntax error somewhere?

The second method returns an error "Subqueries cannot be used in the expression (Sum(IIF(Pic>0,Night,0)))" is it just a syntax error somewhere?
I don't know - in fact that's why I originally decided to use two separate queries, meaning i wasn't sure if Access allows this sort of thing. I'll try to figure that out.

I don't get it, that should have worked. Yes, it could be a syntax error but I don't see one as yet.

I just made a row of fake data and tried this:

SELECT SUM(IIF(Pic > 0, Night, 0)) as [Night WHERE Pic > 0], 'Last 30 Days' as Span
FROM Pilots

which worked fine on my machine. I don't know why it isn't working on yours.

Re-copy the query from post#20 and re-paste it into sql view, because it looks right to me. Just to make sure.

Also, try one query at a time (one of the four) to maybe isolate the one that's giving the problem.

I needed to add the table name in front. PIC becomes Pilots.PIC and it worked!!!!!!! I need to figure out if it's giving me correct data now... but i think it is... here's a tentative THANK YOU!

Ok, I am getting the same error now but don't know why....

Glad you figure that out - it's a good thing as I never would have guessed that was the problem. I'm still scratching my head.

This worked for me (things in bold added):

SELECT SUM(Pilots.PIC) as PIC, SUM(Pilots.Sic) as SIC, SUM(Pilots.XC) as XC, SUM(Pilots.Night) as Night, SUM(IIF(Pilots.Pic > 0, Pilots.Night, 0)) as [Night WHERE Pic > 0], 'Last 30 Days' as Span
FROM Pilots
WHERE [Date] >= Date() - 30

UNION ALL

SELECT SUM(Pilots.PIC) as PIC, SUM(Pilots.Sic) as SIC, SUM(Pilots.XC) as XC, SUM(Pilots.Night) as Night, SUM(IIF(Pilots.Pic > 0, Pilots.Night, 0)) as [Night WHERE Pic > 0], 'Last 60 Days' as Span
FROM Pilots
WHERE [Date] >= Date() - 60

UNION ALL

SELECT SUM(Pilots.PIC) as PIC, SUM(Pilots.Sic) as SIC, SUM(Pilots.XC) as XC, SUM(Pilots.Night) as Night, SUM(IIF(Pilots.Pic > 0, Pilots.Night, 0)) as [Night WHERE Pic > 0], 'Last 90 Days' as Span
FROM Pilots
WHERE [Date] >= Date() - 90

UNION ALL

SELECT SUM(Pilots.PIC) as PIC, SUM(Pilots.Sic) as SIC, SUM(Pilots.XC) as XC, SUM(Pilots.Night) as Night, SUM(IIF(Pilots.Pic > 0, Pilots.Night, 0)) as [Night WHERE Pic > 0], 'Last 120 Days' as Span
FROM Pilots
WHERE [Date] >= Date() - 120

What I especially don't understand is why the code in post #24 worked fine.

Again, it's a good thing you figured that one out, because it's still lost upon me.

No idea, but if anyone were to know why, it wouldn't be me. Thanks for the help, I think this is all of the functionality I'll need for this application, if need extra help I'll post it up here! Thanks!