Combining count of records for like DB entries

[TheKepper]

Hi All,
I am creating a movie database for my aunt's business that will help her keep track of her rentals. One of the things she would like to do is to have a count of the total number of times a movie title was rented. She has a library of over 1700 movies at the moment, and some of them have the same title. For example, movie numbers 1700, 1701, 1702, and 1703 all have the same title (for example: "Shrek&quot
I created a set of procedures (and forms, and queries, etc) that allow her to enter a movie to be checked out. It then writes an entry into what I call a transaction table. It contains the movie id, date, and a few other fields.
I generated a query on the transaction table to list all of the movies that were rented and set the "Group By"->Count. This will generate a count of the number of like movie ids. So,in the Shrek example, each movie shows up (1700, 1701, 1702, etc) with the number of times each of them were rented.
My question is this: How can I combine the total count for each movie title (ie. the count of 1700 + the count of 1701 + the count of 1702) in a query and use it in a form. I am attempting to do this in a form "On Load" event procedure (for displaying a "top 10 rentals&quot, but I am not fluent enough in VBA yet.
Any help would be greatly appreciated.
Thanks,
TheKepper

 

[braindead2]

My suggestion would be don't group by id, group on titles.
You may have to do a join between transactiontbl and table that has title name to get it but should be simple enough if you have a proper database design

SELECT TOP 10 Count(title) AS rentalcnt
FROM yourquery
GROUP BY title
ORDER BY Count(title)DESC;

 

[TheKepper]

Hi Braindead2,
I tried what you said and it got me a little closer to what I want to do. Actually, it absolutely worked, but I had to insert a step before hand in my process. Among the many fields in the transaction table is a field called txdate. I added a criteria of "Between #01/01/02# And #01/31/02# on txdate in the query. After running the query above, it returned records that had separate dates in the record. For example, there were entries in the table for 1701 on 01/19, 01/20, 01/21, and 01/22. It didn't include these in the count for some reason. I even un-checked it in the design query window and it still returned a record for the numbers and the dates they were rented. What I had to do was create a make table query that returned all of the rentals for the date criteria mentioned above. Then, I ran the query based on your suggestion above on the table that was created and it worked.
I hoped all of that made sense. Thanks for your help.
-TheKepper