Query a query to find duplicate records 3

[as0125]

I'm new to Access/SQL, so please be patient. Thanks.

I am trying to write a query (I'll call this "Q2"), based on the results from another query (I'll call this "Q1").
Q1 simply gathers and displays all fields from several tables, based on Company Names, and displaying only the most recent entries from each table. With Q2, I'm trying to find any duplicates returned in Q1 -- for example, if two separate entries were made for one company on the same date.

My ultimate goal with Q2 is to determine which company is getting duplicated from Q1, and which fields in those duplicates do not match (which is causing the duplication).

Any suggestions?

 

[Crowley16]

about 4 posts down...

thread701-867340

 

[PHV]

To find the duplicates:
SELECT Q1.Company, Q1.date, Count(*)
FROM Q1
GROUP BY Q1.Company, Q1.date
HAVING Count(*)>1;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[as0125]

Well, I've been using this as a test on a different table:

SELECT DISTINCT p1.*
FROM Strengths AS p1 INNER JOIN
[SELECT UpdateDate, CoName
FROM Strengths
GROUP BY UpdateDate, CoName
having count (*) > 1 ]. AS p2 ON p2.UpdateDate=p1.UpdateDate;

This query ("Q2") correctly returns duplicate records from a table. But I don't know how to modify this query to pull results from another query ("Q1"). Q1 results are from multiple tables and queries. I really need Q2 to be based on UpdateDate per CoName. Part of my problem is that results from Q1 include different UpdateDates from different tables.
eg: table1.UpdateDate, table2.UpdateDate, table3.UpdateDate

Each table is allowed to have different UpdateDates -- but I am sorting for duplicate dates per table.

Hope I am explaining this clearly.

 

[as0125]

Perhaps it'll be helpful for me to include the original query ("Q1"):

SELECT Main.CoName, Main.*, FCQuery.*, BBGQuery.*, PRQuery.*, PCFQuery.*, PSQuery.*, PWQuery.*, PLQuery.*, PMQuery.*
FROM (((Main LEFT JOIN FCQuery ON Main.CoName = FCQuery.CoName) LEFT JOIN BBGQuery ON Main.Tick = BBGQuery.Tick) LEFT JOIN PRQuery ON Main.CoName = PRQuery.CoName)
ORDER BY Main.CoName;

 

[Crowley16]

just put the name of "Q1" in the FROM statement...

you can use a query in exactly the same was as a table in this context...

 

[as0125]

I'm completely confused. I simply don't know how to write up the query. This is what I tried, but doesn't work:


SELECT DISTINCT q1.*
FROM Q1 AS q1 INNER JOIN
[SELECT table1.UpdateDate, table2.UpdateDate, table3.Update, table1.CoName
FROM Q1
GROUP BY table1.UpdateDate, table2.UpdateDate, table3.Update, table1.CoName
having count (*) > 1 ]. AS p2 ON q2.UpdateDate=q1.UpdateDate;

I'm having difficulty figuring out the criteria of the INNER JOIN - "q2.UpdateDate=q1.UpdateDate". Since I want the comparison done on all dates, how do i write the last line?

 

[lespaul]

If you create a query that normalizes your data:

SELECT table1.CoName, table1.UpdateDate, 'TABLE1' As Location FROM table1
UNION
SELECT table2.CoName, Table2.UpdateDate, 'TABLE2' FROM Table2
UNION
SELECT table3.CoName, Table3.UpdateDate, 'TABLE3' FROM table3

now you can join into this query on the single updateDate.

Depending on how developed this database is, you may want to review 'The Fundamentals of Relational Database Design' (you may want to review it any way so you'll know in the future how to develop a normalized database).



Leslie

 

[as0125]

Even though the "UpdateDate" field is on every table, they contain different values and really refer to different types of dates. I want the query to be able to compare each UpdateDate field between the main query and the inner join query.

Am I making sense?

 

[lespaul]

So, you put in an identifier that let's you know what kind of date it is. Replace the TABLE1 with ServiceDate and TABLE2 with ReferralDate or whatever meaningful name you need them to have.


Leslie

 

[as0125]

It works! Thanks for everyone's help on this!!

The problem I was getting stuck on was figuring out how to reference the original query's field names. Now I know that I can treat query recordsets EXACTLY the same way as a table. I don't know why my brain couldn't process that before. =)