Problem SUM()

[mel_flynn]

Hi all,

I have 2 tables. i. tblBookHdr - containing my Booking Header Information incl. GroupNo, BookID and BookFee. ii. tblBookDets - Containing BookID (links to tblBookHdr) and Desc.
A number of different bookings (BookingIDs) may be made under the one group(#). A booking may have a booking fee. A booking can contain a number of line items.

So Group# ABC contains 3 Bookings: i. 123 has a BookingFee of €5 and contains 3 line items ii. 456 has a Booking Fee of €10 and 1 line item and iii. 789 has no Booking Fee and 1 line.

I have my report A. by Group# and B. by BookID. I am showing my BookDets in the report Details section and I want to show a Sum of my Booking Fee in the Group# Footer Section. My total Booking Fee should be €15 however it is showing €25. This is because the data selection for my report i.e.
SELECT tblBookHdr.ID, tblBookHdr.GroupNo, tblBookHdr.BookingFee, tblBookDets.Desc FROM tblBookHdr INNER JOIN tblBookDets ON tblBookHdr.ID = tblBookDets.BookingID WHERE (((tblBookHdr.GroupNo)=’ABC’));

So the report is showing a total Booking Fee of €25. How do I fix the report to display the correct amount?

Thanking you in advance,
Mel.

[mel_flynn]

Can anyone help with this? If I haven't explained the problem clearly enough please let me know.

Thanks.

[brucevde]

A SUM is evaluated/affected by every record and since the same booking fee is repeated for each line item its value is calculated incorrectly.

ABC 123 5 Item 1
ABC 123 5 Item 2
ABC 123 5 Item 3
ABC 456 10 Item 1
ABC 789 0 Item 1

Use a Running Total, which has the option to specify when the total is updated.