Duplicates In A Table-Help Needed
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 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
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 6 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
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
Jun 6, 2006
I need urgent help, I am required at my job to come up with a command that will check to see if a table exists, and if so, delete the table. The whole process goes like this:
There's a form, and a listbox. 2 buttons...1 is import a file and 2nd is generate report. In the listbox theres 3 excel files. You select one excel file then click on Import, followed by clicking on Generate Report button.
The whole technical process is this:
1. A csv file is imported into a temp table
2. A temp table is created and named "_ImportedSKUS".
3. An append query is exectued to add the data from the "_ImportedSKUS" temp table to the final table, "Imported SKUs".
4. Then the temp table, "_ImportedSKUs" is closed and deleted.
I have a delete command at the end of the sub, which goes like this:
DoCmd.DeleteObject acTable, "_ImportedSKUS".
But I'm also need to generate an If statement before the loop that will check to see if the "_ImportedSKUs" table exists, if so, delete it (using same code listed above) and end if.
Can anyone help me out here???
View 4 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
Apr 24, 2006
I’m having trouble defining Relationships I’m thinking I need a “Junction” Table and I have tried looking at the Orders.mdb but it hasn’t helped (I’m sure I’m just missing something) I just don’t see how it works. If at all possible please don’t just give the info try to help me understand so I can get the answer myself.
Here is what I have:
Far table:
FarNumID (PK) > autonumber
FarNumber > Text “224-10C”
FarTitle> Text
FarParagraph table:
FarParaID (PK) > autonumber
FarNumID > Number
FarParaTitle > Text
FarParaText > Text
AC table:
ACNumID (PK) > autonumber
ACNumber > Text
ACTitle> Text
ACParagraph table:
ACParaID (PK) > autonumber
ACNumID > Number
ACParaTitle > Text
ACParaText > Text
1. Each FarNumber can have only 1 FarTitle 1:1
Each FarNumber can have many FarParaTitles 1:Many
Each FarNumber can have many FarParaText 1:Many
2. Each FarTitle can have many FarParaTitle 1:Many
Each FarTitle can have many FarParaText 1:Many
3. Each FarParaTitle and have only 1 FarparaText 1:1
Thanks so much.
View 4 Replies
View Related
Oct 30, 2006
I am looking to set up tables in such a way that will allow for the concept:
If Variable 1=A and Variable 2=B then Output of 3=C
For Example:
If the House is "Blue"
And the Man drinks "Beer"
Then his pet is a "Dog"
If the House is "Green"
And the Man drinks "Vodka"
Then his pet is a "Bird"
NOTE:
There is no equational relationship between the first two variables to get the 3rd output. It is simply a lookup table. In excel you would have the color house down the left, the drink across the top and in the cells would be the type of pet. But I am struggling with how this can be made to function within Access since I will want the user to obviously be able to pick the first two variables and have the "answer" spit out.
View 2 Replies
View Related
Mar 14, 2007
Hi
I will give you an overview of what I have.
I have a database that records sites in Blackburn and each site has a unique ID (RTP_ID). Each site has 10 objectives with 25 Measures in which the sites are scored against.
Objective 1 has 2 measures
Objective 2 has 8 measures
Objective 3 has 1 measure
Objective 4 has 1 measure
Objective 5 has 1 measure
Objective 6 has 3 measures
Objective 7 has 2 measures
Objective 8 has 1 measure
Objective 9 has 2 measures
Objective 10 has 3 measures
Each site has the same objectives and the same measures, but the scores for each are individual.
Table: SiteDetails
RTP_ID, SiteName, Postcode
Table: Objectives
OBJ_ID, ObjName, ObjScore, RTP_ID
Table: Measures
OBJ_ID, MSR_ID, MsrName, MsrWeight, MsrScore
I don't know how to create the relationship between them so that each site can have 10 objectives and 25 measures (with scores) individually. At the moment, I have them connecting as SiteDetails.RTP_ID > Objectives.RTP_ID and Objectives.OBJ_ID > Measures.OBJ_ID but it doesn't work, it thinks that the scores apply to every site.
Please help me, I know that this may not make sense without seeing the database, but unfortunately it is too large to attach.
View 4 Replies
View Related
Nov 4, 2006
I have almost finished my current database but I was asked to create a log table/log file that would list changes made to every record. Now my current database don't allow duplicate records, so any advice pointing me into the right direction will be helpful. I have ran through the search area and found nothing that I can use. Can any one help me out in this specific problem. I picked up a few books and none of them give examples of such things. Thanking you all in advance...
View 14 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
Nov 30, 2005
I have a database that has a linked table within it. I need to enforce referential integrity on this relationship but it won't allow me too.
Does anyone know if this can be done?
If so, could you possibly point me in the right direction
Thanks
View 4 Replies
View Related
Nov 16, 2004
Hi everyone,
Could someone please share their opinion with me on the following:
I have a database with a Table which stores People's details, e.g. ID, Name, Surname, ...., etc (e.g. tblPeople) .
Now I have people filling out a questionnaire, of which the results I want to save in an Access table. Each person will fill out this questionnaire only once. The number of fields I will need to accomodate the answers to questions asked, is around 120. For example, an answer to Question1 will appear in a field called Q1.
I know that the limit for fields in a table are 255, and my total fields in tblPeople so far are about 20.
Should I keep Q1 and all other fields in the same table as tblPeople, or create another table to hold all answers and create a one-to-one relationship between tblPeople and tblQuestions&Answers ?
Can someone give me a tip, in particular if this will cause performance problems?
I appreciate your effort for reading this.
Kind Regards,
Jean
View 2 Replies
View Related
Dec 22, 2004
I am designing a database for my company that keeps track of new classes and new courses created. I have a form for each with the create a class form taking the course title from the courses table/created course from the create a course form.
Here's my dilemma. I want to use cascading combo boxes for class location, facility and room but I think my table structures and relationships may be incorrect.
Here is a list of my tables and their fields:
tblClass
ClassID (PK)
CourseID (FK)
InstructorID (FK)
StartDate
EndDate
StartTime
EndTime
ClassMin
ClassMax
Materials (yes/no)
MatNotes
EmpID (this is the ID of the employee who requested a class creation)
LocID
FacID
RoomID
LogID (Logistical Coordinator ID, if applicable)
Course
CourseID (PK)
CourseName
more...
Employee
EmpID (PK)
FName
LName
more...
Instructor
InstructorID (PK)
FName
LName
more...
Logistical
LogID (PK)
FName
LName
more...
Locations
LocID (PK)
Location
Facilities
FacID (PK)
Facility
LocID (FK)
Rooms
RoomID (PK)
Room
FacID (FK)
LocID (FK)
When I run a query with class, locations, facilities, and rooms no information shows up. If I run the query with the relationships between facility/room, facility/location and room/location removed (leaving the only relationships between class and these three) it works. Why is that? Shouldn't there be a 1-M relationship between Location, Facility and Room? Or, should there be a M-M relationship and I should create tables with each PK?
I apologize for the long post but this has become quite frustrating (despite the simple logistics).
Thanks!
View 1 Replies
View Related
