Relationships Causing Problems with Reporting.

[SamDemon]

I was wondering if someone wouldn't mind taking a look at my tables and relationships for me please, I've got a funny feeling that they are causing the problems with my reports.

The problem that I am currently experincing is:-

I attempt to create a report using a multiple table query. When I run the query all the details appear in the datasheet view fine, but when I try to create a report using the said query, I am asked to enter parameter for fields that I have not set parameters for. Removal of one or more tables stops this occuring, but the problem is that I need all the information from those tables to complete the report.

My tables are as follows:-

tblBookingClass
BookingClass / Text (Primary Key)

tblBookingStatus
BookingStatus / Text (Primary Key)

tblBookingType
BookingType / Text (Primary Key)

tblCAT
CAT / Text (Primary Key)
TicketValue / Currency
CommissionValue / Currency

CompanyDetails
ATOL / Text (Primary Key)
IATA / Text
AccountNo / Text
CompanyName / Text
Address 1 / Text
Address 2 / Text
Address 3 / Text
Address 4 / Text
Postcode / Text
Telephone / Text
Fax / Text
MainEmail / Text
WebSite / Text
MemberOfTrade / Yes-No
BookingGroups / Yes-No
Business Type / Text (BusinessType)
AdHocSales / Yes-No
CoachTours / Yes-No
Cruises / Yes-No
ReadOffer / Yes-No
PackageTours / Yes-No
Other / Text

tblDestinationCalculation
3LetterCode / Text
Destination
Category

tblFlight
FlightID / AutoNumber (Primary Key)
GroupBookingID / Number (GroupBookingID)
DepartureDate / Time-Date
FlightNo - Text
DepartDestination / Text (3LetterCode)
ArrivalDestination / Text (3LetterCode)
BookingClass / Text (BookingClass
NoSeats / Number

tblGroupBooking - Main Table
GroupBookingID / Autonumber (Primary Key
DateOfEnquiry / Date-Time
StaffName / Text (LimeStaffName)
Status / Text (BookingStatus)
PNR / Text
CompanyName / Text (CompanyName)
CompanyRep / Text
Source / Text (Source)
GroupName / Text
PAX / Number
CostingDate / Date-Time
CostingDestination / Text (3LetterCode
SingleNet / Currency
SingleTax / Currency
ExtraCommission / Currency
CommissionDiscount / Currency
TicketDiscount / Currency
InitialDeposit / Currency
GroupSplit / Yes-No
OriginalGroupID / Number
CostSalePax / Number
CostSaleNetFare / Currency
CostSaleTaxRefund / Currency
InvoiceNo / Text
AddCostPax / Number
AddCostNetFare / Currency
AddCostCxlFee / Currency

tblStaffDetails
StaffId / Autonumber
StaffName / Text
DirectTelNo / Text
DirectFaxNo / Text
Email / Text

tblSourceBooking
Source / Text (Primary Key

I hope this makes sense. Any help will be gratefully appreciated.

Kind Regards

Sam


It's just common sense, shame sense isn't common!

 

[SQLSister]

Personally I'm a big fan of using subreports to handle related details.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[willir]

I am asked to enter parameter

You may wish to check your spelling. Often when prompted for a parameter is indicative a typing mistake.

 

[SamDemon]

I understand that, but 'Status' is not used anywhere in the query, i.e. in one of my calculations.

It may be best for me to try and explain what I am trying to achieve from my query.

I am using 5 tables in my query, these are:-

tblGroupBookings
tblFlight
tblDestinationCalculation
tblCompanyDetails
tblCAT

The query is being used to create a report which will be sent out to the client to inform them of their 'Group Booking Offer'. The report will need to contain the following information:-

PNR
COMPANY NAME
ATOL
GROUP NAME
FLIGHT DETAILS
NETT GROUP RATE
NETT GROUP TAX
TICKET FEE

Hope this makes things a little clearer.

Thanks in advance!!

Sam

It's just common sense, shame sense isn't common!

 

[lespaul]

why don't you post the SQL?

Leslie

 

[SamDemon]

SELECT tblGroupBooking.GroupBookingID, tblGroupBooking.PAX, tblGroupBooking.SingleNet, tblGroupBooking.SingleTax, [SingleNet]*[PAX] AS TotalNetFare, [SingleTax]*[PAX] AS TotalNetTax, [TotalNetFare]+[TotalNetTax] AS TotalFareAndTaxes, [PAX]*[CommissionValue] AS GroupCommission, tblGroupBooking.ExtraCommission, tblGroupBooking.CommissionDiscount, ([GroupCommission]+[ExtraCommission])-[CommissionDiscount] AS TotalGroupCommission, ([PAX]*[TicketValue])-[TicketDiscount]*[PAX] AS TotalTicketFee, tblGroupBooking.TicketDiscount, tblCompanyDetails.CompanyName, tblCompanyDetails.Address2
FROM (tblCAT INNER JOIN tblDestinationCalculation ON tblCAT.CAT = tblDestinationCalculation.Category) INNER JOIN (tblCompanyDetails INNER JOIN tblGroupBooking ON tblCompanyDetails.ATOL = tblGroupBooking.ATOL) ON tblDestinationCalculation.[3LetterCode] = tblGroupBooking.CostingDestination
WHERE (((tblGroupBooking.GroupBookingID)=[Enter ID]))
ORDER BY tblGroupBooking.GroupBookingID;


It's just common sense, shame sense isn't common!