help needed with a count(?) query? (1 Viewer)

martinr

Registered User.
Local time
Tomorrow, 02:04
Joined
Nov 16, 2011
Messages
70
I have a set of records that is a list of borrowed books (book_id) and the borrower's details (member_id, category).
Each borrower belongs to a membership category (ie, Student, Teacher, Pensioner).

We want to write a query to identify, for each book, how many times it was borrowed by
members of each category:

Book A = 2 x teacher loans,
Book A = 5 x student loans,
Book A =3 x Pensioner loans.
Book C = 4 x teacher loans,
Book C = 0 x student loans,
Book C = 8 x Pensioner loans

Could we also identify books that had only ever been borrowed by members of one category (ie, Book B has only ever been borrowed by Teachers)?

Book B = 4 x teacher loans,

Book B = 0 x student loans,
Book B = 0 x Pensioner loans

Another variation of this could be, which books have never been borrowed by members of a certain category
(books that have never been borrowed by a student)?

Book B = 0 x student loans,
Book C = 0 x student loans,

Thanks for any help or suggestions as to the best way to write these queries.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 10:04
Joined
May 21, 2018
Messages
3,643
In query design add Book,Catgory, Book_ID then up at the top of the design tab pick the summation (Total) sign to make an aggregate query. Then under Book and Category choose "Group By" under book_ID choose "Count"
 

plog

Banishment Pending
Local time
Today, 09:04
Joined
May 11, 2011
Messages
10,128
You asked for 3 queries. MajP gave you the first.

#2 identify books only borrowed by one type

Copy MajPs query into a new query. In Design view of the query change the "Group By" under the category to "Max". Then you must go into the actual SQL (upper left hand corner 'View - SQL View') and put this after the GROUP BY query:

HAVING MAX(category) = MIN(category)

run it and you have books lent to just one member category

#3. Books Never borrowed.

For this you need a list of all books in a table, lets call that tblBooks. You build a query on that and your borrowed books table (tblBorrowed), joining them by bookid. Then you must right click on the join line and go to 'Join Properties' and change it to the option that shows all from tblBooks. In the bottom part bring in the bookid from both tables and under the one from tblBorrowed put "Is Null" in the criteria. Run it and it will show all the bookid values that haven't been borrowed
 

arnelgp

error reading drive A:
Local time
Today, 23:04
Joined
May 7, 2009
Messages
10,871
using Crosstab Query, you can see All the Details in a glance.
you can further Create another Query for a Special purpose as what you require:

Code:
TRANSFORM Count(yourLoanTable.book_id) AS CountOfbook_id
SELECT yourLoanTable.book_id
FROM yourLoanTable
GROUP BY yourLoanTable.book_id
PIVOT yourLoanTable.category;
snap1.png
 
Last edited:

martinr

Registered User.
Local time
Tomorrow, 02:04
Joined
Nov 16, 2011
Messages
70
Thanks MajP and plog for your help.

The first query returns the number of loans for each book per membership type, which is great.
1602812821034.png


The second and third queries are a bit different...
If I change one of the aggregate types to Max it just returns the max value of the respective values in the list, not the max of the counted data (it’s selected Student based on it’s alphabetic value);
We can see from Query 1 that Students have not borrowed Bookid # 34, 4 times.
1602812881711.png

1602812895198.png


The third query is trying to identify the books that had only ever been borrowed by
members of a single category (ie, Book B has only ever been borrowed by Teachers, never by students)?
 

martinr

Registered User.
Local time
Tomorrow, 02:04
Joined
Nov 16, 2011
Messages
70
Thanks arnelgp, your solution is working well!
 

martinr

Registered User.
Local time
Tomorrow, 02:04
Joined
Nov 16, 2011
Messages
70
is there a way to get the same results without using Transform?
 

Users who are viewing this thread

Top Bottom