Novice in need of direction

[garamon]

Hi,



I have 4 tables that look kind of like this:

Customer
customerID
LastName
StorageUnit
Rent
WarehouseID
Warehouse
Warehouse ID

Unit Rental
CustID
WarehouseID

The Unit Rentals are records of customer rentals, and the rent is the price per unit. The units are in 3 different warehouses.

I have to group the information by warehouse and show the name of the renter by last name, include rent per unit, total rent per each warehouse, and a grand total for all warehouses.

Any help is much appreciated, thanks!

[jzwp11]

Since you already join the rental unit to the warehouse, you just need to join the client to the rental unit

Customer
customerID
LastName


StorageUnit
StorageUnitID
Rent
WarehouseID

Warehouse
Warehouse ID

Unit Rental
pkUnitRentalID primary key, autonumber
CustID
StorageUnitID

How are you going to handle changes in the rental amount?
Does each storage unit have a fixed rent or do you have the option of changing the rental amount when you actually rent the unit to client?
In other words, do you offer a discounted rent to some of your better clients?

Also it is recommended not to have spaces or special characters in your table or field names.

[garamon]

ok sooo...there aren't any changes in prices - i'm trying to build an income report for the warehouses. the client is joined to the rental unit ("custID" in UnitRental was supposed to be "customerID", sorry).

When you say no special characters or spaces, are you saying that because you noticed it or just in general?

[jzwp11]

The table: Unit Rental has a space. I would recommend UnitRental

Also, you need the StorageUnitID field in the Unit Rental table since the client rents the unit not the warehouse.

You will need to create a query that brings the related tables and fields together in order to generate your income report.

[garamon]

yeah, the StorageUnitID is in the UnitID table. The space was a typo but thanks for pointing it out. The query is what I'm having difficulty with (I think my joins are all ok). I can make a query that returns the total rental income, another one that returns the total for each warehouse, another one that lists each unit's price, and another one that lists each customer, but I can't figure out how to make one query that combines all those.

[jzwp11]

Actually, you would make 1 query that brings in all of the raw data and use the grouping capabilities of the report to get the grand total, warehouse total and individual unit amounts (detail section of the report)