Rental Car Inventory Database

[tcshred]

I have a database with two tables. One for rental car bookings, and one for rental car returns.

Tables are structured as below:
Bookings Table:
ID
Location
Booking Date
Quantity

Returns Table:
ID
Location
Return Date
Quantity

I am basically trying to make an inventory calculation. I have made two queries, one for each table. The both do a calculation to work out the sum. Either cars in or out.

I have then created a query which does a SumofReturns-SumofBookings calculation based on the two queries explained above. However, it is giving me multiple rows for each location.


Location1 24
Location1 25
Location2 11
Location2 12
Location3 19
Location3 20

Any ideas why?

 

[CJ_London]

You should consider combining your two tables with an additional field to indicate whether the transaction is a booking or a return.

Without knowing how your two queries (plus presumably a third one to combine them in some way) are constructed it is impossible to suggest why you are getting multiple rows

 

[gemma-the-husky]

the tables sound strange

you would need a table for vehicles/equipment
another table for hires

the hire table would include startdate/time, enddatetime, and details of the return position. I can't think you would need a separate table for the return. Why do you need "quantity" as field in the hires table?