Ms-access – Combining count queries into one table row

countms accessreporting

I'm banging my head against the wall because I'm sure this is super easy.

I have a few queries set up that get counts for a few related tables. This is for an arcade game tracking system. The queries look at two tables and returns counts for how many of each game is in inventory

(SELECT Machine.MName, Count(Machine.Mid) AS CountOfMID
FROM Machine
GROUP BY Machine.MName
ORDER BY Machine.MName;)

and another for how many games are out for rent, again it counts the number of each game (multiple copies) that are out for rent:

(SELECT Machine.MNAME, Count(Rental.Mid) AS CountOfMID1
FROM Machine LEFT JOIN Rental ON Machine.[MID] = Rental.[MID]
GROUP BY Machine.MNAME;)

My question is how do I make a report that has the each game listed and then out to the side have the appropriate counts pulled in? Something similar to this is what I'm after:

CATEGORY --- NAME ------ INVENTORY ------ AVAILABLE ------ RENTED
Video........Frogger.........2...............1..............1.....
.............Metroid.........5...............1..............4....
Pinball......Simpsons........10..............8..............2....

I'm currently putting all of my bounded controls in the MName HEADER of my report for grouping and I just need this last piece to put these counts in the same column. I have tried using the control source for a bounded control to be a sql query just like I had in the actually saved query and it just pops up with a "enter parameter" box meaning I worded it wrong or something.

I'm wondering if I need to put the data in the "details" section of the report to tie each row in to the tables in my queries. Not sure how to do any of this and I've tried everything and googled all night.

Any help would be greatly appreciated. Pictures below:

Relationships
One of the Queries
Report Design View
Report - Almost Done

Best Answer

Define Query objects (call them QRented and QInventory) with SQL definitions as you have provided:

(SELECT Machine.MNAME, Count(Rental.Mid) AS CountOfMID1
FROM Machine JOIN Rental ON Machine.[MID] = Rental.[MID]
GROUP BY Machine.MNAME;)

and this:

(SELECT Machine.MName, Count(Machine.Mid) AS CountOfMID
FROM Machine
GROUP BY Machine.MName
ORDER BY Machine.MName;)

Now create a combined query from your Query objects like this:

SELECT QInventory.MName, QInventory.CountOfMID as Inventory,
    QInventory.CountOfMID  - QRented.MIDI as Available,
    QRented.MIDI  as Rented
FROM QQInventory
LEFT JOIN QRented on QQInventory.MName = QRented.MName
ORDER BY Machine.MName;
Related Question