Unique Record

[evlai]

I have a table with all the duplicate Campaign Code. I have actually tried to use "GROUP BY" bur still not able to come show the result i want.

Campaign Code Product Sku
SG1232454 GH123
SG1232454 GH134
SG1232454 GH167
AV1254321 GH123
AV5433232 GH123
AV5433232 GH143
AV5433232 GH195

I need a query to display the unique campaign code as below:
Campaign Code Product Sku Unique Campaign
SG1232454 GH123 1
SG1232454 GH134 0
SG1232454 GH167 0
AV1254321 GH123 1
AV5433232 GH123 1
AV5433232 GH143 0
AV5433232 GH195 0

Thanks

[matthewspatrick]

SELECT CampCode, SKU, IIf(Count(*) > 1, 1, 0) AS IsUnique
FROM YourTable
GROUP BY CampCode, SKU

[evlai]

Thanks. I have actually tried to use the code, but it is not working...it shows all zero in the result. any other ways?

[OBP]

Can you post a ziped copy of the database with that table in so we can work on it?

[evlai]

thanks for taking time to reply my question...here to attach my zip file.

[OBP]

evlai, I am sorry but I can't see how you can get the counts that you want,as there isn't anything to count to give you the values that are in your "desired tabe".
Am I missing something?
Do you just want the first occurence of the CampID to be counted?

[GaryB]

would setting up a query using totals to use group by in the campid column and count in the OppID column. This gave me
2132j 3
2132p 3
2162p 1

It grouped by the campid and totaled each occurance?

Is this what you are trying to do?

Gary