I'm trying to create a query that w

[hysonmb]

I'm trying to create a query that will calculate a total remaining value after entering data into multiple tables.

I need to find out how many Square Feet remain in an area after entering a users who will use a specific amount of Square Feet based on position. I have separate tables for each building and floor and they're related by the Building ID. Both tables are related to my user table by IDs. This is what it boils down to:

tblLoc lists the locations available
tblBldg lists the building numbers
tblFloor lists the floor numbers and the Sq.Ft of each
tblPerso has the user and all relevant data

In the end, I need the report to say something to the effect of:
1st floor of building 1 at whatever location has 5 users using a total of 50 out of 1500 available square feet. Reamaing available square feet is 1450.

If anyone can help me, please post. Thanks in advance.

 

[lespaul]

Well, you're not going to be able to get all the information you want to report in a single query.

Why do you have separate tables for each of the buildings and floors?

Can you provide a little more detail about your tables and how the relationships are set up?

Thanks,
Leslie

 

[hysonmb]

The reason that I have the separate tables is because I need to know the square footage on each floor of each building and as we grow and accquire more buidlings, it becomes a hassel to enter:

Building 5, 1st Floor
Building 5, 2nd Floor
Building 4, 3rd Floor, etc.

With the separate tables I just say that I have Buidling 5 and I go to the floors subdatasheet and start plugging away at the data for the floors. It also keeps the combo box small on the form because the user can select a building and see only the floors in that building rather than each location that we have in the next combo box.

Is that not good to do? If there is a better way I would be happy to learn.

To answer your question about my relationships, I have

tblBldg - tblFloor (One to Many)
and
tblLoc - tblBldg (One to Many)

These are also only a portion of a more expansive database that I'm working on. Each of them are related in one way or another to another table in the database as well.

Maybe I should have posted this in a reports forum? If you can help, please do. I appreciate the response either way.

 

[JonFer]

Here are the steps that should work assuming you don't have any effective dates in your tables and that I understood your schema correctly.

Join Loc to Bldg on LocationID
Join Bldg to Floor on BuildingID
Join Person to Bldg and Floor on BuildingID and FloorID
Join Person to Position on PositionID

Group by Location, Bldg and Floor
Count(*) as Users
Sum(PositionSquareFeet) as UsedSqFt
Max(FloorSquareFeet) as TotalSqFt
Max(FloorSquareFeet)-Sum(PositionSquareFeet) as AvailableSqFt

 

[hysonmb]

Thanks for the help, I'm sorry for the delay in response but I've got a lot going on and I haven't been back to that db to test this solution.