Help finding duplicate records across 8 quarters of data

  • Thread starter Thread starter JessM
  • Start date Start date
J

JessM

Hi All,

I have one main table with 8 quarters of data in it. I need to figure out
how many records are duplicates in this table and the time period in which
they are duplicates.

So for instance, Profile ID 1 is in time periods Q108 and Q109a. The time
period information is all in the same column in the table and that is where I
am running into a bit of trouble.

Profile ID is the uinque identifier and Quarter is the time period identifier.

Thanks!
 
Here is an example of the master Table

Policy ID Qrt ID
5676 Q108
5481 Q208
5555 Q108
5555 Q308
5481 Q109

I need to identify all the duplicate Policy ID's and all of the quarters
that they belong in. So in the above case the query would return something
like this:

Policy ID Q108 Q208 Q308 Q408 Q109
5555 Yes No Yes No No
5481 N0 Yes No No Yes

Thanks!
 
Use a crosstab query. The wizard says you need three fields to build a
crosstab but it can be done.
TRANSFORM IIf([Policy ID] Is Null,"No","Yes") AS Expr1
SELECT JessM_1.[Policy ID]
FROM JessM_1
GROUP BY JessM_1.[Policy ID]
PIVOT JessM_1.[Qrt ID];
 
Back
Top