Hi all,
I have a group booking. This contains a number of different bookings (BookingIDs) under the one group(#). A particular booking will have a single booking fee. A booking can contain a number of line items.
I want a report that will show all bookings under a single group and show all lines relating to each booking, as well as show the booking fee/s that apply. E.G.
|
Group# |
BookID |
BookFee |
|
ABC |
123 |
5 |
|
ABC |
456 |
10 |
|
ABC |
789 |
0 |
|
BookID |
Desc |
|
123 |
B&B |
|
123 |
Full Board |
|
123 |
Half Board |
So Group# ABC contains 3 Bookings: i. 123 has a BookingFee of €5 and contains 3 line items ii. 456 and iii. 789 have 1 line each with 456 having a €10 BookingFee.
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’));
returns:
|
Group# |
BookID |
BookFee |
Desc |
|
ABC |
123 |
5 |
B&B |
|
ABC |
123 |
5 |
Full Board |
|
ABC |
123 |
5 |
Half Board |
|
ABC |
456 |
10 |
B&B |
|
ABC |
789 |
0 |
B&B |
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.
IP Logged
