Help finding duplicate records across 8 quarters of data

[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!

 

[KARL DEWEY]

I did not follow your explaination. Post sample data.

 

[JessM]

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!

 

[KARL DEWEY]

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];