# Calculation on Group

#### ThePeter

##### New Member
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?

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

#### ThePeter

##### New Member
And just like that...

I made a crosstab query with:
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!

Last edited:

1,191,559
Messages
5,987,288
Members
440,087
Latest member
Ruppert23

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back