Query Resulting In Duplicate Entries

[kestefon]

The following query is producing duplicate entries. Any idea what the culprit might be?

Here's some context: Basically, I have a table of data (called "trp"), and a couple of other tables with percentages in them. I have two queries...one if trp.DataType = "TRP" and another if trp.DataType = "OtherMetric". (The headers are all the same, but the calculation in the "Variable Value" column will be different for each of these queries.)

SELECT prc.Market AS Geography, trp.Product, trp.Indication, trp.[Variable Name], CStr(Format([trp.Period],"MM/DD/YYYY")) AS Period, prc.Index*trp.[Variable Value] AS [Variable Value], trp.Outlet, trp.Daypart, trp.[Program Name], trp.[LEN], trp.Creative, trp.Campaign, trp.[Campaign Name], trp.[Media Type], trp.Vendor, trp.Channel
FROM Nov2013_TVNational AS trp, tblTRPpercent AS prc
WHERE trp.Indication=prc.Indication AND trp.[Media Type]=prc.Type AND trp.Geography="National" AND trp.Month=prc.Month AND trp.DataType = "TRP"
UNION ALL SELECT prct.DMA AS Geography, trp.Product, trp.Indication, trp.[Variable Name], CStr(Format([trp.Period],"MM/DD/YYYY")) AS Period, prct.[percentUniverse]*trp.[Variable Value] AS [Variable Value], trp.Outlet, trp.Daypart, trp.[Program Name], trp.[LEN], trp.Creative, trp.Campaign, trp.[Campaign Name], trp.[Media Type], trp.Vendor, trp.Channel
FROM Nov2013_TVNational AS trp, tblDMApercent AS prct
WHERE (((trp.DataType)="OtherMetric"));

[BluffMeAllIn]

Hi kestefon,

Just had a quick review of your query, noticed that in the first vs the second you have a lot of extra where conditions (should these not be the same for both queries, just curious). Also essentially you don't have trp.datatype as part of your select statement which is in essence the only thing that should uniquely identfy the difference between the results of your first query vs the results of you second query and because you are using the UNION ALL vs UNION that this will result in duplicates existing in the final query results.

I guess the question is should it be possible that all the data in the fields of your queries for select could be identical with the exception of the trp.datatype? If that is the case but you simply don't want to see the duplicates in your results then use UNION instead of the UNION ALL and this will essentially remove any results from being returned in the second query that match as duplicates in the results of the first query.

Clear as Mud?

Thanks,
Dave

[kestefon]

Thanks, that makes sense. I'm running into a problem though...the UNION query seems to be hitting Access' memory limitations, which is bizarre because the UNION ALL query (which produces twice as many results) runs just fine.

[BluffMeAllIn]

Thanks, that makes sense. I'm running into a problem though...the UNION query seems to be hitting Access' memory limitations, which is bizarre because the UNION ALL query (which produces twice as many results) runs just fine.

This does actually make sense because the UNION has the additional overhead of determining duplicates, while the UNION ALL simply returns duplicates and all.

*Edit: The work on the processing side of the fence is not always represented with increase in results, its usually quite opposite when querying into a large amount of data that the less results returned can turned to use the most memory and processing which is why we always want to develop our queries as efficiently as possible or sometimes break them down as I have suggested below to remove your duplicates.

You could stick with the UNION ALL and then run an additional query over the results to weed out the duplicates. To do this you would have to essentially input the results of your query into a temporary table, then run a SELECT DISTINCT ........ FROM tmpTable. Essentially the ...... would be all of the fields in the tmpTable and this would identify only the records that are not exact duplicates.

I would have to have a close look to determine if it could be done all within the same query however I think it would possibly still run into the memory issue with running the query and essentially trying to week out the duplicates all at the same time in memory to produce the results without the duplicates.

Let me know if you have any further questions or issues regarding? Also w3schools.com is a great resource for specific sql functions such as DISTINCT and UNION etc.

Thanks,
Dave

[kestefon]

Turns out that the apparent duplicates were just part of the original data, not a SQL error. The trp.DataType values (TRP or OtherMetric) are mutually exclusive, so I couldn't think of any reason why the query would produce duplicate records...looks like it worked as planned.

[BluffMeAllIn]

Turns out that the apparent duplicates were just part of the original data, not a SQL error. The trp.DataType values (TRP or OtherMetric) are mutually exclusive, so I couldn't think of any reason why the query would produce duplicate records...looks like it worked as planned.

This thought did cross my mind as well regarding the duplicates possibly being correct results, but as had indicated if you did wish to remove them the Distinct suggestion should work as it goes based on the fields being selected. Hope I was some help at least even though it turns out to have been ok afterall.

Take it easy.
Dave