Count query help

[ScrmingWhisprs]

I work at a library and I have an Access DB that I use as an active shelflist (all the items that are in our library). I have a query that breaks down the collection count, number of items in that collection currently checked out, and the number of items in that collection set to storage.

Basically I have 4 queries: 1 for each of the counts (Total collection count, checked out items count, and storage count), then the 4th query pulls in each of those counts into one datasheet for each collection.

My problem is that if one of the collections does not appear in any one of the first 3 queries, then it does not appear at all in the 4th query. For example, we have 130 total items in the Juvenile Graphic Novels Collection. Of the 130 items, there are 13 checked out. 0 of the 130 items are in storage. Now when I bring up the query that brings those 3 queries together, the row for that collection does not appear because there are 0 items in storage.

Maybe there is a better way than actually creating 4 different queries?

 

[April15Hater]

Could you post the SQL code for the queries?

 

[Datagopher]

I'm assuming that you're setting a relationship on the 3 queries in the 4th query, and you have it set to display only records where theres matches. If that's the case what's happening is if theres one query without an entry then that record will be omitted all together.

An easy way to do this if I'm understanding you correctly though it might be better to post your actual code and provide a little more insight as April had suggested would be to create one query with the fields that you want the calculation performed on and do a sum on each one of those fields. Your SQL would be similar to this.

SELECT Sum(Table1.Amt1) AS SumOfAmt1, Sum(Table1.Amt2) AS SumOfAmt2, Sum(Table1.Amt3) AS SumOfAmt3
FROM Table1;