"Total" problem

[Meltdown]

I have a "Products" table which has a one to many relationship on a "Lots" table. So while the customer is only buying one type of product, it can have many lot/batch numbers.

When i try to SUM the value of an order, if the product has more then one Lot number Access multiplies the value of the product by the number of related Lots.

So product A costs $10.00 and the customer bought 100 which should be $1000.00, but the 100 they bought has six different "Lot" numbers associated with it and now Access returns a value of $6000.00 for the order which is obviously wrong.

You can view part of the report here:REPORT

The LOT values are in red.

I think I need to count the number of LOTS associated with a product and divide the Total with the Count number, but I'm not sure how to do this.

Thanks for any help

 

[WayneRyan]

Meltdown,

Is your report based on a query?

If it is I think that you are missing
the "group by" aspect. If you group by
product & then lot number, you can have
a new column like:

TotalCost: [TotalNumber] * [UnitPrice]

this will be calculated for each group.

Experiment with the query and when its
right your report should be OK.

You can also check out the sorting and
grouping in your report's design.

hth,
Wayne