:confused: Duplicates
I have all my master data in my table which has a lot of duplicates (700 entries). I ran the find duplicate query offered by access, but exported the data to excel to delete all the duplicates.
My question is how do I import that updated excel list back into the duplicates query so that it updates my master data in my table?
Thanks
Hi this is my first post... so hi all :)
ok what i have is a table with contact details 900k plus
there are about 90k of which are duplicates.
this is the basic feilds that are important in this case.
Id, data_source, data_recived, data_code,
what i want is to have a table with unique records (no dups in data_code)
this table will look like this...
Id, data_code, Num_dups, dup1_source, dup1_date, daysbtw_Dup1_dup2, dup2_source, dup2_date, daysbtw_Dup2_dup3 ,dup3_source, dup3_date, daysbtw_Dup3_dup4 ,dup4_source, dup4_date,
I know there is no more than 4 dups of each record.
what i want from this is a table that will give me a record of how many dups for each record then all the dates that they were added and the date between each record entry.
if anyone can help it would be great .
thanks in advance.
Hey, all! Thanks for helping, here is my situation.
I have a table with about 70,000 records that have duplicate Address field values. The rest of the field values for those records are different. When I do a find duplicate querry I get the result that 17,000 records have the same address. However, when I do the append qurrey as instructed here: http://support.microsoft.com/?kbid=209183 I get a total of only 600 records in the new table. I have tried deleting all of the indexes for both the new and old table, with no luck.
I'm using Access 2000 on XP Pro.
If anyone could help with this I would greatly appreciate it!
Thanks
Will
I have an Access table with an indexed key that is currently set to duplicates OK. There are not supposed to be any duplicates in that field. But it is possible than a small number have crept through.
Two questions:
1. What would happen to those duplicate records if I changed the setting from duplicates OK to no duplicates?
2. Is there any way to ferret out those duplicate records first and change them manually? The table currently has 48000 records so it would be a pain to go through them page by page. (I know how to export them to Excel, where the dups can easily be found; I was just wondering if something could do it within Access.)
Hi. I have two tables, of which i have run a duplicate query, removed the duplicates by doing an "is null" criteria, and keeping that new table.
I know though there are still more duplicates but the names may not be exactly the same, say barclays and barclays plc. so i run another query on both tables to show me the first five letters of table 1 and table 2. I can run another "is null" criteria query to find the non duplicates there. these are the unique values.
But even if there are duplicates up to the first five letters the next letter may change, for instance barcleys tractors and barclays bank. So these arent duplicates.
So can anyone come up with a way of taking the duplicate ones according to the first five letters, and maybe duplicate ones up to 10 letters, and working out which ones are the unique ones. my brain is frying.
thanks
alex
Hi
I have imported a 1 column table from an external source for the sole purpose of deleting duplicates. There are over 400,000 rows. What do i need to do to identify the duplicates?
Thanks
I work at a hospital and deal with several thousand account numbers a day. I am working on an Access database that will handle some of my workload. Here is what I am facing. Lets say on some of the tables that I import there are duplicate account numbers with different totals in the end...
acct info info info amount
123456 xxxxx xxxxx xxxx 5.50
123456 xxxxx xxxxx xxxx 17.50
123456 xxxxx xxxxx xxxx 900.00
753951 xxxxx xxxxx xxxx xxxxxxxx
and so on...
is there a way that i can only show one account number but the total of the amount for the account? please help...this is desperately needed.
JG
Hey All, i'm looking for some help from one of the heavy hitters in the Access Forum. (Keep in mind i'm self-taught in access..never picked up a book on the stuff) So far i have done ok. I'v made many databases and in my opinion i have a firm grasp on the fundamentals.
I have come accross a database that I did not create which i am suppose to work on. I can do the work just find but i'm a little confused on the structure. when i looked at the relationships i noticed that many of the field names in several tables were bold (showing they were set as a primary key) So i looked at the design view of a few of these tables and noted that the primary key was set on 3 different fields. i also noticed that each field allowed duplication. Furthermore i noticed that on at least one of the tables, in the relationship window there was a relationship using the ID field which was a 1 to many. however there was a relationship on the other side of that table still using the ID field that was a many to one. How can a field have a one to many relationship and a many to one?? it can either be duplicated or it can't...right???
Anyways i also tried to duplicate this in a test database and found that when you make a table and then right click at the side of the field you wish to make as Primary key, that works fine, but doing it on another field simply switches the primary key to that field and takes it off the first one.
(as a side bar..is the only way to search this forum to go manually through all the threads to see if you can find what you need? aka...no search tools?? or did i just miss them?):confused:
I have a table that has country_code, Mfr_num, Item_num, catalog_code, mfr_obsolete_code. the key is on country_code, mfr_num, and item_num. I need to find duplicates of mfr_num and catalog_code. Can anyone point me in the right direction on this?
View 14 Replies View RelatedI have the data from a membership DB which has quiet a few duplicated Names and addresses (though they may have a 'type' field which is different)
I have identified these using the find duplicates wizard. Is it possible to use this query to delete the duplicated entries and leave one record.
Thanks
RussG
I do not understand why this is happening but I am getting some, not all, records duplicated in the Select query on two tables. (see attached)
Can anyone explain why AND how I can make it return without duplicates.
Cheers
Hi,
I have got a table that has following fields display on a form:
ClassNo Class Date ClassType ExecutionDate Order Quantity
The Table CLASSES has got many records which are duplicate. Mostly ClassNo is duplicated. I could have stopped duplication by restricting table not to accept duplicate values, but my requirement is such that i dont want to stop the table from accepting duplicate values. When i display the same table data on the form, all the duplicate entries are displayed.
All i need is to find a way to stop the FORM showing duplicate ClassNo Field. The Duplication restriction i wish to set only on ClassNo. If there are more than one ClassNo reptition than it should show that entry only once. For example the form looks like this:
ClassNo Class Date ClassType ExecutionDate Order Quantity
1 12-12-2006 A 12-01-2007 Ok 10
1
1
1
1
1
2 20-10-2006 A 20-11-2006 Ok 100
2
2
2
2
I wish to see the form this way:
ClassNo Class Date ClassType ExecutionDate Order Quantity
1 12-12-2006 A 12-01-2007 Ok 10
2 20-10-2006 A 20-11-2006 Ok 100
Regards,
Darno
Hi,
I have a form and within that form there is two combo boxes and one text box. When one choses a value from one combo box, and then chooses a value for the other combo box and then enters the value into the textbox I don't want the user to be able to enter the same arrangement. That is I want to prevent duplicates on that combination. Also if they do this I want a Message Box to appear saying that that this combination already exists
I was thinking of using a multiple field index to prevent the duplicates, but I don't know if this is a wise thing to do. Can someone give me some help to see how I can prevent duplicates or offer another solution to prevent duplicates on the combination of the values.
Thanks
Greg
I have a table in a healthcare patient database that stores Admitting Number, Scan Number, Last Name, and some other fields detailing the scan. Each patient can have multiple scans and I'm trying to design a query that lists how many scans each patient has had by finding all the duplicate admitting numbers. I'm using this SQL statement:
SELECT MultScans.Admitting_Number, Count(MultScans.Admitting_Number) AS Num_Of_Scans
FROM MultScans
GROUP BY MultScans.Admitting_Number
HAVING (((Count(MultScans.Admitting_Number))>1));
which works fine but I can't figure out how to also print out the last name, scan number and the other fields in this query. If I add the field to the select part it says I can't because the expression is not part of the aggregate function and if I add the expression to the 'group by' the query runs but displays all the duplicates. Thanks for any help.
hi,
i have a form with a sub-form on it. each has is bound to a table. the main form's table has a one to many relationship with the sub-form table, and the sub-form table has the PK of the main table as a FK.
every time i try to add a second record to the sub form, it gives me an error saying i cant have duplicate foreign keys.
i cant figure out what to do. i am using access 2003 and microsoft sql server version 8. i need to enable duplicate foreign keys so that each record of the main table can have more than one record in the sub table.
Please help!
Hi, How do you normally prevent duplicates being inputted into you forms.My Idea is thisThe input text box doubles up as a dropdownlist of current values held in the database for that field.So when you access the organisation form and go to the first text box as you enter the 'I' for IBM All the orgs with 'I' appear. When you see that the organisation your inputting already exists you will stop and move on to the next form.I dont know how to do this, so currently I have set the field OrgName as Indexed: Yes (No duplicates) but this isnt really any good as the user enters all the other details and clicks submit before the error message comes up.I have attached my file for you to understand this better.
View 2 Replies View RelatedI don't exactly know all of the correct access lingo but i am having trouble with duplicates in my system. My system is set up so that when i enter my client's ID number the system will not let me create a new form if the ID has already been entered. However, the way my system is set up I have to enter all of my client's info and then click on the next button before it tells me it is a duplicate. Is there a way that when I tab off to the next line an error message will pop up if the ID is a duplicate?
Thanks!
alwaysneedhelp
Can anyone tell me why access puts the primary key field in the index list twice for the same table? For example i have a primary key called CustID, when i go into design view and click on the index button on the toolbar, i see it listed twice, once as primary key and then again as its own field?!
I dont see the reason for this but often times with many of my databases i see this happen to many of my tables. :confused:
Hello everyone,
This is code with which I choose records from one table and I put them to another table (OdabranaOprema),
can I add code with which I can stop choosing the same record twice?
I would like in that case have possibility to change the quantity of the record which is selected twice,
thanks
Private Sub cmdOdaberi_Click()
On Error GoTo Handler
Dim strRedak As String
Dim strOdabranaReferenca As String
Dim rsPonudjenaOprema As Recordset
Dim rsOdabranaOprema As Recordset
Dim db As Database
Dim intRedak As Integer
Dim strSQL As String
Dim strSQLOdabir As String
txtNapajanje.SetFocus
Set db = CurrentDb
strSQL = " SELECT Napajanje.Referenca As Referenca, Napajanje.Opis as Opis, Napajanje.Visina " & _
" FROM Napajanje " & _
" WHERE (((Napajanje.PickFlag)= True))"
strSQLOdabir = "Select OdabranaOprema.Referenca, OdabranaOprema.Opis, OdabranaOprema.Kolicina, OdabranaOPrema.Visina " & _
"From OdabranaOprema"
Set rsPonudjenaOprema = db.OpenRecordset(strSQL, dbOpenSnapshot, dbReadOnly)
Set rsOdabranaOprema = db.OpenRecordset(strSQLOdabir)
Do Until rsPonudjenaOprema.EOF
txtNapajanje.SetFocus
rsOdabranaOprema.AddNew
rsOdabranaOprema.Fields("Referenca").Value = rsPonudjenaOprema.Fields("Referenca").Value
rsOdabranaOprema.Fields("Opis").Value = rsPonudjenaOprema.Fields("Opis").Value
rsOdabranaOprema.Fields("Visina").Value = rsPonudjenaOprema.Fields("Visina").Value
rsOdabranaOprema.Fields("Kolicina").Value = txtNapajanje.Value
rsPonudjenaOprema.MoveNext
rsOdabranaOprema.Update
Loop
Set rsPonudjenaOprema = Nothing
Set rsOdabranaOprema = Nothing
Set db = Nothing
Handler:
Izlaz:
End Sub
I have created a table for product codes and prices. After importing data from Excel I have a LOT of duplicated records. So I set up a query to search for 'distinct' records and then set up a delete query to delete anything that is not distinct. The query seems to be setup correctly (I have followed the instructions from Access Help) - but then when I try and run the query I get the message 'Could not delete from specified tables' - Is this a permissions issue? And if it is, where do I set up the permissions for the database? Thank you any ideas would be greatly appreciated!
View 1 Replies View RelatedI have a PO DB that I'm trying to normalize. For the most part it is normalized but there are 2 fields that are not normalized because they repeat data. So I'm trying to restructure a bit and having some problems when it comes to data entry via forms.
There is also a PO table above this one but is not used in the subform for the details.
My old set up for my PODetailsTable
PODetailsID pk
PO fk
Item
Quantity
Price
Allocation
I want my new set up to be
PODetailsTable
PODetailsID pk
PO
ItemID fk
Quantity
Price
AllocationID fk
ItemTable
ItemID pk
Item
AllocationTable
AllocationID pk
Allocation
On my form I can't enter anything that is already on the ItemTable. I looked at some microsoft sample DB's and they have you enter the item from a different form then you can select it in from the combo box which would really slow down data entry peticularly early on.
Is there a way I can type the Item in and if its new, it will be put it on the ItemTable associate an ItemID to both tables?? But if its already in the Itemtable just apply the already existing PK on ItemTable to the FK ItemID on the PODetails Table for that record??
Here is a A2K format of it which is how it currently works. In the relationships window you can see an example of how I want it but can't get to work.
17501
Thanks JOe K.
Why would the Idexed change from "Yes (No Duplicates)" to "No" while data is being entered in a form?
View 1 Replies View RelatedI ran the Find Duplicates Query Wizard and it found multiple duplicates. Now how do I go about deleting these duplicates?
View 1 Replies View RelatedI get this from a query
date___ name
1_______1
2_______1
3_______2
4_______3
5_______4
6_______4
field date is unique while field name has
what can i do to get the following such that there are no duplicates for
the name field
_________________________________________________
| name | date1 | date2 | date3 | date4 | date5 | date6 |
-------------------------------------------------------|
|1 ____|1 ____ |2 ____| ____ | ______| ______| _____|
|------------------------------------------------------|
|2 ____| _____ | _____|3 ___ | ______| ______| _____|
|------------------------------------------------------|
|3 ____| _____ | _____| ____ |4 ______| _____| _____|
|------------------------------------------------------|
|4 ____| _____ | _____| ____ | _______|5 ____|6 ____|
|------------------------------------------------------|
Best Regards
manolo
Hello All,
I have a table “subdivisions”
With a daughter table “lot numbers”
I need to limit lot numbers to no duplicates in the LotNumber field, within a subdivision, without limiting it to no duplicates in the table.
Any clues?
LotNumber is a text field.
LotNumberID is autonumber, primary key
Preferably this would be accomplished in a query.
Hello,
I imported an excel worksheet (~20,000 entries) into Access, and I am
trying to extract some information. First I wanted to find duplicates,
and rank by duplicate appearance. I have gotten that part.
In my db, I have a column 'Qty' that specifies the number of times the
event occurred. At the moment, I only have the number of times the
event appeared in the db. I would like to factor in the number of times
now.
An example of my db would look like:
ID(access) Event Qty
1 A 10
2 B 100
3 C 30
4 A 60
5 A 50
So in my initial query, I get
Name:Query1
CountofID Event
3 A
1 B
1 C
However, to take this further, I want to be able to total the Qty field for each event. So, in this case, A actually needs to be 120, B needs to be 100, C needs to be
30.
My idea is to make a second query based off the first query, where one
item would be the Event# from Query1 (because that cut out all the
duplicates), and the second would be the all the number of occurences
of that event added up.
I did some searching before posting and this thread seems somewhat similar to what I need, but he is looking for a moderately different solution.
http://www.access-programmers.co.uk/forums/showthread.php?t=103526
Any help is appreciated, Cheers.