How To Tackle Duplicate Records Issue
Apr 8, 2007
In my database I have a a date field and Number field with sequential numbering with DMax which will reset to 1 when the year changes. I reset the PC clock to 2008 and checked by entering two records and it works fine. Now my problem is how do I tackle the problem of duplicate records. The DB displays No.1 record from 2007 and another No.1 record from 2008. Is there a way to ask the user to input the year and then the DB takes up only the desired year's records for data entry, search, print etc. Shall be grateful for help.
View Replies
ADVERTISEMENT
Mar 16, 2012
I am creating a database from an Excel Workbook. Many of the calculated cells are dependent on the values of other calculated cells.There are more than 255 cells for the one sheet so I can't use one table.I was forced to use multiple tables and place the calculation fields in their own tables for the use of their fields in a calculating query, although they won't be seen on any form or report.
What is the best way to relate these tables even though they should really be all part of one table? Should I just use a Cross Join? Will a cross join allow me to properly calculate with a field from an unrelated table? What about an Outer Join?I need to reinforce integrity so that if one set of table data is deleted, so are all the others.I have already tried to place a "Report Number" autonumber field on each table, but it doesn't seem to allow me to reinforce the integrity and gives me, "invalid field name of definition of index or relationship".
View 4 Replies
View Related
May 29, 2014
In my simple database (attached), I need to mass duplicate Tasks and their Notes.
I have three tables: tbTasks (PK: Task_ID), tbNotes (PK: Note_ID), jtbTaskNotes (FKs: Task_ID and Note_ID). jtbTaskNotes is my many-to-many junction table that ties Tasks to Notes.
The main form (fmTasks), bound to tbTasks, has a subform (sbfm_TaskNotes) that displays notes associated with each Task. On themain form,you select which Tasks you want duplicated via a checkbox. The append query (quCopyTasks) will duplicate all tasks that have the checkbox checked. All good there. However, I can't figure out how to also duplicate each task's Notes.
I found Allen Browne's solution [URL] ....., but that only handles duplication of one record at a time, whereas I need to duplicate many records at a time (sometimes 10+ records). How do I go about duplicating multiple Tasks and their associated Notes?
Before you ask "why are you duplicating records?": There are times when tasks need to be re-accomplished and therefore need to have a new record. It's easier to duplicate records than it is to hand-jam everything again.
View 5 Replies
View Related
Nov 16, 2004
I have built a qry that initially shows the correct information. For example.
tblContent has 289 records with a Type = Class.
I built a Query to select from tblContent Type = Class and I get 289 records. I add additional criteria of Progress <>"Not Scheduled", I then get 206 records. I then add additional criteria Last Name <>"Demo" And <>"Care" And <>"Support". This brings up 200 records, but the query appears to duplicate each record 3 times. I do not have 3 of the same types of records.
The SQL Statement is below
SELECT tblProfile.LoginName, tblProfile.FirstName, tblProfile.LastName, tblProfile.Organization, tblProfile.CostCenter, tblContent.Title, tblContent.Type, tblContent.Code, tblContent.[Date Assigned], tblContent.[Date Started], tblContent.[Last Accessed], tblContent.Progress, tblContent.[Date Completed]
FROM tblProfile INNER JOIN tblContent ON tblProfile.LoginName = tblContent.LoginName
WHERE (((tblProfile.LastName)<>"Demo" And (tblProfile.LastName)<>"Care" And (tblProfile.LastName)<>"Support") AND ((tblContent.Type)="Class") AND ((tblContent.Progress)<>"Not Scheduled"));
The qry is named qryPhysical Class. I have provided the link to view the database. Can you help me?
http://briefcase.yahoo.com/turnerbkgabrobins
Thank you in advance for your assistance.
View 1 Replies
View Related
Mar 19, 2007
I want to find duplicate records based on FirstName and LastName and delete the duplicate. Also, I want to delete any records which have a blank FirstName and LastName.
How can I do this?
Thanks,
Dave
View 3 Replies
View Related
Jul 5, 2005
pls can someone point me in the right direction
have searched through the forums.
i have two fields Forename & surename i need to prevent duplicate records if the forename and surename match another record.
View 2 Replies
View Related
Oct 24, 2006
Hi
I have a table (which is populated by people filling in a form) which contains two fields:
Section
WeekCom (e.g. week commencing)
I want to make sure that the form will not allow someone to make a double entry. For example, if Sally goes in and says enters "Benefits" as the section and "WeekCom" as 15/10/06, and then Ben tries to do the same - the form will not allow Ben to enter it.
How do you do this please? The table does not have a primary key at the moment....is there any way of doing it without setting one of the fields as a primary key?
Thanks
Maria
View 5 Replies
View Related
Apr 9, 2007
I Need to run a query that will not show duplicate records.
For example if joe smith is listed five times i only want the most recent date of membership.
Does anyone have any ideas?
View 1 Replies
View Related
Apr 30, 2006
I have a form in Access which has event date and then also an event time slot such as "Morning, Afternoon, Evening" . I need to make it so that you cannot have a duplicate time booking, i assume i will need to code some VBA but if anyone could tell me what it would be much appreciated. Thanks
View 2 Replies
View Related
Feb 7, 2008
i m new user to access i just wanna know how can i count duplicate recodes for example how many time
record repeat in one column
waiting for ur quick reply thanks
View 1 Replies
View Related
Apr 30, 2007
Hello,
I have a table that has many duplicate records. I tried importing the table and setting the field to no duplicates but did not work. How can I delete duplicate records. The primary Id id fine I have another field that I can use. it is a BAC number that is repeated many times. Here is the table I need fixed.
Thanks
View 1 Replies
View Related
Feb 20, 2006
Hi Guys,
I hope someone can help with this. I have a table, "Blasthole Submission" which is populated by input in a form, using the code below:
Const MyTable As String = "Blasthole Submission"
Const MyField As String = "Sample Name"
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim intCounter As Double
Set db = CurrentDb
Set rs = db.OpenRecordset(MyTable)
For intCounter = Me.txtStartValue To Me.txtEndValue
rs.AddNew
rs.Fields(MyField) = "TP" & intCounter
rs.Fields("Submission #") = Me.SubNum
rs.Fields("Sample Type") = "Blasthole"
rs.Fields("XRF") = "True"
rs.Fields("LOI") = "True"
rs.Update
Next intCounter
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
What I am hoping to do is to place a random duplicate in the table, called, for example TP111152 DUP, approximately every 50th record. Is there any easy way of doing this?
Thanks in advance for your help!
View 11 Replies
View Related
Jan 13, 2007
i am using a textbox in the main form to transfer the Yr/Mth into the table. (refer to qp.zip)
now my problem is, how do i prevent duplicate records into the table?
e.g: currently whenever i scroll ard the months control in the form, i will get duplicate Mth/Yr into the table. how do i prevent that from happening?
i only want to have updated data inside the Mth/Yr table instead of duplicated Mth/Yr
Public Sub PutInMonthlyRecords()
Dim sql As String
Dim Db As DAO.Database
Dim rs As DAO.Recordset
Dim f As Form
Dim MthYr As String
Set f = Forms!frmQpi
MthYr = f("txtMthYr")
sql = "SELECT * FROM [tblQpiMonthly] WHERE (([tblQpiMonthly].[Input MthYr] = 'MthYr' ));"
Set Db = CurrentDb()
Set rs = Db.OpenRecordset(sql)
If rs.RecordCount = 0 Then
If (f!txtTotalPF) = 0 Then
Exit Sub
End If
rs.AddNew
rs![Input MthYr] = f("txtMthYr")
rs![Monthly TotalPF] = f("txtTotalPF")
rs![Monthly Rejection] = f("txtTotalAvoid")
rs![Monthly TotalAvoid] = f("txtRejection")
rs.Update
Else
If (f!txtTotalPF) = 0 Then
rs.Delete
Exit Sub
End If
rs.Delete
rs.AddNew
rs![Input MthYr] = f("txtMthYr")
rs![Monthly TotalPF] = f("txtTotalPF")
rs![Monthly Rejection] = f("txtTotalAvoid")
rs![Monthly TotalAvoid] = f("txtRejection")
rs.Update
End If
Db.Close
End Sub
View 1 Replies
View Related
Jun 26, 2005
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.
View 2 Replies
View Related
Mar 27, 2006
I have an order database using Access 2000, I need a report to list all agents that have ordered in the past year, how many times they ordered and sort by Office city.
I have
SELECT tblCustomers.Type, tblAgentList.AgentID, tblAgentList.LastName, tblAgentList.FirstName, tblAgentList.Agency, tblAgentList.AgencyCity, tblAgentList.DeliveryAddress, tblAgentList.DeliveryCity, tblAgentList.DeliveryState, tblAgentList.DeliveryPostalCode, tblAgentList.[AgentOffice#], tblCustomers.InspectionDate
FROM tblAgentList INNER JOIN tblCustomers ON tblAgentList.AgentID = tblCustomers.AgentID
GROUP BY tblCustomers.Type, tblAgentList.AgentID, tblAgentList.LastName, tblAgentList.FirstName, tblAgentList.Agency, tblAgentList.AgencyCity, tblAgentList.DeliveryAddress, tblAgentList.DeliveryCity, tblAgentList.DeliveryState, tblAgentList.DeliveryPostalCode, tblAgentList.[AgentOffice#], tblCustomers.InspectionDate, tblCustomers.[Job#]
HAVING (((tblCustomers.Type)="P") AND ((tblCustomers.InspectionDate)>[Enter starting Date:]))
ORDER BY tblAgentList.AgentID, tblAgentList.AgencyCity, tblAgentList.AgentID;
It list all agents sorting by AgentID, with a line for each order.
I would like to list each agent once with the number of times ordered, and then sort by AgentCity. Any help would be appreciated... Thanks in advance!:D
View 3 Replies
View Related
May 2, 2006
I have two table. Deliveries Created (DC) and Goods Receipt (GR).
I have a query which links the two by PO number, PO Item, Unit code and Delivery quantity.
This works fine until one PO Number has more than one delivery quantity which is equal. For example.... The PO quantity is 4,000 but, because the units can only be shipped in quantities of 1000, there are four different entries in both the DC and the GR tables each with the same PO number, PO Item number, Unit code and delivery quantity (and shipped on the same date). This of course gives 16 results for the query using this PO number.
There is one unique field in the DC table this is the posting number field. I've tried grouping by this number and taking the First values from the GR table. This works OK in that it produces Four results but, two of the deliveries were received on Day 1 and two were received on Day 2. If I take First of GR date I get DAY 1 for all, If I take Max I get Day2 for all. What I need is the correct date.
I hope I have explained this OK.
And I hope you can help
View 6 Replies
View Related
May 10, 2006
I thought I had just about finished my DB but now Ive printed out and checked my reports I notice I have several records in different types of reports showing duplicate records. Iv'e gone back over the queries and there are one or two duplicates in several of my queries that I did not notice before because there are are only the odd one or two. No matter what I do I can't stop this happening. I think it must be something to do with the dates in my payment table. PaymentID is key field - foreign key is MemberID. One member can have several paymentID's a new one every year when membership renewed. The problem seems to be when the member has two payment dates in the same year. This shouldn't be anyway but the database has not been used properly with entry dates missing, written over of wrong dates etc. Ive tried to correct this but do not want to tamper with past payment date records. I already have 'select distinct' in my queries and have tried 'distinct row' which seems to return even more duplicate records. Im pulling my hair out over this there must be a way to return the records from members showing just 1 only of their very latest payment record/date. Im using this expression in the query.
LastPaid: (SELECT MAX(PaymentDate) FROM S_Payments_Table WHERE S_Payments_Table.MemberID =S_Members_Table.MemberID)
View 1 Replies
View Related
Jun 19, 2006
Hello
I've had to export data from an old third party database to Access with the task of reporting on the data. I have a client table and work tables, one for each year going back to 1992. In the original database, the clients do not have a date of when they were set up. I need to find out how long clients have been with us, so I thought of going back through the work tables 2006 to 1992 and finding out where they first occurred, by looking for duplicate records in the tables. Is this a simple enough thing to do? The client table and work tables are linked through the field Client.
Thanks
Dave, England
View 4 Replies
View Related
May 1, 2007
Can anyone suggest a way to remove duplicate records.
I've come up with this:
SELECT *, count(*) cnt FROM tableName GROUP BY fieldname1, fieldname2, .... HAVING cnt > 1
But this does not fully resolve my problem.
Thanks.
View 2 Replies
View Related
Jul 9, 2007
I am creating an ordering system which comprises of amongst others, these fields:
ID
OrderHdr
OrderLine
I want to ensure that if a user enters an ID, OrderHdr and OrderLine that this combination does not already exist elsewhere within the same table.
I have written a query like this:
COUNT OrderLineDetails.id, OrderLineDetails.orderno, OrderLineDetails.orderline
FROM OrderLineDetails
WHERE (((OrderLineDetails.id)=?) AND ((OrderLineDetails.orderno)=?) AND ((OrderLineDetails.orderline)<>?));
I will be replacing the "?" with the relevant form fields from the form, but how do I run the query and store the result in a variable and prevent the user from carrying on until they change any of the fields until there is no duplication.
View 3 Replies
View Related
Jan 14, 2008
On the below data I need to delete records where dep is blank or the same for the same ID. So I need to find and del recNr. 1,4,5. Any help will be appreceated!Thanks
recNr IDDep
1 ABX
2 ABX 1234
3 ABX 1258
4 CDE
5 CDE 7895
6 CDE 7895
View 5 Replies
View Related
Feb 6, 2008
Hi,
I need to remove all duplicate records in a table. These are records that have matching Contract and Order fields. I can't do a DISTINCT query, because that still leaves one record. Thanks.
Michael
View 5 Replies
View Related
Mar 14, 2008
Hi there,
I'd like to filter out so that the name only appear once base on LastOfDate.
Sample data in query
Customer_____Date__________Product
John_________02/02/2008_____A
John_________05/03/2007_____B
John_________03/06/2006_____A
Nick_________04/04/2006_____A
Nick_________04/04/2007_____B
James_______05/04/2008______A
So in the query, I just want to have John, Nick and James appear once with the LastOfDate.
The result should be
John_________02/02/2008_____A
Nick_________04/04/2007_____B
James_______05/04/2008______A
I included the db file which only perform LastOfDate and unique name, however since John has product A and B, it won't get rid of B.
SELECT DISTINCT query1.Name, Last(query1.Date) AS LastOfDate, query1.Product
FROM query1
GROUP BY query1.Name, query1.Product;
I just want one Name, LastOfDate and one product, regardless of A or B.
Thanks,
Isabel
View 1 Replies
View Related
Apr 30, 2008
I am having problems deleting various rows from an SQL table, the message coming up is "The row value(s) updated or deleted either do not make the row unique or they alter multiple rows (xxrows). Any Suggestions?
View 1 Replies
View Related
Apr 12, 2005
Hi all,
With some help from this forum (esp. Pat Hartman), i've been able to code up a script that performs a check on duplicate values.
My database has a Room Bookings form which consists of the room name, periods and booking date (BDate) fields. I need to prevent a duplicate on the same date, period and room.
The only problem with my code is that it only does a check on one of the fields e.g. BDate although i select a different period or room it still comes up with an error message saying that booking already exists? This means it is only checking the duplicate value of 1 field and not a combination of fields.
Here is my code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strSearch As String
Dim varKey As Variant
strSearch = "BDate = #" & Me.BDate & "# And Period1 = """ & Me.Period1 & """And Period2 = """ & Me.Period2 & """And Period3 = """ & Me.Period3 & """And Period4 = """ & Me.Period4 & """And Period5 = """ & Me.Period5 & """And Period6 = """ & Me.Period6 & """And Lunch = """ & Me.Lunch & """And After_School = """ & Me.After_School & """"
varKey = DLookup("Booking_ID", "Furtherbookings", strSearch)
If Not IsNull(varKey) Then
If MsgBox("Booking already exists Booking ID: " & varKey & ". Do you wish to continue to create a new record?", vbYesNoCancel) = vbYes Then
Else
Cancel = True
Me.Undo
End If
End If
End Sub
Can any1 identify where i am going wrong?
I'd really appreciate any help/guidance to resolve this problem!
:(
View 1 Replies
View Related
Feb 2, 2006
Help please!
I have a form (frmHalfTab) built on a table (tblHalfTab2) that has existing data in it. In the very near future, I will be uploading additional data to this table via excel cut and paste. What I want to do is search for and eliminate duplicate records that have duplicate data in TWO fields, (MemberNum) and (MBRDrug), so that if MemberNum is "10" and MBRDrug is "Zzz" for more than one record, the duplicated records need to be deleted, OR a check box needs to be checked automatically for all the identical records, the original and the duplicate(s). I wanted to put this behind a button, if possible.
Now, I have created a duplicate query that will find the duplicated records, but I'm not sure how to use it to get rid of the duplicated records or check the checkbox appropriately. Should I ditch this query and choose a different route, or am I on the right track? Thanks in advance for your help.:confused:
View 2 Replies
View Related
