Duplicates In Table/Query
Aug 1, 2007
I am using a combo box to assign gun numbers on an employee form (frmemployees). I also have a query listing only the gun numbers that have been assigned. When I select a gun from my combo box, I what to evaluate if it is already on the gun assignment query. That way I cannot assign the same gun to two different people. I am using the before_update event to do this. The code that I am using is as follows:
Private Sub intgunNumber_BeforeUpdate(Cancel As Integer)
If DLookup([intgunNumber], "qrygunassignmentcurrent", Me.intgunNumber) = Me.intgunNumber Then
MsgBox "Item # is already listed.", vbOK, "DUPLICATE FOUND"
Cancel = True
End If
"me" represents my frmemployees form. The problem is that it's causing an event even for the numbers that aren't listed on the query. I am trying to get the evaluation correct but am having difficulties. All the fields are numeric fields. :( :mad:
View Replies
ADVERTISEMENT
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
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
Dec 10, 2007
Why would the Idexed change from "Yes (No Duplicates)" to "No" while data is being entered in a form?
View 1 Replies
View Related
Jan 11, 2005
How do I make a table from another that will only display the duplicate records in a field .... I have add'l fields in addition to the partno but not relevant.
i.e. partno new table will show:
0001 0001
0002 0001
0001 0003
0003 0003
0003
Thanks for your help....
View 2 Replies
View Related
May 19, 2005
Hi Everyone, I need help in selection of primary key and designing of a tables. I am having a table called Current trailers with Trailer number as a primary key. I have similar table to current trailers called history table in which the trailers that left the yard are stored. As a trailer can enter and exit the yard couple of times in a week, its not letting me to have duplicates in the history table.So can anyone help in selection of primary key for the history table. Or can anyone tell me how I can have duplicates in a table.
Thanks in Advance,
Naveen
View 2 Replies
View Related
Mar 30, 2007
Hi
I want to amend my query so that all duplicate records don't appear. For example when the query is run the information generated includes:
Day the audit took place
Room the audit took place in
Faculty the activity belongs to
Staff lecturer(s) who takes the class
Activity that was going on when audit took place
Start time of Activity
Finish time of Activity
Counted number of people in the room
Therefore, if there is an activity going on for a faculty but there is more than 1 staff name against it, I just want 1 record to appear against that faculty. Whereas what is happening for example is that there is a activity which takes place from 3pm to 4pm and the activity belongs to the Faculty of Computing and Engineering, and as the class was taken by 3 different lecturers, 3 separate records appear. I am concerned about this because the information is then used in a summary report for each Faculty for the year, so in this case the summary report will calculate 3 activities when it was just 1 activity.
It would be great if its something I could just amend in the query, and not in the source table.
Much appreciated
View 1 Replies
View Related
Aug 19, 2004
Hi,
I'm running MS Access (2002 SP-2)
I have a table that has the following columns in it:
Sent NBR (text) / Sent Date (date/time) / PIC (text)
It's a customer listing of our customers and which long distance carrier they have selected. Unfortunately my data also shows OLD customers that no longer own that phone number. Because of this, various phone numbers are listed numerous times, but only the Sent NBR with the most current Sent Date is the one I need to see. The others are irrelevant and need to be deleted. I'd manually delete the duplicated Rows but I have over 115,000 duplicates!!
Do you guy know of a way to eliminated the duplicates for each Sent NBR and only show me the Sent NBR's with the Most current date?
Any help would be greatly appreciated!
Scott
View 9 Replies
View Related
Jan 30, 2008
Hello,
In access 2003 i have an external Oracle table named EXT-COM_EXTRA_DATA_FIELDS
in this table I have the following fields: COM_DATA_KEY, COM_DATA_FIELD_NO, COM_DATA_VALUE
From this I have created a query named: LOOKUP-BARCODES-10 this looks at the above table by filtering the
the records on the field: COM_DATA_FIELD_NO where this =10.
Also I have created a second query named: LOOKUP-BARCODES-20 this looks at the above table by filtering the records on the
field: COM_DATA_FIELD_NO where this =20
Below is sample data in the table:
COM_DATA_KEY; COM_DATA_FIELD_NO; COM_DATA_VALUE
009A/s 10 03010303165001
009A/s 20 04350309115002
010B/P 10 05150909116001
010B/P 20 06150909116002
011C/X 10 07281809217003
011C/X 20 N/A
The problem is i would like to combine the numbers in the COM_DATA_VALUE field for the same product (ie the same COM_DATA_KEY
record) on the same rows without any duplicate records as shown below:
COM_DATA_KEY; COM_DATA_VALUE; COM_DATA_VALUE (2)
009A/s 03010303165001 04350309115002
010B/P 05150909116001 06150909116002
011C/X 07281809217003 N/A
As you can see from above some records only have data in one COM_DATA_VALUE (like 011C/x) where COM_DATA_FIELD_NO = 10 but no
data for the same product where the COM_DATA_VALUE = 20.
Im thinking maybe i can create a new join query that looks at the above two query’s and combines the values in the COM_DATA_VALUE for
the same product onto the same record row but im not sure how to go about this without creating duplicates & its been some time since ive done joined queries. Any help would be great?
ive attached a cut down version of the database with the table imported from oracle rather than being linked to oracle if this helps.
Thanks
Kevin
View 2 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
Aug 15, 2013
Access 2007 - I am using the oft used, frequently posted and shard code from srfreemen - modified as below. now I know I sort of broke commandment # 8 - Thou shalt not copy and paste other people's code without at least attempting to understand what it does. but I honestly did try to understand what it does.
I keep getting runtime error 3078 - cannot find the input table or query 'tblEnrolment_Committee_Master'. It is the name of a table in my database but I am missing the very basic element of how to get my form (which draws all of it's fields from that table) to look it up or identify it.
Likely because of a silly basic error. Normally - I wouldn't attempt such things but my work needs me to ensure no duplicates exist in this case AND it's already a primary key - but I don't want to wait until the whole form is filled out for it to identify the duplicate!
How to get the tblEnrolment_Committee_Master to be included in the search and erase this error ...
Private Sub Entitlement_File_Number_BeforeUpdate(Cancel As Integer)
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset
Set rsc = Me.RecordsetClone
[Code] .....
View 3 Replies
View Related
Jun 11, 2013
I have a table called Stock Levels which contains 3 fields. (ID, ProductID, StockLevel) ID is the Pkey, ProductID contains duplicates and StockLevel which contains different stock levels
and I am trying to remove the duplicates and retain the the data so I am left with the correct stock number
what I have done is the following, but I am still getting duplicate values in productid and stocklevels
SELECT DISTINCTROW id, productid, stocklevel into mynewtable from stocklevels
I have attached a screenshot of the table
View 3 Replies
View Related
May 13, 2015
I am trying to loop through a table and count the duplicate records only once. Please see the attached file.
1) In the test table, I want to loop through all pt_accts.
2) In the countable column, if an acct repeats, I essentially just want to put "1" in one of the records. For the non-repeating accts, putting in a 1 is sufficient. Basically, I just don't want to count the duplicate ones multiple times.
In the example, acct "aaa" repeats, but there is just one "1" in the countable column. "eee" repeats as well, but there is just one "1" and the rest is 0.
Is this possible via VBA?
View 14 Replies
View Related
Feb 6, 2014
Attached is my many to many relationship setup. I would like to prevent the possibility of entering the same person more than once for the same training event. I am assuming that I do this by setting an index setting for a field the junction table?
View 3 Replies
View Related
Aug 15, 2013
Is it possible to make a calculated field in a table not allow duplicates?
There is obviously no option to select No Duplicates in the field settings?
View 2 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 29, 2014
I have a report with 2 access tables (1 Master table and another a daily feed table)
The Master table keeps a log of all incoming records. (once append it to this table, should not show in future reporting)
The Daily feed information within the last 48 hours. (uploaded from an excel report into access temporary table)
When the daily feed table gets completed, I append the records and updated them into the Master to avoid duplication.
When I upload the daily feed table and I match it against the Master table to find duplicates, how can I delete the duplicates from the Daily Feed table?
This is my code to find duplicates:
SELECT CMPreport.ID, CMPreport.MbrName, tblMaster.ID
FROM CMPreport LEFT JOIN tblMaster ON CMPreport.ID = tblMaster.ID
WHERE (((tblMaster.ID) Is Not Null));
View 9 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
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
Apr 4, 2006
Please help. I am having difficulty understanding why a subform is showing duplicates despite using SELECT DISTINCT for its query. I have looked all over the forum but am still quite confused.
I think my database is normalised but this may be the problem and can attach it if necessary.
My query is as follows
SELECT DISTINCT StudentAndCourse.ClassID, StudentAndCourse.LevelID, StudentAndCourse.StudentID
FROM [Level] INNER JOIN (Class INNER JOIN StudentAndCourse ON Class.ClassID=StudentAndCourse.ClassID) ON Level.LevelID=StudentAndCourse.LevelID;
Any help would be most appreciated as I cannot understand what is going wrong.
View 1 Replies
View Related
May 1, 2007
Hello,
I am just wondering whether there is any way I could run a simple query that would pull all the data I need and inform me of the duplicates if there is any.
Thanks!
View 2 Replies
View Related
Mar 25, 2008
Hi everyone.
I have a query linking 2 tables. The idea of the query was to show our longest serving customers. The one table contains CustNo, Address details, etc and the second table contains AccNo, CurrBal, AccType, CustNo, etc etc. (note the bold fields are the primary keys). However this is bringing back results on account level rather than customer level, i.e CustNo 3 might Have AccNo xxxxxx, AccNo xxxxx1, AccNo xxxxxx2. How do I get it to show the 'oldest' account for each customer. So it will only show each customer once and that customers oldest account. Heres the SQL for the query. Hope someone can help. Thanks
SELECT DBSINV97.DateOpen, DBSINV97.AccNo, DBSINV97.CustNo, DBSINV97.PostalName, DBSINV97.CurBal, DBSINV97.AccType, DBSINV97.DateClose, DBSCUS01A.Title, DBSCUS01A.Initial, DBSCUS01A.Surname, DBSCUS01A.Street, DBSCUS01A.District, DBSCUS01A.Town, DBSCUS01A.County, DBSCUS01A.Postcode
FROM DBSINV97 INNER JOIN DBSCUS01A ON DBSINV97.CustNo = DBSCUS01A.CustNo
WHERE (((DBSINV97.DateOpen)<"2002-01-01") AND ((DBSINV97.DateClose)="1800-01-01"));
View 1 Replies
View Related
Mar 26, 2008
I've been researching and reading about how to eliminate duplicates in my Query, but all the suggestions don't seem to work. Here is my problem:
I want to create a Query with information from 2 Tables. Ultimately, I want my Query to be a malining list that I will be sending requests to. But, I want only the names, addesses, etc. of the people who fit certain criteria (info. stored in several different fields of the two tables). So, if a person matches two of my specified criteria, they appear twice in my query. Or if they fit all the criteria, they might appear 5 or 6 times. I want each name and address to show up once, even though the person may fit into several criteria. Does this makes sense?
I've tried typing "DISTINCT" after "SELECT" in SQL, but that didn't do anything. I've also tried changing the "Unique Values" to YES, but that didn't work either.
Sorry if it's unclear, but I am very new to Access! :(
View 7 Replies
View Related
Nov 24, 2004
Hello
I've created a query that finds duplicate records in a table. I've also made a form based on that query that shows some fields of the duplicate recoeds found. In that form there's also a textbox that counts the records found (using the count([field]) function). Everything works perfectly when there are duplicate records. However, when there are no duplicates , I'd like the textbox value to be 0 (it shows nothing now obviously because the query returns no records), as long as some other actions to be performed. How can I check (in the vb code or somehow else) if the query returns no records as a result ? Thanks in advance
View 3 Replies
View Related
Jan 9, 2006
Friends,
I have a table with 3 fields:
NAME
AMOUNT1
AMOUNT2
Field AMOUNT1 and AMOUNT 2 can have the same data and I need to run a query which will return records with similar data in both fields. Both fields are text (5 characters) and the match has to be done on the first four characters.
Please note that I have tried with a Find Duplicates query but does not seem to return all the records with showing the same data probably to the fact the the last character is different.
Any help? Thank you.
View 3 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