Daily totals in a report of date range.

[Caine]

Hi. I have searched the forums for an answer, but I may not be on the right track.

What I have is a db linking to SQL. The query pulls information related to quantity usage of an item per day. I would like to add a daily total and usage percentage for each day in a range. User specifies the date range for the query. The usage percentage is a calculated field in the query. All the others are pulled directly from the SQL table.

example report:

Date Item Max Used Usage
8/31/05 a 40 20 50%
8/31/05 b 18 14 61%
8/31/05 c 22 17 77%
8/31/05 d 12 9 67%
8/31/05 e 17 14 82%

Total
8/31/05 109 74 68%

and again for each day in a specified range.

I may be going about this the wrong way, so if you have suggestions, I would appreciate it. Thank you.

 

[Smart]

Write a query that selects the required fields something like this
SELECT Table2.ddate, Sum(Table2.max) AS SumOfmax, Sum(Table2.used) AS SumOfused, Count(Table2.usage) AS cusage, Sum(Table2.usage) AS susage, [susage]/[cusage] AS totusage
FROM Table2
GROUP BY Table2.ddate;

Just add your date parameter and amend the table / field names accordingly

 

[Caine]

Thank you. I will give that a shot.

 

[Caine]

This is what I have now. When I run it with a large range over dates, I get ODBC call failed. If I leave off the Between [enter flight_date] And [enter end flight_date] from the criteria, then it returns a divide by zero error.

If I enter get a single date or a small range, it works, but the sumOfmax_seats and sumOfseat_count fields in the query are identical to the max_seats and seat_count fields. Both sload_factor and totLoad_factor are identical to the load_factor field.

SELECT Scheduled_Flights.Flight_Date, Scheduled_Flights.Flight_No, Scheduled_Flights.Fare_Zone, Scheduled_Flights.Origin, Scheduled_Flights.Destination, Scheduled_Flights.Max_Seats, Scheduled_Flights.Seat_Count, [Seat_count]/[max_seats] AS Load_Factor, Sum(Scheduled_Flights.Max_Seats) AS SumOfmax_seats, Sum(Scheduled_Flights.Seat_Count) AS SumOfSeat_Count, Count(Load_Factor) AS cLoad_Factor, Sum(Load_Factor) AS sLoad_Factor, [sLoad_Factor]/[cLoad_Factor] AS totLoad_Factor
FROM Scheduled_Flights
GROUP BY Scheduled_Flights.Flight_Date, Scheduled_Flights.Flight_No, Scheduled_Flights.Fare_Zone, Scheduled_Flights.Flight_Type, Scheduled_Flights.Origin, Scheduled_Flights.Destination, Scheduled_Flights.Max_Seats, Scheduled_Flights.Seat_Count, [Seat_count]/[max_seats]
HAVING (((Scheduled_Flights.Flight_Date) Between [enter flight_date] And [enter end flight_date]) AND ((Scheduled_Flights.Flight_Type)="sched"))
ORDER BY Scheduled_Flights.Flight_Date DESC , Scheduled_Flights.Flight_No;

Any suggestions? I appreciate your help.