I am relatively new at access queries - and I wnated toknow if this is possible to be done with a query (join) and if so how....

I have 3 tables - a table with a list of users
a table with a list of books
and a third table (relational table) with a list of user numbers and book numbers based on the books the user selected to read
(a user can pick more then one book and a book can be selected more then once...)

I wanted to know if a query could be created that would give me a the complete list of books along with a count which tells me how many times the book is found on the third table (how many people selected that book to read...) so if two people wanted to read that book there would be a 2 count and if no one choose to read that book there would be a 0 count ?

is this possible in one query? How would be the most efficient way to achieve this list of info?

thank you for your help

Elladi

You would want to do this in the Query designer if you are new to SQL.

Open the query designer and select two tables, the Books table, and the selection junction table. The relationship between the tables should be visible between the two tables.

On the text menu, select View > Totals.

Select the Book Name field in the books table, then select the ID field in the junction table. Change the Total for this field to Count.

Select the relationship between the two tables, and check the check box that says "Show all records in the book table, and only those records in the junction table..."

When you run this query, it will show all the books, and then give you a count for all the times the book was selected. If the book was never selected, the field will be Null, and not have a 0 in it.

I know there is a way to add the 0, but I am not in front of Access right now.

Did that help?

mmcdonal

Look it up at: http://wrox.books24x7.com

Thank you I just got the query to work - it is perfect...by the way there is a 0 in the count field.

That's great about the 0. I thought it might not show up in ANSI 89.

mmcdonal

Look it up at: http://wrox.books24x7.com