How To Stop Duplicate Records
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 Replies
ADVERTISEMENT
Feb 25, 2005
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 2 Replies
View Related
Oct 22, 2006
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 1 Replies
View Related
Dec 3, 2012
I have one Form & Table. I am making customer list, But i dont want a duplicate name, so how i control or stop duplicate name when i do entry.
View 1 Replies
View Related
Apr 3, 2013
I have a database that lists all the books I have read. The Table "Books" holds "AuthorId' "Title" etc. I have another table that is called "Whats Next To Read" which I store the next book that I will read from that author. It also uses "AuthorId" and "Title" etc. In the form, how can I look to see if the title put in for that author in "Whats Next" does not have the same title already in "Books" table. I can use Dcount to find the title but it could be same title different author. I need same title with same author.
This is what I have so far.
If DCount("Title", "Books", "Title = '" & forms!Whatsnexttoread!Title & "' > 0 Then
Msgbox "Title is already in books.", vbInformation
End If
I need it to only look at the same author.
View 3 Replies
View Related
Mar 25, 2007
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 1 Replies
View Related
Mar 23, 2015
I'm using an append query that needs to add the records from another database into a table. I can get it to get the data and add the records. There are other columns in the database that are not in the one the data is pulled from. If I would run the append query again, it would add the same records again at the end. How can I avoid this? I only need to add new records that are not yet in the database I want to pull the records to.
View 4 Replies
View Related
Jul 3, 2013
I have an excel file, and am trying to import it, and once when I have imported it, am checking for a condition with While Not rs.EOF, it works fine for the purpose of looping, but it does not stop when the records are over., it is additionaly printing 19 null statements, where I beleive it, should not be.
View 9 Replies
View Related
Sep 22, 2013
I have a sales sytem in Access 2010.New customers have to be imported to MYOB daily. I have a query that finds new customers and appends them to a table for importing at a later stage. I export the records in that table if the field "imported" is set to false. The results are exported to Excel so they can be imported into MYOB after some additional data is added. If there are no records to append to the import table I want to stop the export from happening. I am new to VBA and don't know how to express that the select query "000 Append New Customers...." should only run if the append query has records. If there are no records found, I want to put a message saying "no records found". Here is the code I have behind the command button:
Private Sub PrepCustcmd_Click()
'Turn warnings off
DoCmd.SetWarnings False
DoCmd.OpenQuery "000 Append New Customers to MYOB Customers", acViewNormal, acEdit
[code]....
View 2 Replies
View Related
Sep 19, 2013
I have a form that allows a user to complete a stock take. I would like to stop other users from receiving or despatching stock while a stock take is in progress.
Is there a way I can lock a table, or stop users adding or altering records that match certain criteria. i.e. don't let users receive or despatch stock from with a locationID of 'A'.
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 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
