Tracking duplicates

[mac_see]

I am a beginner in Access. I just know that there are tables where you can create fields and enter data. Other than this I am good for nothing.

Lets assume I have 120 fields and 55000 records in a table which is a LOT of data. Is there any way in Access I can track the number of duplicate entries? I don't want to eliminate them. I just need to extract the duplicate entries may be in a different table.

Maxi

 

[Norie]

Maxi

What exactly do you mean by 'track' duplicates?

If you want to find duplicates in your data you can use the Find Duplicates Query Wizard.

Just goto the Query tab, select New and then follow the steps.

BTW why do you have 120 fields - it seems rather a lot.

 

[mac_see]

I am sorry, I did not put that across in a correct manner.

Eg. here is the data

d1 d2 d3
a b c
d a b
a b c
b a f

d1 d2 d3 are the three fields that accept one letter from a-z

I have four records in the above example

The duplicate query would show 2 Dups. a b c

What I want to know is, is there a query that can tell me which letter is duplicated more than 1 times?

Here is the result I want.

a duplicated 4 times
b duplicated 4 times
c duplicated 2 times
d duplicated 1 time
e duplicated 0 times
f duplicated 1 time

Maxi

 

[Norie]

I'm sorry I still don't quite understand your questions.

Have you tried creating a Find Duplicates query with the wizard?

That should be able to tell you how many times a record is duplicated.

You can easily count the no of times a particular entry appears in a single field using a totals query.

To actually find out how many times a particular letter is duplicated across fields is a different matter.

To do that Excel is probably a better tool.

If the data was in Excel you could use a COUNTIF worksheet function to find out how many times each letter occurred.

 

[mac_see]

Hi! Norie,

Yes, I have tried creating a Find Duplicates query with the wizard and it works fine and yes, I am trying to find out how many times a particular letter is duplicated across fields.

I know Excel can very well do it using the countif function. I was just wondering if this is possible in Access. If yes, how?

Maxi

 

[Norie]

Maxi

It is possible in Access but is, in my opinion, impractical.

It would either creating a whole load of update/make table queries or writing VBA code.

 

[mac_see]

I can understand it is impractical but I wan to see the difference in speed the way Excel and Access calculate this. I think there would be a marginal difference in the speed but I want to do it once.

Can you help me with a VBA code to do this?

Maxi

 

[Norie]

Can you help me with a VBA code to do this?

You would need to give more information.

 

[mac_see]

Eg. here is the data

d1 d2 d3
a b c
d a b
a b c
b a f

d1 d2 d3 are the three fields that accept one letter from a-z

I have four records in the above example

The duplicate query would show 2 Dups. a b c

What I want to know is, is there a query that can tell me which letter is duplicated more than 1 times?

Here is the result I want.

a duplicated 4 times
b duplicated 4 times
c duplicated 2 times
d duplicated 1 time
e duplicated 0 times
f duplicated 1 time

Maxi

The above thing is what I am expecting. I am trying to find out how many times a particular letter is duplicated across fields for which I need a VBA code.

Maxi