VBA Distinct

[Guy Winfield]

Hello,

I'm a novice at VBA and hardly know the basics. However, i'm using Microsoft Access 2007 at the company i work at and i think i've finally come to the point where i can't avoid using SQL/VBA much longer.

I have duplicates in my database for 2 reasons:
-First of all the data that i am managing has duplicates, these tables aren't controlled by me though, i simply import and use them.
-I had to use a "create table" query in order for some of my work colleagues to manually fill in some fields newly created fields. Create table--> copy structure--add new fields with new data manually -->crush previous table to make new table with same data but with added fields manually.

This new table is then put in a query and that is where the main source of duplicates come from. Seeing as i've had to re-use tables from a previous query in this query because i have a few calculated fields that needed to be added and is impossible to be done prior to the creation of the new table. (hope i'm making sense). I guess this is my structure issue, but it is unavoidable unfortunately. Because i can't use my new calculated fields without the manual data.

So this means that i have a final query with more than 2000 duplicates instead of the previous 169 (impressive i know).

So the ideal situation would be to dispose of the duplicates in this final phase and i can't help but thinking that it would be so much easier if i knew SQL/VBA.

Does anyone have a simple easy to understand way of making my duplicates disappear or at least have unique data ?

Many thanks !

[Seth Schrock]

The SELECT DISTINCT query will display only unique values, but it doesn't remove them from the table. In order to delete the duplicate records, I think the best way would be to create a ranking query (see Ranking Queries in MS Access) and delete records where the rank is greater than 1. You would obviously have to make the ranking query have the criteria to find duplicate records, thus including every field that would make it a duplicate record.

[NeoPa]

In a situation such as this Guy, a properly organised structure progresses beyond a 'Nice to Have' and becomes 'Critical'.

Most of what you explain lacks the required level of detail for good advice. We can advise around bumps in the road, but are generally loathe to give advice that we understand will simply lead to your issues becoming bigger and more troublesome for you in the future.

Sure, you can get around individual instances of duplications, but without a thorough understanding of when duplicates appear and why that is a very risky approach. What it means in most cases (Again - too little information to be sure this pertains in yours.) is that the results become unreliable and, when treated as gospel as they often are, can be dangerously misleading.

My strong advice to you is to analyse why there are duplicates and put in place procedures to ensure they can no longer be created. Primary keys are useful in that context. Whether a randomly assigned one would help in your situation is hard to predict without an understanding of what that is.

The fact that you refer to them simply as duplicates, rather than duplicate XXXX type items, leads me to believe your appreciation of the situation and the related dangers is currently inadequate. Go and ensure you appreciate the whole system more clearly before trying to provide a computerised solution for the system. The latter approach will surely lead you to pain unless/until you retrace your steps back to the beginning and start again. You wouldn't be the first to take that route, but it's avoidable so don't be another even.

Whatever you choose I wish you good luck in your endeavours.