Pull max results from a query from a join

[Roy33]

I am using a small db for tracking sales data. I want to write a report that will display the sales rep that had the most sales for a certain product category.

The db is as follows (simplified)
---tbl_reps
ID_REP rep
1 joe
2 george
etc.

---tbl_category
ID_CAT cat
1 computer
2 stereo
3 dvd player
etc.

---tbl_sales
ID_sale rep category (lookup column)
1 1 1
2 1 1
3 2 1
4 2 2
5 3 3
6 4 3

I've designed queries that generate a list of all the sales data per category: (e.g the query for the computer category)
----query_computer_sales
ID_SALE rep product
1 joe computer
2 joe computer
3 george computer
4 jon computer
5 jon computer
6 jon computer
I can't figure out how to
1) first count the total number of computers sold for each rep (so for this example joe = 2, george = 1 and jon = 3)
2) and then return for my report the name and number of computer sales for the person with the most sales.

So the final goal would be to have a report:
---report_sales_leaders

Computers: Jon 3
Stereos: Jon 5
DVD Players: George 3

Thanks in advance for any advice!

 

[jrbarnett]

Roy33

You are almost there with your query. Go into design mode, open the View menu and click Totals.
Put another column on the end, set its total to Count and the field to product.

This will do exactly what you want.

John