2

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

1

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;
  • Great help so far, Pieter! Thank you! I got this all set up but how can I incorporate the 'MCategory' field from the 'Machine' table? Can I do this is the master query or should I do it on the report? Either way I can't figure out how to get it in there so the category data is pulled in to the report. Thanks! – user1340081 Aug 3 '14 at 16:11
  • @user1340081: As all items are (by definition) in inventory, I would simply incorporate that into the QInventory query and add the field to the top-level join. Also, the JOIN in the QRented definition can be an INNER JOIN (change made above) which likely will improve its performance. – Pieter Geerkens Aug 3 '14 at 16:14
  • @user1340081: Remember the technique - it is widely applicable. If you upscale a real RDBMS you can code the separate queries into the main one using CTE's as embedded sub-queries. – Pieter Geerkens Aug 3 '14 at 17:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.