Assign Duplicate records unique identifier

Thanks in advance for your help on this.

The goal of my queries is to match records from two different tables based on part #, PO #, and QTY and make a new table from the matched records.

Currently the query makes a table of those records where the Part #, PO #, and Qty match between the two tables. The problem is that more than one record in either table might contain the same Part #, PO #, and QTY -- the result is too many records or duplicate records. See primative example below:

example 1
Table A (matches with) Table B
Part # PO QTY Part # PO QTY
1.) 455 325 2 455 325 2
2.) 455 325 2
3.) 455 325 2

. . . . the result of the match is as follows:

Match table
Part # PO QTY
1.) 455 325 2
2.) 455 325 2
3.) 455 325 2

Only one of the above three should match, but I have to identify each row seperately.

example 2:
Table A (matches with) Table B
Part # PO QTY Part # PO QTY
1.) 455 325 2 455 325 2
2.) 455 325 2 455 325 2

. . . . . . .the results are as follows:
Match table
Part # PO QTY
1.) 455 325 2
2.) 455 325 2
3.) 455 325 2
4.) 455 325 2

there should only be two rows in the output, but access matched each record twice.

So, basically, my question is how do I make a query pull out those records in each seperate table who have the same Part #, PO # and QTY, then assign them some unique identifier automatically. My purpose is to make sure records only match one-for-one.

Thanks
Greg


Post your answer or comment

comments powered by Disqus
Hello there,
I am just wondering how can I use VBA to remove duplicate records, as identified by the combination of values of certain fields, would be removed from a table.

Thanks!

Hello All,

I have been doing a fair bit of searching around looking for examples where others are trying to accomplish a similar task to what I am working on, but I can not seem to find very much so today I joined the forum . Now for the task I am trying to perform.

There is currently a database which contains location information on a variety of different samples, each which is identified by a unique sample number (the primary key). However, when these samples are processed, they are split it half and each is assigned a second identifier for each half (in this case, an A or a B). Therefore, if originally there was sample 1, it will be split into 1A and 1B. My task is essentially to create a second database with further information for each sample half (1A or 1B), while maintaining a link to the main sample database to reflect any changes to the sample location information. So far I have been using a make table query that pulls from the linked table which links back to the main database and combines with another table that simply has one column called "Piece" which has an 'A' in the first row and a 'B' in the second row.

I am quite new to using access, but I have found this task fairly frustrating so far because it feels like I am trying to force access to work in ways that it was not designed. Essentially I would like access to create a duplicate entry for each sample in the original database, and force in an 'A' or a 'B', and combine these fields to create a unique identifier. However, I am running into a lot of issue when trying to append data from the original database etc. and I was hoping that someone would be able to provide a sanity check on the actual task I am doing, and if so, potentially some suggestions on how to accomplish it.

If this has already been covered before and I have missed it, please point me in the right direction!

Thanks,

Danny

I have access 2000. I wish to be able to duplicate records in a table based on a foreign key automatically using a command button on a form.

When the user selects a category I ask them for a new name and then add the new category and copy all its campaigns. I have a routine to create the new Campaign. I need one to copy all the Campaigns assigning the newly created CatId and unique CamId to the new records. I can pass the new CatId to the routine.

I have two tables
Category
CatId (Unique Key)
CatName

Campaign
CatId (Foreign Key)
CamdId (unique Key)
Camname

Hi everyone

I remember being a user of these forums when I was but a wee A Level student, and many years later it seems I'm back again!

I'm working with a large data import for my company, and amongst other things need to locate and display some duplicate records.

I have a table called 'data' with 44000 records in it. There are a lot of fields, but the key fields I'm currently working with are as follows...


	Code:
	*Account | Surname | DOB | Postcode
============================
123451 | Smith | 17/07/1977 | AA11 1AA
123452 | Smith | 17/07/1977 | AA11 1AA
123453 | Jones | 11/01/1911 | BB11 1BB
123454 | Jones | 11/01/1911 | BB11 1BB
123455 | Jones | 11/01/1911 | BB11 1BB
123456 | Brown | 16/06/1966 | CC11 1CC
123457 | Brown | 16/06/1966 | CC11 1CC

