Keep Getting Duplicates Of Data
Sep 11, 2004
I've become frustrated trying to work out a problem. I have a database of equipment which is loaned out, then returned and may then be subsequently loaned out again.
I have a table called equipment inventory which has equipment listed as equipmentID.
I can obviously create a list of what is the total list of equipment.
I have two other tables called Loan Occasions and Loan details. One loan occasion may have multiple pieces of equipment going out at the same time. When the Equipment is returned, the Return date is recorded and this may then go back into stock. However, the same piece of equipment may then be loaned out again and of course is no longer in stock but on loan. Subsequently, the same may happen again.
I have tried multiple ways to produce a list of what is actually in stock and what is on loan but I keep getting the wrong answers. I keep ending up with a list with duplicate items of the same equipment which has been lent out, returned and then lent out and returned again. I have set my Return date default to null but this doesn't seem to help.
Does anyone have a suggestion of how to solve this dilemma.
Thanks
View Replies
ADVERTISEMENT
Feb 10, 2014
I need to delete duplicate rows (or create new table without them) but the duplicate is not the entire row of data.
Date Time LeagueMatchingId League HomeMatchingId HomeName AwayMatchingId AwayName
HomeScore AwayScore HomeRedCardCount AwayRedCardCount FTOU FTOverOdds FTUnderOdds
20121202 10/02/2014 20:34:02 17 FRANCE LIGUE 1 147 Lorient 154 Toulouse 0 0 0 0 1.5 0.88 -0.98
20121202 10/02/2014 20:34:02 17 FRANCE LIGUE 1 147 Lorient 154 Toulouse 0 0 0 0 1.5 0.88 -0.98
[Code] .....
In this example I am only interested in the first and last rows, since the ones in between have the same data in the last few columns. They are not true duplicates since the time stamp for each is different.
Currently thinking I need to compare each and every row to the prior row in VBA and delete if criteria match.
View 4 Replies
View Related
Jul 2, 2014
what I am trying to do with that table is get a sum of all assets, a sum of all Customers, and a sum of all Employees only counting each Comp ID once. Each company that we deal with carries multiple of our products. So as you can see I currently have it in the table where it shows the different data for each product over 2011, 2012, and 2013. Obviously for this I cannot just sum the entire column, as I would have the same company summed multiple times. The thing that is making this challenging for me is that I need it summed for each individual year (2011, 2012, 2013), with the end result being a total number of employees, assets, and customers.
I was thinking that to do this I would need a Sumif formula with several other formulas inside of it, but cannot figure out what exactly I would need to do. I know that if I took it into excel I would be able to do it, but we have over 7000 different customers, each having anywhere from 3-20 different products, and for each of the years 2011-2013... so the data is just too massive to try to convert and manipulate in excel.
The reason that I am hoping to get a count of their customers, assets, and employees is so that we could then take the info from individual customers and find out that particular customer's portion of the total assets, customers, or employees. I guess the mock table that I made doesn't keep the formatting when I add spaces, so imagine that each pair of l l is a column and match that with the columns above.
I also need another column that has the 2012 and 2013 results
l Comp ID l Year l Prod Fam l Prod Group l Prod Code l Assets l Customers l Employees l Assets for 2011 l Customers for 2011 l Employees for 2011 l
l 1101232 l 2011 l Family A l Group A l Prod A l 59000 l 33 l 28 l
l 1101232 l 2012 l Family A l Group A l Prod A l 55000 l 36 l 23 l
l 1101232 l 2013 l Family A l Group A l Prod A l 51000 l 39 l 25 l
l 1101232 l 2011 l Family A l Group C l Prod B l 59000 l 33 l 28 l
l 1101232 l 2012 l Family A l Group C l Prod B l 55000 l 36 l 23 l
l 1101232 l 2013 l Family A l Group C l Prod B l 51000 l 39 l 25 l
l 1101232 l 2011 l Family B l Group B l Prod C l 59000 l 33 l 28 l
[code]....
View 3 Replies
View Related
Feb 12, 2008
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.
View 6 Replies
View Related
Aug 8, 2005
hi, I have 2 Tables: tablea & tableb. The field names in both tables are: user_id, firstname, lastname, from these i have 2 querys (querya & queryb) both of which just look at the fields from the repective tables, & from this i have 1 form (entry form) that pics up the fields from querya. I would like to be able to enter details (records) into the entry form which puts all the data into tablea then automatically copies all the information into tableb, then automatically checks to make sure there are no duplicate records in tableb and finally deletes all the records out of tablea (probably after the form closes). This might sound a bit strange why i want to do this but I hope if its possible it could be used in a larger database.
I think i need to do querys but not sure what I need to do, Any advise would be very helpful.
View 3 Replies
View Related
Mar 12, 2015
I have two tables, tblCandidate and tblNewHire. I am trying to transfer candidates to tblNewHire based on the date they are hired. However, I would like the database to skip any records that are already in tblNewHire, just in case someone inadvertently puts in the wrong date when running the query.
I created an append query (SQL below) but this enters the data regardless of duplication. I need to be able to check both SSN and LastName against the new table and can't set either one to be unique values as it's possible there could be two candidates with the same last name.
Any way to do this by creating a recordset and looping through to check for dupes.
INSERT INTO tblNewHire ( SSN, FirstName, MiddleName, LastName, Phone, Email, EOD, HiringMechanism )
SELECT tblCandidate.SSN, tblCandidate.FirstName, tblCandidate.MiddleName, tblCandidate.LastName, tblCandidate.Phone, tblCandidate.Email, tblCandidateTracking.ActionDate, tblCandidateTracking.HireMechanism
FROM tblCandidate INNER JOIN tblCandidateTracking ON tblCandidate.SSN = tblCandidateTracking.SSN
WHERE (((tblCandidateTracking.ActionDate)=[forms]![frmNewHireMain]![txtEODDate]) AND ((tblCandidateTracking.LastAction)="EOD"));
View 2 Replies
View Related
Jun 11, 2013
Each month I get the previous months data. In that data, I have a list of names that have duplicates in them. Some of the names have last, first middle. Some have just last, first. I want to combine the like names and leave the unlike names not combined.
I want to combine in this situation
ex. Smith, Peter
Smith, Peter W
I do not want to combine in this situation
ex. Smith, Peter M
Smith, Peter W
My goal is to get the original name in a table with the combined name next to it.
Original NameNew Name
Smith, PeterSmith, Peter
Smith, Peter WSmith, Peter
View 2 Replies
View Related
Dec 7, 2007
Hi All,
I have an MS Access table that currently links into word as a mail merge. Great!
Sample data as follows:
Tutor Student
Mr A Donald Duck
Mr A Mickey Mouse
Mr A Minnie Mouse
Mrs B Joe Bloggs
I want to produce a mail document that allows me to address each tutor separately, along with the respective student names in a grid.
Now I've setup the document, but when I view each document it produces the list of all students for each tutor.
e.g.
Dear Mr A
Your students are:
Donald Duck
Mickey Mouse
Minnie Mouse
-------------------
Dear Mrs B
Your Students are:
Joe Bloggs
Is this at all possible?
Thanks:)
View 1 Replies
View Related
Dec 18, 2013
I have some data in an array that I need to normalize, remove duplicates, and import.
Original Table
Every record in the array has a person, all but a few have an address, most have a phone, and some have an email.
Person----- Address ----- Phone ----- Email
Tom ----- 10 A Ln ----- 789... ----- e@a
Sue ----- 20 B Ln ----- 256... ----- _____
Sam ----- 30 C Ln ----- _____ ----- _____
Dan ----- 40 D Ln ----- 478... ----- _____
Jan ----- 40 D Ln ----- 567... ----- e@d
Stu ----- 50 E Ln ----- _____ ----- _____
Syd ----- ______ ------ 224... ----- _____
New Data Structure
I want to group the data by HouseHold; which Address will serve to define for this import.
tblHouseHold
hhID
tblAddress
adrID, hhID, Address
tblPerson
prsID, hhID, Person
tblPhone
phnID, hhID, Phone
tblEmail
emlID, hhID, Email
I've been working on a procedure to step through the recordset and add the data one record at a time so I can get rid of the duplicates.
I've tried a few approaches, but this is where I'm at now.
Code:
Dim rs As DAO.Recordset
Dim rsHH As DAO.Recordset
Dim rsPhone As DAO.Recordset
Dim rsEmail As DAO.Recordset
Dim rsAddress As DAO.Recordset
Dim rsPerson As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
[Code] .....
View 2 Replies
View Related
Dec 8, 2005
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
View 14 Replies
View Related
May 19, 2013
I have two tables, Table Products & Table Sold and I'm trying to aggregate the products table to reduce the total number of products and I want this update to happen with all tables that share a 1 to many relationship with the product table. (table sold is one of those).I have products apple, pear, bananna. I am now aggregating them to all just be called fruit. Problem is when I make this change in the product table I get this error:
"The changes you requested to the table were no successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."how to go about aggregating data in a table that has 1 to many relationships with many other tables. A
View 2 Replies
View Related
Nov 1, 2014
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.)
View 2 Replies
View Related
Jul 5, 2007
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
View 7 Replies
View Related
Jul 14, 2005
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
View 2 Replies
View Related
Apr 11, 2007
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
View 6 Replies
View Related
Oct 9, 2007
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:
View 9 Replies
View Related
Aug 11, 2006
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 Related
Jan 22, 2007
I 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
View 3 Replies
View Related
Jun 8, 2007
: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
View 1 Replies
View Related
Sep 6, 2007
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
View 3 Replies
View Related
Oct 31, 2006
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
View 3 Replies
View Related
Jun 27, 2005
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
View 2 Replies
View Related
Aug 23, 2005
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.
View 3 Replies
View Related
Nov 28, 2005
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!
View 5 Replies
View Related
Jun 13, 2007
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 Related
Nov 26, 2007
I 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
View 5 Replies
View Related
