Help - Join Query for total count

  • Thread starter Thread starter devprog
  • Start date Start date
D

devprog

Hi,

Need help here, thanks.

Table 1:

lookupID CustomerName
-------- ----------
1 John
1 Joe
2 Helen
3 Steve
4 Albert
4 Thomas
5 Jay
5 Olson
6 Jack

Table 2:

LookupID ProductCatagory
--------- ---------
1 TV
2 PC
3 Range
4 AC
5 Fan
6 Microwave


Question:

How can I come up a single query that will return me the result as
below. Basiclly it will show me how many items has been sold
in each product catagory. (Please note that the result is shonw
the Product's Name , not its ID.)

(Desired Result as below)

ProductName Sold
------------- ----
TV 2
PC 1
Range 1
AC 2
Fan 2
Microwave 6


Thanks for reading this request for help.

MT
 
SELECT ProductCatagory, Count(CustomerName) as NumSold
FROM Table1 As A INNER JOIN Table2 as B
ON A.LookupID = B.LookupID
GROUP BY ProductCatagory

IF you need to build the query using the query grid,
Put both tables into the query
Drag from lookupID in one table to lookupid in the other table.
Put ProductCatagory and CustomerName in the fields
SELECT View: Totals from the menu
In the totals line, change Group By to Count under the CustomerName field.
 
Back
Top