Calculation on Group

[ThePeter]

I am preparing a report for occupancy based on room rate classification.

We have three rate classifications, depending on long-term, short-term, or Penthouse: Tenant, Transient, or Penthouse. The first two may occupy the type of room, but the third is only in the condos.

The "Building Occupancy" linked table has the headings [BuildingID],[Building],[DateOccupied],[RateClass] and [Nights].

The "Building Details" static table has the headings [BuildingID],[Building],[Rooms] and [Condos].

[BuildingID] is an index for "Building Details" only.

In my report, I need to report the number of Tenants, Transients, Penthouse, and (calculate) Available (rooms/penthouses). I am able to build a query to pull only the Penthouses, but I need to get a total of available rooms over the course of the weekend in question (Criteria: Between[Thursday] And [Thursday]+6) to appear with the transient rate class records only. My queries are fine until this last step, when it returns ambiguously-joined errors.

Can anyone suggest another method?

 

[ThePeter]

And just like that...

I made a crosstab query with:
Row Headings [BuildingOccupancy]![BuildingID],[DateOccupied].
Column Heading [RateClass]
Value [Nights]

Then I put all the columns from both tables into one query and made calculated fields, subtracting occupancy from rooms, finally arriving at empty penthouses and empty rooms.

Boomshakalaka!