How to Build A Schedule Report

[jollofrice]

I am building a database for a vehicle rental service and need to build a report to display the vehicle schedule. I want to use dates as column headers and list the vehicles down the left side of the report. At the intersections I need to list the name of the customer and the driver assigned or whether the vehicle is being repaired.

The relevant fields and tables are:

Customers Table:
[CustomerID]
[FirstName]
[LastName]
[Organization]

Sales And Reservations Table:
[CustomerID] (linked to Customers)
[ReservationNumber]

Rentals Table
[RentalID]
[ReservationNumber] (linked to Sales And Reservations)
[DriverID] (linked to Drivers)
[LicensePlate] (linked to Vehicles)
[StartDate]
[EndDate]

Repairs Table
[RepairID]
[LicensePlate] (linked to Vehicles)
[StartDate]
[EndDate]

Drivers Table
[DriverID] (linked to Rentals)
[FirstName]
[LastName]

Vehicles Table
[LicensePlate] (used as primary key)
[VehicleName]

I have absolutely no idea where to start. I've tried some crosstabs, some reports from scratch, and a few combinations of simple queries. Any help would be great.

 

[elgoober]

Hi

A cheap and cheerful way to get round the various relationship issues you have is to chuck the data into a master table

So you'd have DATE, ID,LICENSE PLATE,CUSTOMER + DRIVER

Then

Design an append query (you will have to do 3 - 1 for rental, 1 for sales, 1 for repairs) that chucks all the values from each table into the 1 table (this assumes the ID's clearly differ).

Then once these are all in - write a query grouping by date each ID and vehicle and driver

Then write a report based on the query grouping on the header of date - to get a diary effect -

You would then write a delete query for the master table and a macro that went

delete the table
apend1 (say repairs)(query)
append2 (reservations)(query)
append3 (rentals)(query)

This would automate all of the above

Of course you could put some proper relationship's in and build like that - but more complex

Cheers
paul

 

[jollofrice]

How do you mean proper relationships?

Also, would I create 3 append queries and then combine to 1 table? Or 3 queries and 3 tables?

 

[elgoober]

Linking the tables by the relevant ID's.(this can be fiddly on output - making sure the relevant results appears - which is why in the ist instance I recommend the cheap and cheerful way)

Yes 3 append queries into 1 mastertable with the relevant fields

 

[jollofrice]

The tables are all already linked by ID's, will that interfere with the append query method?

 

[elgoober]

No

Try 1 append and see

 

[jollofrice]

Alright. I'll try that. Thanks.

 

[jollofrice]

Okay, the append query method works to get all of the events in the right place, but I still can't format the report as I'd like. The document this report will produce is used frequently and relied upon heavily during the regular course of business and I'd like to minimize the learning required from the employees when the database system is implemented, so I'd like it to be as close to the original document as possible.

Currently, the schedule is kept manually in Microsoft Excel, the vehicles are listed down the left side and dates are used as column headers. The jobs the vehicles have been assigned to and their respective drivers, as well as repairs, are listed in the cells where the vehicle row and date column meet.

The document is used to check future availability of the vehicles and printed daily for Fleet Manager use by selecting the current week and printing the selection. I had planned to use a form to enter report criteria for the needed period of time, for both printing and checking vehicle availability.

Any suggestions?