Totals from queries into the detail section

[JJrunner]

I am fairly new to Access, and I have a problem that sounds easy, and probably is, but I have been at it for a couple of days and my head is spinning!!!! I can't see the forest for the trees....

Anyway, here is my problem:

I have Information that I want from two tables onto a report. I have a table called "Sales" and one called "Purchases". These tables have a common field called productID that comes from the "Product" table. Each of the two tables have a numberofunits sold/purchased and the price sold/purchased field. I want to have a report that will simply give me the product ID, and a total units purchased, total units sold, and a total units available. I have a form with subforms that works great on an individual product, but I need a summary report that shows all products. I have tried many different types of queries, but cant get them to work. I end up with information duplicated, or cant get the totals to flow to the report. Like i said, this sounds easy, and I am sure I am missing something obvious, but I cant see it. Thanks in advance for your help.

 

[genomon]

Check out DCount and DSum domain aggregate functions in Access help.

 

[JJrunner]

Thanks, that helped some, however i have put that in the detail section, and i have my report set to group on Product ID. The DSUM fields dont change totals with the change in Product ID.

 

[dhookom]

I would create a totals query from the Sales table that groups by the ProductID and sums the number of units sold etc. Do the same for the Purchases table. Then create a query from the Product table combined with the two totals query. Join the ProductIDs and edit the join properties to include all the records from the Product table.

Duane
MS Access MVP

 

[JJrunner]

Thanks, I have tried that and it did work. I really appreciate your help!