if duplicate does not mean duplicate what is the word for duplicate?

[Cottonshirt]

I have a large athletics database and was recently given a set of data in a digital format (Excel spreadsheet) that permits Import into my ACCESS database. this is not something I've done before and I was very wary of making mistakes and also of Importing duplicates of records I already have.



to limit the potential for disaster I created a dummy table tbl_upload, and Imported a small sample of the data into there. a very quick visual check was all that was required to confirm that none of the new data was in fact a duplicate so I created an APPEND query to add them to the existing tables.

at that stage I must have done something wrong, because ACCESS APPENDed several hundred copies of each new record into my database. I'm not particularly interested in exploring what caused this, my main interest is what happened next.

I thought to myself that it would be a fairly simple task to identify and delete any duplicate records. since my database has, up to this point, been created entirely manually, this is also something I've not had to do before, so I went online and found the following instructions for how to do this:

Delete Duplicate Records


to keep this simple, let's assume I have three records

Bob Smith
Bob Smith
Bob Smith

clearly, I want one of these and need to delete the other two. after following the instructions from Messrs Microsoft and Co. I didn't have one copy of Bob Smith, or even three copies of Bob Smith, I had no copies of Bob Smith at all.

so my question is basically, how would I go about deleting just 2 copies of Bob Smith so that I am left with the one I want. If that isn't "Delete Duplicate Records" what on earth is it?

many thanks for your kind attention,

[Bob Fitz]

Does your table have an Auto-Number type Primary Key field

[isladogs]

ALL fields in your duplicates query must be identical for the records to be considered duplicates.
So for example, as Bob is suggesting, if you include the ID field, this will be different in each case, so the records aren't picked up

[Cottonshirt]

Does your table have an Auto-Number type Primary Key field

yes it does.

[isladogs]

More to the point is it included in your dupes query? If it is, remove it.

[Bob Fitz]

yes it does.

then you may be able to delete the duplicates but leave one record using criteria in the delete query. The attached db has a very simple example.
Be sure to make a backup of your db before attempting something similar that deletes records

[Cottonshirt]

thank you,

like you said, a "simple" example.

but I'm not quite ready to implement this in my database.

for the benefit of anyone who hasn't (or cannot) opened the database, the delete query looks like this:

DELETE Table1.ID, Table1.txt
FROM Table1
WHERE (((Table1.ID)>DMin("ID","qryDups","txt='" & [txt] & "'")));

I am close to understanding what this does. Dmin() selects for deletion any record, except the one with the lowest ID, that is both in the domain of the qryDupes and, that meets the criteria, "txt='" & [txt] & "'".

and it's that criteria that confuses me.

I appreciate that & is a text concatenation symbol, but I don't understand why [txt] is concatenated with two empty strings. why not just have txt=[txt] ?

and the answer seems to be that: txt=[txt] is, effectively, not a criteria, so that DMin() selected for deletion all records from Table1 except the one with the lowest ID. and whether or not they were in the domain, or duplicates, did not seem to matter.

amending it to read: "[txt]=[txt]" made no difference, all records in Table1 except the one with the lowest ID, irrespective of whether or not they were duplicates, were deleted.

amending it to read "[txt]='[txt]'" resulted in no records being selected for deletion, but I freely admit that I don't understand why any of these experiemts have had the results I described.

could you please explain what: "txt=' " & [txt] & " ' " actually does?

thank you.

[ArviLaanemets]

[txt] is a value of text field in form's recordsource, or it is a value of form control (In case you didn't rename controls differently from field names. It is the reason why such renaming is adviced - otherwise Access must decide, and you may not like it's decision.);
txt is in current case a name of text type field in table, you are querying data from.

An example:
You have a field in recordsource/control on form with value [txt] = 'This is a text';
You want to get all records from table YourTable, where value of field txt is same as value of [txt]. When you know the value of [txt] you can always write the query as

SELECT * from YourTable WHERE txt = 'This is a text'

(NB! As field txt is text field, the value in WHERE condition is enclosed between "'");
Now you want the query to read the WHERE condition directly from form (from source field or from control). When you write the query as

SELECT * from YourTable WHERE txt = [txt]

you get an error, because Access is trying to run a query

SELECT * from YourTable WHERE txt = This is a text

To avoid this error, the query must be

SELECT * from YourTable WHERE txt = "'" & [txt] & "'"

When WHERE condition is numeric, then no quotes are added.
When WHERE condition is US format datestring and you want Access to interpret it as date, you use "#" (non-US format datestrings you have to convert).

[Cottonshirt]

I'm terribly sorry, but I must be dim or something because I don't see how this answers the question.

all you've said is: "if it's wrong you get an error so do it the right way and that works," which is obviously true, but fails to explain anything.

would someone else like to have a go?

I would very much like to know why [txt] has to be concatenated with two empty strings, and why three sets of inverted commas are necessary.


thank you.