Need help with complex query and reports (beginner)

[dicker521]

I am trying to generate reports based on a query and am not sure just how to go about it. The application is a trucking compnay and I need a report to generate total miles driven in a state by a particular truck between two dates. My data is in a table with Truck number,trip number, date, state, and miles driven (for that trip). A truck may do 10 trips per month (which is a typical date range for a report) so my data has a number of mileages that I want to consolidate. I need to generate a report that summarizes, unit number, and miles travelled in each state for a specific time frame. Unfortunately I don't know how to better explain myself. I'm not sure how to go about this using the methods I am familiar with.
Thank you in advance.
Todd

 

[lespaul]

Unfortunately I don't know how to better explain myself.

Draw a "picture" of what you want:

This is what I have:
[tt]
TableName
TruckNumber TripNumber Date State MilesDriven
{now put dummy information here}[/tt]

This is what I need:
[tt]
TruckNumber State TotalNumberOfMiles
{Take data from above and show how you would expect that data to look for the report}[/tt]



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[PHV]

A starting point (SQL code)
SELECT TruckNumber, State, Sum(MilesDriven) As TotalNumberOfMiles
FROM yourTable
WHERE [Date] Between [start date] And [end date]
GROUP BY TruckNumber, State

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dicker521]

The data is in a table as follows;
TruckNumber Date TripNumber State MilesDriven
T278 01/01/07 1 NY 230
T246 01/19/07 4 TX 900
T278 01/01/07 1 NY 45
T322 01/11/07 5 TX 120
T246 01/12/07 4 MI 300
T278 01/31/07 18 CT 65
And I need to generate a report that summarizes
Trucknumber Date State Miles
T278 [Select Date Range] NY 275
CT 65

T246 [Date Range] TX 900
MI 300

T322 [Date Range] TX 120
**

I initially used a crosstab query and generaed a report based on the query but I encountered a problem when I would run a report for a truck that did not have any mileage in a certain state access returned an error.

ANy help is appreciated

 

[wspille]

To see the trucks that have no mileage records, first create and store the summary query as described by PHV, and then do an outer join with a query that has the truck information. This will include all of the trucks, whether there are mileage records or not.

 

[lameid]

Modifying PHV's anwer for adding the date range...

Select TruckNumber, State, Sum(MilesDriven) as Miles, [Enter Start Date] As StartDate, [Enter End Date] as EndDate

From <Table>

Where [Date] Between [Enter Start Date] And [Enter End Date]
GROUP BY TruckNumber, State

 

[dicker521]

Thank you all very much.