The Account field is a unique identifier. I've currently got a query setup with the following SQL code to locate customers who have more than one account based upon Surname, DOB and Postcode.


	Code:
	 
SELECT data.Surname, data.DOB, data.Postcode, Count(*) AS Multiples
FROM data
GROUP BY data.Surname, data.DOB, data.Postcode
HAVING (((Count(*))>1));

This is ok, as it gives me a Multiples field illustrating how many accounts each customer has. But the next step is to be able to identify specifically which accounts each customer holds. For example something like:


	Code:
	 
SURNAME | Account1 | Account2 | Account3 | ...(upto 7)
===========================================
Smith | 123451 | 123452 |  | ...
Jones | 123453 | 123454 | 123455 | ...
Brown | 123456 | 123457 |  | ...

At this point I'm completely stumped! Can anyone help...?

Many thanks,


Ryan (your bewildered friend in need!)

Hi guys,

I have 2 databases which are exactly the same in its structure but differs in its content/records.

I would like to append data from 3 tables from database A to the same 3 tables in database B on a weekly basis without the duplicates.

Each of these 3 tables contain the same unique identifier (i.e. Employee ID)

Table1 (Personal Particulars): Employee ID, Employee Address, Contact Number.

Table2 (Employee Equipments): Employee ID, Equipment.

Table3 (Employee Qualifications): Employee ID, Qualifications.

How do I go about appending the records without duplicating records everytime i append the records when I back the data up?

Thanks!!

Be advised that I'm a complete novice with Access, and have used it only to view Excel files with more than 64,000 records. Any help would be appreciated.

I have an Excel file of 10,000 records entered by salesmen of possible prospects, with salesmen, prospect name, address fields. Some salespeople have entered the same prospect and address information; the only thing different in these records are the salesman name.

I know how to import the file into Access. But from this point, how do I construct a query and generate a report that shows me ONLY THE DUPLICATE RECORDS? We would like to use this report to identify the double entries and the salespeople so they can clean up the database. I know Access (I have Access 2003) can create this query, I don't have a clue on how to do it.

One hint: A post on another forum suggested " Make a group-by query (grouped on all fields except salesperson). Add a count field. Select if count >1. This should do the trick."

I'm sure this is the answer, but I don't know how to "make a group-by query", how to "add a field", etc. Like I said, I know how to import the data. That's it.

This is extremely frustrating to be this close to the answer and not know how to finish it. Any assistance would be appreciated. Thank you.

I have a query with with four fields:

Property
Unit
Square Footage
Start Date

I've created a 5th field called End date. What I'd like to do is have the query look for duplicate unit records by property. If a duplicate record exists then assign an end date to the superceded unit's square footage. The end date would equal the start date of the new unit's square footage.

Example:
House, 101, 1,000, 1/1/09, 5/1/09
House, 101, 1,200, 5/1/09, n/a

Thanks in advance,

Matt

Attempting to create a report that displays the individuals from 3 different categories of staff, which support different programs. Columns are as follows:

Program/Staff A/Staff B/Staff C

Categories have many individuals and multiple individuals can support a program. The way the query that the report is based on works is it creates a record of every possible combination of names related to a program. My problem is when I have multiple individuals in more than one category supporting a single program (i.e. 2 Staff A individuals and 3 Staff B individuals), I am no longer able to hide all the duplicates even with the appropriate properties assigned. Some of the duplicated records will not be hidden while others will be. So in some cases I am seeing the same individual multiple times because it is being matched up to multiple individuals from another category.

Does anyone have any insight into this matter?

Hi.
I recently asked for help with a problem regarding counting only non-duplicated records on a report, and a member called Rich gave me a solution, but I'm still lost.

My database is basically monitoring damage to vehicles. Each vehicle has a unique Chassis Number, and some of these have multiple distinct damages, for which we must claim.
I've been told by head office that the database must have a new record for each instance of damage, so effectively a chassis number is repeated (in the main table and report queries) as many times as there is instances of separate damage.

On the reports (about 10 different ones), I need to include a count of vehicles, but this doesn't give me an accurate result. Say I have 10 vehicles, but 2 of them have 2 areas of damage each, the Chassis count value would be 12, but I need this to give me 10.
In other words, how do I only count each different chassis number only once?

