Duplicate Query

[capjlp]

Hi Folks



I need to create a duplicate query and I can't seem to get the wizard to do what i want.

I have one table that have monthly usage data in it and its imported data from other sources. I have had issues with data being duplicated. It has several fields but I only want to look at 4 to determine if they are duplicates.

I need to look at these four fields I Put a short description.

orgcode - Organization Code
accountid - The id of the account These 2 fields are my key's some orgs may have the same ccount id but one org will never have a duplicate.

I am looking for duplicates in these two fields
BillingDate - Month and Year of bill example Nov-14
Usage - The usage for the month - example 1000

I want to see a duplicate if I have a return that looks like this.

ORG1, 1234, Nov-14, 1000
ORG1, 1234, Nov-14, 1000

That would tell me its most likely a duplicate. I can have something like this

ORG1, 1234, Nov-14, 1000
ORG1, 1234, Nov-14, 800

That wouldn't be a duplicate. Does that make since? I just need to display it and be able to manually delete the duplicate record.

Thank you in advance for the help.

[vinod.excel]

you can write a query with these 4 fields only and apply Group by clause at the end use Having clause

Finally your query looks like this....

Select orgcode, accountid,BillingDate,Usage, , Count(*) AS Expr1
From Table1
Group By orgcode, accountid,BillingDate,Usage
HAVING (Count(*)>1);

Now it will list all duplicated rows one time with no.of times they are repeated. Now you can go to Table1, look for rows from above query results and delete records which you don't want.

Hi Folks

I need to create a duplicate query and I can't seem to get the wizard to do what i want.

I have one table that have monthly usage data in it and its imported data from other sources. I have had issues with data being duplicated. It has several fields but I only want to look at 4 to determine if they are duplicates.

I need to look at these four fields I Put a short description.

orgcode - Organization Code
accountid - The id of the account These 2 fields are my key's some orgs may have the same ccount id but one org will never have a duplicate.

I am looking for duplicates in these two fields
BillingDate - Month and Year of bill example Nov-14
Usage - The usage for the month - example 1000

I want to see a duplicate if I have a return that looks like this.

ORG1, 1234, Nov-14, 1000
ORG1, 1234, Nov-14, 1000

That would tell me its most likely a duplicate. I can have something like this

ORG1, 1234, Nov-14, 1000
ORG1, 1234, Nov-14, 800

That wouldn't be a duplicate. Does that make since? I just need to display it and be able to manually delete the duplicate record.

Thank you in advance for the help.

[capjlp]

Hi Thanks for the reply that will work. I do have an identifier field that I could use to find the record how can I use that field in this query?

Thank you

[capjlp]

Sorry I answered my own question. I will just make another query using this query to show me the Identifiers.

Thanks
Bill

[capjlp]

Ok I created a query to show the duplicate records and a second query where i used the table and this duplicate query to show the full record. However the record set is read only is there a way to make it so I can get this set editable?

Thanks