Basic one to many problem

[Stuart Green]

I have a one to many set up. On the one table I record freight charges, on the many individual line item slaes and their price. Net result is that on screen I have the total sales value on a subform and the freight charge on the main form, all very straight forward so far. I want a report that simply gives me the total sales in one column and the freight charges in another. If we take the scenario that there are 10 invoices each with 3 line items, then I have 30 records in the many and 10 in the one. I have tried basing the report on a query but I get 30 lines of data with the freight charge repeated 3 times for each line that exists in the many. End result is that the sales figure is OK but the freight is overstated to the power 3. As a work around I thought to record the sales value sum onto my "one" table but failed miserably but I don't really want to do this. Any suggestions please. It has the feeling of being something quite basic but the lights are out at the moment!

 

[Rich]

Try not allowing duplicates or use a subreport for freight