Duplicate Records resulting from a Query

[LivewellMCPH]

Hello - I've tried finding the correct thread so I wouldn't have to post a new one, but here we are. I am trying to figure out why the following query returns 32 rows for each InspectionID. The report is an invoice we send out, but a few-page report is turning out to be hundreds of pages long, all repeats of the desired output. Thank you in advance. The SQL follows:

SELECT qryInspection1.InspectionID, qryInspection1.PoolID, qryInspection1.PoolLocationID, qryInspection1.InspectionDate, qryInspection1.InspectorName, qryInspection1.InspectionOperatorName, qryInspection1.InspectionCPOName, qryInspection1.PoolFacilityName, qryInspection1.PoolAddress, qryInspection1.PoolCity, qryInspection1.PoolState, qryInspection1.PoolZip, qryInspection1.Owner, qryInspection1.OwnerAddress, qryInspection1.OwnerCity, qryInspection1.OwnerState, qryInspection1.OwnerZip, qryInspection1.PoolName, qryInspection1.PoolType, qryInspection1.PoolInsideOutside, tblInspection1.Inspection1ID, tblInspection1.[1a], tblInspection1.[1aDisinfectionType], tblInspection1.[1b], tblInspection1.[1bGasChlorination], tblInspection1.[1c], tblInspection1.[1cTurbidity], tblInspection1.[1d], tblInspection1.[1dTC], tblInspection1.[1dFC], tblInspection1.[1dCC], tblInspection1.[1e], tblInspection1.[1eBr], tblInspection1.[1eOther], tblInspection1.[1f], tblInspection1.[1fpH], tblInspection1.[1g], tblInspection1.[1gTotalAlkalinity], tblInspection1.[1h], tblInspection1.[1hCalciumHardness], tblInspection1.[1i], tblInspection1.[1iTemperature], tblInspection1.[1j], tblInspection1.[1jSaturationIndex], tblInspection1.[1k], tblInspection1.[1kCyanuricAcid], tblInspection1.[1l], tblInspection1.[1lORP], tblInspection1.WaterQualityComment, tblInspection2.Inspection2ID, tblInspection2.[2a], tblInspection2.[2aPostingProvided], tblInspection2.[2b], tblInspection2.[2c], tblInspection2.[2cBatherCapacity], tblInspection2.BatherControlComment, tblInspection3.Inspection3ID, tblInspection3.[3a], tblInspection3.[3aDepthMarkers], tblInspection3.[3b], tblInspection3.[3bDeck], tblInspection3.[3c], tblInspection3.[3cSkimmersGutters], tblInspection3.[3d], tblInspection3.[3dInlets], tblInspection3.[3e], tblInspection3.[3eMainDrains], tblInspection3.[3f], tblInspection3.[3fDivingBoards], tblInspection3.[3g], tblInspection3.[3gEmergencyShutdown], tblInspection3.[3h], tblInspection3.[3hElectricHazards], tblInspection3.[3i], tblInspection3.[3iShowerRooms], tblInspection3.[3j], tblInspection3.[3jSecureFencing], tblInspection3.[3k], tblInspection3.[3kClean], tblInspection3.[3l], tblInspection3.[3lGoodRepair], tblInspection3.[3m], tblInspection3.[3mADACompliant], tblInspection3.PoolFacilitiesComment, tblInspection4.Inspection4ID, tblInspection4.[4a], tblInspection4.[4aMechSecure], tblInspection4.[4b], tblInspection4.[4bMechAccessible], tblInspection4.[4c], tblInspection4.[4cMechClean], tblInspection4.[4d], tblInspection4.[4dTestKit], tblInspection4.[4f], tblInspection4.[4fMechRecords], tblInspection4.MechComment, tblInspection5.Inspection5ID, tblInspection5.[5a], tblInspection5.[5aPump], tblInspection5.[5b], tblInspection5.[5bFilterType], tblInspection5.[5c], tblInspection5.[5cFilterSize], tblInspection5.[5d], tblInspection5.[5dCrossConnection], tblInspection5.[5e], tblInspection5.[5eRPPCertification], tblInspection5.[5f], tblInspection5.[5fGauges], tblInspection5.[5g], tblInspection5.[5gPipingCoded], tblInspection5.[5h], tblInspection5.[5iCalc], tblInspection5.[5hFlowRate], tblInspection5.[5i], tblInspection5.[5iTurnoverRate], tblInspection5.[5j], tblInspection5.[5jAutomaticFeed], tblInspection5.[5k], tblInspection5.[5kBackwash], tblInspection5.RecirculationComment, tblInspection6.Inspection6ID, tblInspection6.[6a], tblInspection6.[6aChemLocation], tblInspection6.[6b], tblInspection6.[6bChemSeparate], tblInspection6.[6c], tblInspection6.[6cChemSecure], tblInspection6.[6d], tblInspection6.[6dChemContainer], tblInspection6.[6e], tblInspection6.[6eChemHandling], tblInspection6.[6f], tblInspection6.[6fChemTestKit], tblInspection6.ChemicalComment, tblInspection7.Inspection7ID, tblInspection7.[7a], tblInspection7.[7aImminentThreatClose], tblInspection7.[7b], tblInspection7.[7bBacteriaClose], tblInspection7.[7c], tblInspection7.[7cDisinfectionClose], tblInspection7.[7d], tblInspection7.[7dClosurepH], tblInspection7.[7e], tblInspection7.[7eTurbidityClose], tblInspection7.[7f], tblInspection7.[7fFecalClose], [OwnerCity] & ", " & [OwnerState] & " " & [OwnerZip] AS MailFull, [PoolCity] & ", " & [PoolState] & " " & [PoolZip] AS PoolAddressFull, qryInspection1.PoolCapacity, tblInspection2.[2bRescueEquipment], qryInspection1.DateEntered
FROM ((((((qryInspection1 LEFT JOIN tblInspection2 ON qryInspection1.InspectionID = tblInspection2.InspectionID) LEFT JOIN tblInspection1 ON qryInspection1.InspectionID = tblInspection1.InspectionID) LEFT JOIN tblInspection3 ON qryInspection1.InspectionID = tblInspection3.InspectionID) LEFT JOIN tblInspection4 ON qryInspection1.InspectionID = tblInspection4.InspectionID) LEFT JOIN tblInspection5 ON qryInspection1.InspectionID = tblInspection5.InspectionID) LEFT JOIN tblInspection6 ON qryInspection1.InspectionID = tblInspection6.InspectionID) LEFT JOIN tblInspection7 ON qryInspection1.InspectionID = tblInspection7.InspectionID
WHERE (((qryInspection1.PoolLocationID) Like [Forms]![frmPickDate].[cboCustomerID]) AND ((qryInspection1.InspectionDate) Between [Forms]![frmPickDate]![txtStartDate] And [Forms]![frmPickDate]![txtEndDate]))
ORDER BY qryInspection1.InspectionDate;

