Query showing repeats of the same record

[xian2]

Hi All,

I have created a query with 6 related tables as the record source.

The query includes 4 fields from 4 of the tables:

tblTourBookings
TourStartDate

tblAdditionalCosts
Price

tblCostDetails
Quantity

tblToursOffered
DayPrice

there is another field in the query which I made up:
Total Price: [DayPrice]+([Price]*[Quantity])

The problem occurs when I run the query, the same date can appear up to 4 times (even when I use the TourID primary key in the query, it is just repeated).

i have a perameter query under tourstartdate :

Between [Please enter Start Date] And [Please enter End Date]

This works correctly when the query is run.

Any help on why this may be occuring is greatly appreciated.

Many thanks in advance

best wishes

Edd

[pks00]

Can u post your query?

u may get away with using DISTINCT or u might need a inner query

[xian2]

Hi, the query is:

SELECT tblTourBookings.TourStartDate, tblAdditionalCosts.Price, tblCostDetails.Quantity, tblToursOffered.DayPrice, [DayPrice]+([Price]*[Quantity]) AS [Total Price]
FROM tblToursOffered INNER JOIN ((tblClient INNER JOIN (tblAdditionalCosts INNER JOIN (tblTourBookings INNER JOIN tblCostDetails ON tblTourBookings.TourID = tblCostDetails.TourID) ON (tblAdditionalCosts.AdittionalCostID = tblCostDetails.AdditionalCostID) AND (tblAdditionalCosts.AdittionalCostID = tblCostDetails.AdditionalCostID)) ON (tblClient.[Client ID] = tblTourBookings.ClientID) AND (tblClient.[Client ID] = tblTourBookings.ClientID)) INNER JOIN tblTourDetails ON tblTourBookings.TourID = tblTourDetails.TourID) ON (tblToursOffered.ToursOfferedID = tblTourDetails.ToursOfferedID) AND (tblToursOffered.ToursOfferedID = tblTourDetails.ToursOfferedID)
WHERE (((tblTourBookings.TourStartDate) Between [Please enter Start Date] And [Please enter End Date]));

Many thanks for your response

Edd

[xian2]

That code is very long so I thought I would post it as text as well to make it easier to read (well maybe, depending on how you like things)

SELECT tblTourBookings.TourStartDate, tblAdditionalCosts.Price, tblCostDetails.Quantity, tblToursOffered.DayPrice, [DayPrice]+([Price]*[Quantity]) AS [Total Price]
FROM tblToursOffered INNER JOIN ((tblClient INNER JOIN (tblAdditionalCosts INNER JOIN (tblTourBookings INNER JOIN tblCostDetails ON tblTourBookings.TourID = tblCostDetails.TourID) ON (tblAdditionalCosts.AdittionalCostID = tblCostDetails.AdditionalCostID) AND (tblAdditionalCosts.AdittionalCostID = tblCostDetails.AdditionalCostID)) ON (tblClient.[Client ID] = tblTourBookings.ClientID) AND (tblClient.[Client ID] = tblTourBookings.ClientID)) INNER JOIN tblTourDetails ON tblTourBookings.TourID = tblTourDetails.TourID) ON (tblToursOffered.ToursOfferedID = tblTourDetails.ToursOfferedID) AND (tblToursOffered.ToursOfferedID = tblTourDetails.ToursOfferedID)
WHERE (((tblTourBookings.TourStartDate) Between [Please enter Start Date] And [Please enter End Date]));

Thanks again

Edd

[pks00]

Is the price, quantity and dayprice the same for each tourdate?

do u get the desired results if u place a distinct?

i.e..

SELECT DISTINCT tblTourBookings.TourStartDate

[xian2]

Is the price, quantity and dayprice the same for each tourdate?

do u get the desired results if u place a distinct?

i.e..

SELECT DISTINCT tblTourBookings.TourStartDate

Hi,

Many thanks for your reply.

The DISTINCT bit helped and the price and quantity are the additional costs like ferry crossings which there could be 2 of. Whereas the dayprice is for the tour for which each day can only be put once.

The DISTINCT query sorted it down to only two rows, one for the Dayprice and one for the additional costs. The only problem is that each row has the same total.

i think I may need to make two queries and build them as subforms into a report.

Any other suggestions are very welcome

Best

Edd

[NeoPa]

DISTINCTROW will lose multiple items that already exist inside a table.
Properly linking the tables together will mean that multiple items are not re-shown.

You need to consider this carefully.