A query to accumulate data

[Guest]

Hi,

I have two tables, PARTS MASTER and SALES DETAIL. SALES DETAILS records all
sales (qty sold) of PARTS. I would like to generate one summary query
showing all parts sold and the price (from PARTS MASTER) and the extended
value. Right now we are running a large detail query/report. I would
appreciate any ideas on how to do this.

Thanks.

 

[tina]

link the two tables in a query, with an INNER JOIN from the "parts" foreign
key field in the SalesDetails table to the "parts" primary key field in the
Parts table. add the fields you want to see in the summary, from both
tables. on the toolbar, click the Totals button (looks like a funny capital
E, or a capital M on its' left side). Group By the Parts field(s), including
the price field, and Count the primary key field of the Sales table.

the result should be a listing of each part that was sold, and it's price,
with a count of the total number sold for each part that's listed. if you
want to see all parts listed, whether or not there were any sales for some
parts, then change the INNER JOIN to a LEFT JOIN.

hth