Query pulls duplicates

[TazoTazmaniac]

Hi everyone,



Having a big problem to which I can't find the solution. I'm creating a database that involves stock, customers, deliveries and collections and I want to make a delivery report. I've spent a lot of time on making the main database and have only started on one report so far so I may be tackling it completely wrong from the beginning (please tell me if so!)

So, the report that I want to make includes a lot of fields, so I have based it on a large query containing 7 tables (the SQL for which is below). It looks very cumbersome, so not sure if this is the way to go, but have no idea how else to do it otherwise. Now, the problem is that the query comes up with duplicates of the same delivery and I'm not sure why. In fact, it has duplicated the same delivery 96 times. I suspect it is something to do with an incorrect join, but have no idea how to find out which, or even how to correct it! If more information is needed, I'll gladly give it.

Please, please, please can someone point me in the right direction - it would be much appreciated! Thank you in advance for your time.

SELECT tblDelivery.DeliveryID, tblDelivery.DeliveryDate, tblDelivery.[AM/PM], tblDelivery.Notes, tblDelivery.PropertyType, tblDelivery.Bedrooms, tblDelivery.Floor, tblDelivery.Invoice, tblDelivery.[Owned/Rented], tblDelivery.NewOccupancy, tblAllocation.NumberRequested, tblClientDonorContact.ContactFirstName, tblClientDonorContact.ContactLastName, tblClientDonorContact.ContactNumber, tblClientDonorContact.AlternativeContactNumber, tblClientDonorContact.Referral, tblClientDonorContact.ReferralContact, tblClientDonorContact.ReferralTelNo, tblClientDonorContact.ReferralInfo, tblAddresses.Address1, tblAddresses.Address2, tblAddresses.Area, tblAddresses.Postcode, tblDelivery.ContactID, tblAllocation.NumberAllocated, tblDelivery.AddressID, tblStockDescription.Description, tblOccupancyEthnicity.Occupancy, tblDelivery.TakenBy, tblDelivery.LiftAvailable, tblAllocation.RequestNotes, tblAllocation.ItemID

FROM (((tblClientDonorContact 

INNER JOIN tblDelivery ON tblClientDonorContact.ContactID = tblDelivery.ContactID) 

INNER JOIN tblOccupancyEthnicity ON tblClientDonorContact.ContactID = tblOccupancyEthnicity.ContactID) 

LEFT JOIN tblAddresses ON tblClientDonorContact.ContactID = tblAddresses.ContactID) 

INNER JOIN ((tblStock INNER JOIN tblAllocation ON tblStock.ItemID = tblAllocation.ItemID) 

INNER JOIN tblStockDescription ON (tblStock.ItemID = tblStockDescription.ItemID) AND (tblAllocation.ItemID = tblStockDescription.ItemID)) ON tblDelivery.DeliveryID = tblAllocation.DeliveryID

WHERE (((tblDelivery.DeliveryDate)=Tomorrow(Date())));

[June7]

"Duplicates" probably occur because I am guessing the query includes several tables that are the 'many' side of relationships with another table. This would be the expected output in such a query. You should probably consider report/subreport(s) arrangement.

[TazoTazmaniac]

Now you've said it, that seems like an obvious thing to do. D'oh! Thank you, I shall give that a try.

[TazoTazmaniac]

Thank you for the earlier help. I am now having a similar problem which I am unable to solve again.

I'm now getting just one set of duplicate results. Earlier, I viewed the query in datasheet mode and could see that that was where the problems lay as the duplicates were there. I have now made two seperate queries for a report and subreport and they both seem to work fine with no duplicates. However, once I put them on the report together, I get a duplicate in the detail section (most of the fields I put into the header as I only need one record per report, but stock items I put into the detail section as it could be several different items).

Not sure where to go here. Any suggestions? I can post extra information needed tomorrow if necessary, but I don't currently know what information you might need!

Thank you again for your help.

[June7]

No idea why that happens. If you want to provide db for analysis, follow instructions at bottom of my post.

[TazoTazmaniac]

I've attached my database containing fake information. The report I'm talking about is 'rptTomorrowsDeliveries2' with sub 'rptTomorrowsDeliveriesSub' and the main report is based on the query 'qryTomorrowsDelAddressAndStockReport', and I think in the end I based the sub report on tblClientDonorContact' and allowed the wizard to create the query itself. I don't think it made a difference either way I did it.

The problem on the report is that the person being delivered to only has one dressing table allocated to them, but it shows up twice on the report.

Let me know if you have any ideas. Thanks again.

SC Rebuilt Stock Database v 6.4.1.6_forum.zip

[June7]

I open rptTomorrowDeliveries2 and it shows only one dressing table record.


qryTomorrowsDeliveries has tblStockDescription linked to be tblStock and tblAllocation. Remove one of the links. Tables normally should not be linked on autonumber fields. If these two tables have a 1-to-1 relationship, why not combine to one table?

[TazoTazmaniac]

That's odd. Definitely was doing that the last couple of days! Must've done something to sort it accidently or something. Thank you for your help anyway, it's been appreciated!