The report uses a group header for Chassis Number, and Rich told me that I need to add a hidden text box with the control source set to =1, and then sum that number in the report footer.

This sounds ideal, but how do I actually write the sum? If I name the "=1" control "Chassis count", I can't use:
=Sum([Chassis count])
because you can't use the name of a calculated control in a sum.

I can't use:
=Sum([=1])
and I can't use:
=Sum([1])
or even:
=Count([1])
because these return the wrong values.

I must be just being thick, but how do I sum these hidden 1's?

Please help! I have to get this sorted by Monday morning!
Much appreciated,

Gram

I download a text file from the net that has 7 days news and every time the file is updated whenever there are some new news, but it contains only 7 days news. I need to download the file every day whenever new data is added and put it the MS Access database table where "Symbol", "News" and "PostingDate" are the three fields. I am facing problem in deleting duplicate records of memo field........ Let me be little more clear, say I have downloaded and imported the data on 1200 hours 31/3/2011 and again I want download and import new data at 2000 hours on the same day (31/3/2011) but I only want to add the new data> I tried to do this by SQL statement SELECT DISTINCT......but it does not allow to be executed on memo field. Also there might be same news on same Symbol in different date so I need to get the unique data basing on all three fields. I am attaching the DB here with. I have very shallow knowledge on VBA, please help me considering my limited knowledge on VBA .................thanks

I am trying to resolve an issue where I need to duplicate lines of sort.
I am importing an excel sheet that has the following: (there are more fields, but with the basic code-I could move forward)

Title code Req Auth

Staff 112 3 3

I would like the code to create the following

Staff 112 1 1
Staff 112A 1 1
Staff 112B 1 1

This code is a unique identifier that will be used to track employee positions. Any help will be greatly appreciated.

Hello,

I am trying to write a procedure that will loop through the records in a table and compare them on one field to check for duplicates. If they are duplicates, I need the value from the Quantity fields for the two adjacent records (the table is sorted) to be added and assigned to the first record. Then, I would like to have the code delete the second (duplicate) record. For some reason, when I invoke rst.MoveFirst, it doesn't go to the first record in the table (I checked this by doing an assignment statement to try to modify the Quantity field in the first record). Here's what I've got so far:

Private Sub CombineDups()
Dim prevRecVal As String
Dim thisRecVal As String
Dim Quantity As String


Dim dbs As Database
Dim rst As DAO.Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Shopping List", dbOpenTable)
With rst
.MoveFirst
.Edit
End With

thisRecVal = rst![Ingredient Num]

Do Until rst.EOF
rst.MoveNext
prevRecVal = thisRecVal
thisRecVal = rst![Ingredient Num]
Quantity = rst!Quantity
If thisRecVal = prevRecVal Then
With rst
.MovePrevious
!Quantity = rst!Quantity + Quantity
.MoveNext
.Delete
.Update
End With
End If
Loop

With rst
.Update
.Close
End With
End Sub

I am stumped. Can anyone please help me out?
Thanks,
CM

Hi! - Help!!!

I have a relational database, I would like to include a "Copy Record" button on the main form to enable the user to create a duplicate record, well a record based on that one anyway, obviously with a new unique record number.

The duplicate record through the wizard will not work because there is a key field.

Also there are related tables including a 1-M rel, that data needs copying from. (all displayed via subforms on the main form)

Is there an easy way to go about this? And can someone explain it to me please!

Thanks

I would appreciate if anyone could suggest a method to remove duplicate records, I am still a bit of a nove and still would appreciate a simple as possible method.

But essentially, the company can have similar:
product sectors
provincial locations
telephone numbers

I would also be interested in associating a unique numeric code and not the auto nuymber if possible?

If you like I could send you the excel file to give you a better understanding.


AlmasPelliSasDiMassaroAlessandroC
AlmasPelliSasDiMassaroAlessandroEC
AlmasPelliSasDiMassaroAlessandroEC
AlmasPelliSasDiMassaroAlessandroEC

ApisSRL
ApisSrl
ApisSrl
ApisSrl
ApisSrl


AIPASpaAgenziaItalianaPerPubblicheAmministrazioni
AIPASpaAgenziaItalianaPerPubblicheAmministrazioni
AIPASpaAgenziaItalianaPerPubblicheAmministrazioni

