Append query is creating duplicates

[D4WNO]

Hi all,

As a real noob to creating databases I've come a bit unstuck after getting this far. I have a set of data that I update a couple of times daily.

Current Remedy Data is my base table
The below code is the SQL for a query which adds in a few extra colums onto the end from other small tables

Now when I run this query (named Updated Remedy Data Query) it does add those columns correctly, but it also creates duplicate entries

I've got about 168,000 genuine records in Current but a touch over 200,000 in the Updated. (It is the Updated one that I need to use for all my reports)



If someone could please help me point out what I've done wrong below I'll be forever in your debt!

Thanks
Dawn

SELECT [02 Current Remedy Data].[Assigned Individual], [02 Current Remedy Data].[Assigned Group], [02 Current Remedy Data].[Ticket ID], [02 Current Remedy Data].[Change-Id], [02 Current Remedy Data].[Arrival Time], [02 Current Remedy Data].[Ind Negotiated Date], [02 Current Remedy Data].Summary, [02 Current Remedy Data].Status, [02 Current Remedy Data].[Full Name], [02 Current Remedy Data].[Current Site], [02 Current Remedy Data].[External Reference], [02 Current Remedy Data].[Submitter Group], [02 Current Remedy Data].Keyword, [02 Current Remedy Data].Category, [02 Current Remedy Data].Source, [02 Current Remedy Data].Subcode, [02 Current Remedy Data].Priority, [02 Current Remedy Data].[Case Type], [02 Current Remedy Data].[Resolved Time], [02 Current Remedy Data].[Last Updated], [02 Current Remedy Data].CLID, [02 Current Remedy Data].Component, [02 Current Remedy Data].Country, [02 Current Remedy Data].[Is this a Parent?], [02 Current Remedy Data].[Is this a child?], [02 Current Remedy Data].[Missed SLA Reason], [02 Current Remedy Data].Reactivated, [02 Current Remedy Data].ReactivateCount, [02 Current Remedy Data].[Root Cause], [02 Current Remedy Data].[Group Count], [02 Current Remedy Data].[Assignee Count], [02 Current Remedy Data].[Met Initial Response Date?], [02 Current Remedy Data].[No of Client Chase], [02 Current Remedy Data].[EL1?], [02 Current Remedy Data].[EL2?], [02 Current Remedy Data].[EL3?], REF_MarketCountry.Country2, REF_BusinessType.BusinessType, REF_HighStatus.HighStatus, REF_RAGroup.Group2, Year([Arrival Time]) AS [Arrival Year], Month([Arrival Time]) AS [Arrival Month], Year([Resolved Time]) AS [Resolved Year], Month([Resolved Time]) AS [Resolved Month]
FROM ((([02 Current Remedy Data] LEFT JOIN REF_MarketCountry ON [02 Current Remedy Data].[Current Site] = REF_MarketCountry.[Current Site]) LEFT JOIN REF_BusinessType ON [02 Current Remedy Data].Subcode = REF_BusinessType.SubCode) LEFT JOIN REF_HighStatus ON [02 Current Remedy Data].Status = REF_HighStatus.Status) LEFT JOIN REF_RAGroup ON [02 Current Remedy Data].[Assigned Group] = REF_RAGroup.RA;

[JoeM]

First off, it looks like you are just dealing with a SELECT query, not an UPDATE or APPEND Query (those are action queries which physically add or update records to the underlying tables). It looks like you are just linking a bunch of tables, and doing some calculations on them (just to get the lingo right and make sure we are all on the same page).

When you link multiple tables together and end up with more records that you started with, it means one of two things:
1. You are dealing with a "one-to-many" relationship between some of your tables
2. You have an incomplete join between two tables (maybe it requires you to use multiple fields to link between your two tables)

Without seeing your data, we really can't pinpoint where the problem is. However, it should be fairly easy to find where the above is occurring. Just start with two tables, and see how many records it returns, i.e.

SELECT *
FROM [02 Current Remedy Data]
JOIN REF_MarketCounty
ON [02 Current Remedy Data].[Current Site]=REF_MarketCountry.[CurrentSite]

If this query looks good (meaning it doesn't return more records than you expect), then add then next join in there. Repeat this process until you find the point where these extra records are being added. That is the relationship you need to focus on and fix.

Note, if is truly returning duplicate records due to a "one-to-many" relationship (and maybe the fields that are different are not being returned in your query), and you want to filter out those duplicates, just add the word "DISTINCT" after "SELECT", i.e.

SELECT DISTINCT [02 Current Remedy Data].[Assinged Individual],...

Note, in order for this to work, two records must truly be a duplicates, meaning that every single field being returned for them matches another record.

[D4WNO]

You Sir are fantastic, thank you. The DISTINCT option solved it

[JoeM]

Great, glad to hear it.

You may want to investigate using the method I described, and make sure that it isn't just a "band-aid" approach to something that may be able to be solved by changing the query relationship (especially if you intend to edit the data in this query, as the data in a query using "DISTINCT" will not be editable).