In QueryAnalyzer I have the following simple statement:
SELECT SUPPLIER.SUPPLIER_ID, PARTS.PARTS_ID
FROM SUPPLIER INNER JOIN
PARTS ON SUPPLIER.SUPPLIER_ID = PARTS.SUPPLIER_ID
GROUP BY PARTS.PARTS_ID, SUPPLIER.SUPPLIER_ID
HAVING (SUPPLIER.SUPPLIER_ID = 1020)
This returns the following recordset:
SUPPLIER.SUPPLIER_ID PARTS.PARTS_ID
1020 02
1020 07
1020 16
What I ultimately wish to achieve is a recordset that would simply give me the SUPPLIER_ID and a sum of the parts. For example, the above supplier has three distinct parts, therefore, I would expect the recordset to look like this:
SUPPLIER.SUPPLIER_ID TOTAL_PARTS
1020 3
Something tells me this is probably fairly easy to achieve yet I seem to be spinning my wheels. Any help would be much appreciated!
Thanks in advance.
SELECT SUPPLIER.SUPPLIER_ID, PARTS.PARTS_ID
FROM SUPPLIER INNER JOIN
PARTS ON SUPPLIER.SUPPLIER_ID = PARTS.SUPPLIER_ID
GROUP BY PARTS.PARTS_ID, SUPPLIER.SUPPLIER_ID
HAVING (SUPPLIER.SUPPLIER_ID = 1020)
This returns the following recordset:
SUPPLIER.SUPPLIER_ID PARTS.PARTS_ID
1020 02
1020 07
1020 16
What I ultimately wish to achieve is a recordset that would simply give me the SUPPLIER_ID and a sum of the parts. For example, the above supplier has three distinct parts, therefore, I would expect the recordset to look like this:
SUPPLIER.SUPPLIER_ID TOTAL_PARTS
1020 3
Something tells me this is probably fairly easy to achieve yet I seem to be spinning my wheels. Any help would be much appreciated!
Thanks in advance.