AFiorDiPelle
AFiorDiPelle
AFiorDiPelleDiReginatoSonia
AFiorDiPelleDiReginatoSonia

3PRappresentanzaCommercioEsteroSRL
3PRappresentanzaCommercioEsteroSRL
3PRappresentanzeCommercioEsteroSrl

ConfartDiTosanaPAECSAS
ConfartDiTosanaPAECSAS
ConfartDiTosanaPAECSAS
ConfartDiTosanaPAECSAS
ConfartDiTosanaPierachilleECSAS
ConfartDiTosanaPierachilleECSAS

Hi,

I wonder if someone can help with what must be an easy solution to this problem.

I have a table with 350,000 records, but without a unique identifier field. I just want a simple incrementing number field to become the primary key but dont know how to add this in. I cannot add an autonumber as there are two many records - it falls over.

Ive tried exporting it all to excel, but as well as being cumbersome, something went wrong and so I'm back to square one.

I think there must be a way to add an incrementing number either via a query or in VBA but I dont know how. Please can somebody provide a solution.

Thanks,
Adam.

I'm working on a database to store information about archaelogical artifacts. I have a number of different types of artifacts, such as beads, utensils, and ceramics. Each type has fields that pertain only to that type of artifact (e.g., circumference applies to beads, not utensils). But at the end of the day they are all still artifacts and share some fields in common, such as location of dig, estimated period of origin, etc. So I am envisioning tables like the following for each artifact type:

Table: Beads
ID (PK)
Completeness
Material
Manufacturing Technique
Bead Structure
Bead Form
Bead Shape
etc.

Table: Utensils
ID (PK)
Utensil Form
Plating
Handle Decoration
Object Length in millimeters
etc.

Table: Ceramics
ID
Manufacturing Technique
Ware
Vessel Category
Form
Mended Form
etc.

I saw somewhere how these tables could be connected to another table whose purpose is to give each record a unique identifier vis-a-vis all the records in other tables -- obviously each record has its own within-table identifier (table.ID), but I want each record to be numbered uniquely in the db as a whole. So I saw something like

Table: Generate Artifact ID
ContextSampleID (PK; to be used in other tables)
ArtifactID (foreign key, related to the ID fields in each of the previous 3 tables).

I'd post a picture or link of the relationship diagram I'm modeling after, but as a new user I cannot. My problem is that I do not understand how the "Generate Artifact ID" table works (or if it works?). There's probably something very basic I'm missing; this project is outside the scope of my usual, more modest Access projects. Can anyone enlighten me?

Many thanks in advance.

I am trying to remove duplicate records from a table. I have run a query to identify the duplicates. However, I am not sure how to delete the duplicates from my table and leave only one of the records. Contract number is duplicated, but ID # is different.

I have a table with the following fields - Appeal #(Auto Number), Student ID, Class. I want to append records from an identical table and then delete the duplicate records (or not append the duplicate records in the first place).

The trouble is the only single unique key is the Auto Number field.
The same student appears in the table many times and the same class appears many times, but their combination is unique.

This makes it impossible to remove the duplicate queries by copying the structure of the table and then appending values.

Is there a way to do this or have I just designed the table badly?

Need help and know no to very little code.

Have a table DONORS that has assigned Soundex for finding possible duplicate donors. The fields I need to compare are Soundex,Lname.Fname,DOB,DonorId so that when I create a report from the query I can group possible duplicates in pairs to be researched to determine if they are actual duplicates are not.
I tried using the find duplicate records wizard to no avail.

I think I should be looking at using an Alias for the Donors table when the designing query. Need something like If Soundex and Lname and or DOB are same then pair these two where DonorId is not =.

Will be willing to e-mail table to anyone that might be able to help.

Thanks

RC

I know there a many posts about eliminating duplicate records because I did search, but I did not see anything that really answers my question.

I have a query in a local Access database that is using a Linked table from an SQL database managed by our corporate IT department. I have read access to only certain tables / fields in the SQL database. My query is based on 3 tables from the database and I have them linked together in my query.

When managers move from one site to another, they are given a different TeamID number. However, until they are replaced, the manager will have more than one TeamID assigned to him in the SQL database. Evidentally, the way our IT has the SQL database structured, when I query open items it duplicates the record for that manager because he has 2 TeamID numbers. So the only field that comes up differnt in the duplicate records is the manager's TeamID number.

