How To Stop Duplicate Records
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 Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Stop Duplicate Records
I have an user imput form that has a unique file reference number which is typed in by the user in the first bound field. Does anyone know of a way of informing the user that the file reference number is already in use and therefore a new one must be used. At the moment the user is completing the entire form before being made aware that it would create duplicate records and therefore unable to save current record etc...Totally fustrating! any ideas? Steve
View Replies !
View Related
Stop Duplicate Request
I will explain this situation using a scenario. Let's assumed that i'm working on a library system where i need a loan form that will only show books that are available for loan. In this case, this form has a main form that shows library member's details and its subform shows the details of the books that are loan by library member. In this subform, one of the fields (ISBN no) displays its values using a combo box. Once a value is selected from this combo box, values in other fields found in the subform will be shown too. These values are based on a table that contain books info, where only the value (ISBN no) in the combo box comes from a query. This query will only show books that are available for loan. If a library member wants to borrow 'booktitle1' then this book will not be available to be loan by other members and assumed that there is only one 'booktitle1'. I tried to update the field manually by changing the status field every time the book is loan out. I hope to solve this problem in an effective way. I am new with access and i have difficulty to explain it in a much better way. Sorry, if my description cause any sort of confusion. These are the tables. book_info (table1) ------------------------------- bookTitle ---------- text ISBN(pkey) ------- text authorName ------ text category ---------- text dateReceived ---- date publisher ---------- text status -------------- text user_info (table2) -------------------------------- name --------------- text userID(pKey) ------ text address ------------ text tel ------------------ number hp ------------------ number occupation -------- text DOB --------------- date loan_info (table3) ------------------------------ userID(fKey) ------ text ISBN(fkey) -------- text dateReturned ---- date dateBorrowed ---- date dateDue ---------- date remarks ----------- text Thanks. Leah_603
View Replies !
View Related
Stop/SKIP Appending Query If Duplicate Found
I want to stop/skip appending of those records who match in main data table "MCIGMMS" on the basis [PORTCD] [IGMNO] fields in "PCIGMMS2". When Match found Msg Box appear and appending skip or stop of those records. Can any one help me or give me code. Regards. SQL CODE: INSERT INTO MCIGMMS ( PORTCD, IGMNO, IGMYY, IGMDAT, SHPANO, VSLFLT, VOYAGE, ARVDAT, SLOCCD, CNTCOD, LSTPRT, CAPTAN, SHPCMP, TOTIDX, BOTTOM, BEGIDX ) SELECT PCIGMMS2.PORTCD, PCIGMMS2.IGMNO, PCIGMMS2.IGMCY, PCIGMMS2.IGMDAT, PCIGMMS2.SHPANO, PCIGMMS2.VSLFLT, PCIGMMS2.VOYAGE, PCIGMMS2.ARVDAT, PCIGMMS2.SLOCCD, PCIGMMS2.CNTCOD, PCIGMMS2.LSTPRT, PCIGMMS2.CAPTAN, PCIGMMS2.SHPCMP, PCIGMMS2.TOTIDX, PCIGMMS2.SBOTTOM, PCIGMMS2.BEGIDX FROM MCIGMMS RIGHT JOIN PCIGMMS2 ON MCIGMMS.IGMNO = PCIGMMS2.IGMNO;
View Replies !
View Related
Qry Results Show Duplicate Records, Records Are Not Dup In Table.
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 Replies !
View Related
Duplicate Records
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 Replies !
View Related
Duplicate Records
Ammendment: I want the user to be able to enter the ammount of records to be duplicated into a table. Duplication is required all fields are the same with the exception of the PK neing an auto number. I understand that I may need to use some sort of Loop but need help to doi it. Been looking lots of placed to no avail Thanks all
View Replies !
View Related
Duplicate Records
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 Replies !
View Related
Duplicate Records
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 Replies !
View Related
Duplicate Records!
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 Replies !
View Related
How To Delete Duplicate Records
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 Replies !
View Related
Random Duplicate Records
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 Replies !
View Related
How To Prevent Duplicate Records?
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 Replies !
View Related
Identifying Duplicate Records
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 Replies !
View Related
Count Duplicate Records
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 Replies !
View Related
Duplicate Records In Queries
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 Replies !
View Related
Finding Duplicate Records
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 Replies !
View Related
Remove Duplicate Records
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 Replies !
View Related
Query For Duplicate Records
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 Replies !
View Related
Duplicate Records Delete
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 Replies !
View Related
------->>> Please Help On Filtering Out Duplicate Records.
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 Replies !
View Related
Deleting Duplicate Records
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 Replies !
View Related
Searching For Duplicate Records
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 Replies !
View Related
Delete Duplicate Records
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 Replies !
View Related
Duplicate Specified Number Of Records
Acc 2003 Hi, Does anyone know if this is possible. I've looked but can't find anything like it posted within the forums unless I'm searching for the wrong keywords. If I have a list of records on a continuous form, I want to drop a combo box at the end of the each record with a list of numbers in ranging from 1 - 100. When a value is chosen, the selected record is then duplicated by that number. For example, Select a record, choose 20 from the combo box = 20 new entries of the record. Also, as a side issue, is there a way of omitting or clearing certain fields from the process. For example, Serail Numbers are unique, so I want the user to have to manually key these in once the duplication process completes. Hope I have explained this in enough detail. Thanks Kempes
View Replies !
View Related
Flagging Duplicate Records
I have a table that can contain duplicate records with the same key value. TrackID, INV, Status I need to update the table so that for each INV value, one and only one record is set with a "XFER" status, and any other record gets a "N DUP" status. It doesn't matter which record get the XFER, so long as only one does. For the life of me, I can't think how to write the SQL to do this. Any help? Thanks, David
View Replies !
View Related
Accessing Duplicate Records
Hi all. In my database, I have a indexed (duplicates okay)field called WoNum. The WoNum is a value that is assigned from our ERP system, and it is not the primary key - I have an autonumber for that. The issue I have with this is that when the users enter the same WoNum more than once, then they cannot access the data for the other entries. I have gotten around this temporarily by giving them a command button that runs a parameter query (with a Like statement on the WoNum field). They can then update the record they need through the query results window. However, I know this is not the best way to handle the situation. What I need is one of two things. Either: 1. Figure out a way that they can access the correct record through a combo box or 2. Have a query run automatically when they type the WoNum in the field and then give them a results list where they can click on the correct one and then have the form fill with the correct information. I have thought about requiring them to use the autonumber but we have a hard time just getting them to put in the correct information much less asking them to write down a reference number to use in the future. If anyone can give me some detailed help, I would greatly appreciate it. Thank you Camille
View Replies !
View Related
Removing Duplicate Records... Help Please
Ok well I thought I was doing this right but it appears I have not. I am drawing data from two tables. Both of them related by the person's name. One table I am pulling the unique ID number, the other table contains the name. I have a PO (Purchase Order) field being pulled from the table that holds unique ID. Or lack there of, so I am pulling all the records that contain no PO number. However duplicate unique ID's are being pulled. The way my query is set up now is: Unique ID Number Name Count (Number of Procedures) Charges PO Number - Criteria: "Is Null" I have also right click in the table view area, in the properties I selected Unique values. I have to assume that this would have held back any Dupes from the output. However this is not the case. When I looked in the help files the only thing I could find was: http://office.microsoft.com/en-us/a...0840151033.aspx Quote: "If you want to see only unique values in a result set, you can specify that you want to exclude duplicates from the result set. In the Database window, click Queries under Objects, click the query you want to open, and then click Design on the database window toolbar. Right-click the background of the Diagram pane, then choose Properties from the shortcut menu. Select DISTINCT values. The Query Designer inserts the keyword DISTINCT in front of the list of display columns in the SQL statement. Note If you use the DISTINCT keyword in Microsoft SQL Server, you cannot modify the data in datasheet view." The problem with this is my properties box does not offer a Distinct Values option. So my question is. Is there an expression I can place in the Unique ID field that will exclude any duplicate records. Sorry for the book Sean
View Replies !
View Related
|
TRACKER