Open this in a new tab for an expanded version

[Bulzie]

Seems like all those tables could be normalized, 1 table with a field for each separate measure and one field to say if it is 1a, 1b, 2a, etc. But if not, try using the Totals button in ribbon. Can try it on that last query or might have to create a 3rd query based on the 2nd one, add all fields and use the Totals button.

[June7]

Why 7 inspection tables? How many inspection items are there? What are the fields named 1a, 2a, 3a, etc for?

If these 7 inspection tables each have a 1-to-1 relationship with tblInspection then they should all have the same number of records as tblInspection and your query should work.

Why are you joining the 7 tables to qryInspection1 - why not to tblInspection? What is the SQL for qryInspection1?

[LivewellMCPH]

This DB was created long before I started working with it. I was just asked to figure out why it's not working properly.

SQL for qryInspection1:
SELECT tblInspection.InspectionID, tblInspection.PoolID, tblInspection.InspectionDate, tblInspector.InspectorName, tblInspection.InspectionOperatorName, tblInspection.InspectionCPOName, tblPoolLocation.PoolFacilityName, tblPoolLocation.PoolAddress, tblPoolLocation.PoolCity, tblPoolLocation.PoolState, tblPoolLocation.PoolZip, tblPoolLocation.Owner, tblPoolLocation.OwnerAddress, tblPoolLocation.OwnerCity, tblPoolLocation.OwnerState, tblPoolLocation.OwnerZip, tblPoolInfo.PoolName, tblPoolType.PoolType, tblPoolInfo.PoolInsideOutside, tblPoolInfo.PoolCapacity, tblPoolLocation.PoolLocationID, tblInspection.DateEntered
FROM tblPoolType INNER JOIN (tblPoolLocation INNER JOIN (tblPoolInfo INNER JOIN (tblInspector INNER JOIN tblInspection ON tblInspector.InspectorID = tblInspection.InspectorID) ON (tblPoolInfo.PoolID = tblInspection.PoolID) AND (tblPoolInfo.PoolID = tblInspection.PoolID)) ON tblPoolLocation.PoolLocationID = tblPoolInfo.PoolLocationID) ON tblPoolType.PoolTypeID = tblPoolInfo.PoolTypeID
ORDER BY tblInspection.InspectionDate DESC;

[June7]

Did it ever work properly?

If that query is only joining tblInspection to lookup tables, then I cannot see reason for error. Does this query have same number of records as tblInspection? If not, the issue is within this query.

If you want to provide db for analysis, follow instructions at bottom of my post.

[Bulzie]

"try using the Totals button in ribbon. Can try it on that last query or might have to create a 3rd query based on the 2nd one, add all fields and use the Totals button."

[InsuranceGuy]

Understanding you're not trying to rebuild, you're trying to fix:

Step1: Check qryInspection1 for duplicate entries on value InspectionID, with InspectionDate between the specified dates, with PoolLocationID applying your LIKE filter
Step2: Check tblInspection2 for duplicate entries on value InspectionID, regardless of dates, and PoolLocationID
Step3: Check tblInspection3 for duplicate entries on value InspectionID, regardless of dates, and PoolLocationID
[...]
Step7: Check tblInspection7 for duplicate entires on value InspectionID, regardless of dates, and PoolLocationID


Where duplication is observed, fix it. Re-test from Step1 if issue continues.

Cheers,

Jeff