I would like for my Select Query to ignore the duplicate record. I know I can use an Append Query to copy the data temporarily to a local table and set the proper fields as Primary Keys to do this. However, it would be nice if I did not have to go to all that trouble.

Is there a way to eliminate duplicate in my Select Query?

As always, I appreciate the help.

Jim

I have a query that I would ideally like to run as a SELECT DISTINCT query, becuase it's doing something strange: 2 out of the 23 unique records return multiple times, even though all the data within those duplicate records is identical in every way.

I thought I could solve this using SELECT DISTINCT, but a few fields in the query are drawn from a table where those fields are set as the MEMO datatype -- this was necessary because those are comment fields that seem to average about 400 characters. When I try to use SELECT DISTINCT in the query, I of course get the error message saying that DISTINCT can't be used in a query that includes MEMO datatype fields.

I then went into the report being fed by this beast, to se if I could hide the redundant records by setting the appropriate text boxes' HideDuplicates property to YES, but now I get all these weird blank rows and whatnot.

Does anyone know of a workaround for getting rid of these duplicate records? Or why those things appear in the first place, when that never happens in the querys which this monster query is built on?

Thanks,
Andreas

Hi,

I have imported an excel table into access succcefully. The records contain the following fields: NIIN, FSC, SD, NET QTY, NRTS QTY, Credits, Net, and Noun. Each record has a very unique NIIN. I have written a query to find all duplicate NIIN records and sum up fields NET QTY, NRTS QTY, Credits, Net respectively then append the summed up records to a different table. The query works fine at first.

After analyzing the results, I noticed that there are some NIIN records in which all the data is a duplicate except for the Noun field. Based on the NIIN, the Noun fields should be identical, but due to typos when entered into excel (data received from external source), they are not and thus those records arenot considered duplicates. In reality they are duplicate records.

Is there a way, by using a query, to locate duplicate records by the NIIN field (but not include the Noun field), sum up fields NET QTY, NRTS QTY, Credits, Net and append the summed up record to a new table that does includes the Noun field?

Desperately needing help. I’m creating a database to hold info on magazines.

Tables – Magazine, Keywords, Authors

Relationships – Magazine – keywords (1-to-many), Magazine – Author (1-to-many)

I have created a filter form which allow users to search by Author (cbobox), Subject, & Keyword (both multi-select listbox). I used the wizard to create the search button whereby I use the default docmd.openform , , , stlinkcriteria. I expanded on the stlinkcriteria in VB to include author, subject, & keyword criterions into one search criteria string. When the search button is clicked, a continuous form opens to display all the magazines that meets the search criteria. The form shows the magazine details without the author and keyword fields.

I’ve got it set up so that if there is nothing selected, the continuous form displays all the records of all the magazines BUT the problem is that it displays duplicate records (one record for each associated keywork and author). If I remove the keyword and author fields from the underlying query, thus removing duplicates, the search criteria doesn’t work because these fields are then not present. If I leave these fields in, the search criteria works but displays duplicate records.

How can I get the form to display only one instance of the magazine record (unique magazineID)?

I suppose it would be similar to something like a library catalogue system where, based on the entered search criteria, the system would then display a list of each individual book although one book might be associated with multiple criterions.

Or, is it possible to write code in VB to runSQL based on the search criteria, somehow retrieve the MagazineID (arrayMagazineID as array)that meets the criterions, and then use the openform command to open the form and display magazine details of all the magazines where the forms magazineID = arrayMagazineID? If this is a possible solution, could anybody give me pointers on the code I might use here?

Any advise would be appreciated.

Thx in advance,
Jan

I have a form with Text box that I am using for a case number. I have to use a text box formatted as text instead of formatted as a number because the format of the numbering sequence is 00-000-AAA-00;;. In the table design, I chose "no duplicates" because each case number has to be unique. The last 2 digits is actually a recommendation number for the case. When someone tries to enter a duplicate number, the message that Access provides is "You can not go to the selected record". That does not tell the user that they are trying to enter a duplicate record. How can I make Access say something like "You have entered a duplicate case number. Please try a different number".

Thanks,
Jim


Not finding an answer? Try a Google search.