How To Avoid Duplicate Data
Apr 28, 2006
I have inherited a 5 table database which is associated with membership details and all tables are linked via the membership number which appears in every table..eg
Main Register
membernumber, surname, forename, age etc etc etc
Home address
membernumber, address1, address2, address3, postcode
Business address
membernumber, company name, address1, address2,etc etc etc
Subscription
membernumber, grade, subscription, bank account, etc etc
Training
membernumber, college, results, etc etc
I am informed that it is bad practice to have duplicate data in multiple tables (ie. membernumber field) as updating the data item would be difficult (eg. if the members number needed to be changed for any reason). If this is the case how would I restructure the tables but maintain the link through the membernumber?
I am fairly new to Access....as you can no doubt tell.
View Replies
ADVERTISEMENT
May 28, 2014
I have 3 tables
tbl1 :
NameID
Name
tbl2 :
CatID
Category
tbl3 :
ProdID
CatID
NameID
Remarks
How can I avoid duplicate records in tbl3? Since remarks can be Null? product is later used in tblInventory...
View 1 Replies
View Related
Dec 31, 2010
I have a report which has say two columns - staff number and visit date. For a staff number (which is unique) there may be more than one visit date. But I want to list a staff number only once and not multiple times in the column (but want all the visit dates of course). How can I do that?
View 14 Replies
View Related
Jun 10, 2013
I am not sure if it is possible to implement this but I would like your input. We get request from client via a web based form which contains a unique ID number called RDEFNumber. When we process the request internally, we use this number to identify the request throughout the process. The request from client contains processing of multiple files which are transmitted to us in different times of the day or days.
Every time we receive a file, an email is sent to the IT team with all the information about the file. We use an internal database to send the email to the IT team and use the RDEFNumber as the identifier in the subject line. What I am trying to do is to add an additional number at the end of the RDEFNumber automatically to differentiate the subject line.
For example, we take "1791" as a RDEFNumber. For the first time we enter 1791 to the database RDEFNumber field, it should check the table "tblRDEF" (record source) for duplicate value. If no Duplicate exists, it should keep the number as same. When we get the next file for processing with the same 1791 number and enter this number in the RDEFNumber field, it should automatically change it to "1791-1" since "1791" already exists in the database. When we get the next file, the number should change to "1791-2" and then "1791-3" and on.
View 13 Replies
View Related
May 31, 2014
How can I prevent duplicate records from being added from a form, the dcount in the text field property, trigged before update is where this should be used is what I know but having trouble with the syntax.
I've got the table tblInvnetoryDetail with InvID(AutoNumber), and SerialNumber. The form text field is txtSerialNumber. I've managed this far with the expression
DCount([InvID],"tblinventoryDetail","[txtSerialNumber]=&"'") but this did not work.
How can I get the expression to avoid duplicates.
View 4 Replies
View Related
Feb 22, 2007
In an MS Access 2000 - SQL Server 2005 project I use a "Microsoft Office PivotTable 9.0" control on a form. The control is populated by a stored procedure. This works fine but when the pivottable opens a messagebox with the title "Microsoft Data Access Components" and a message "This page accesses data on another domain. Do you want to allow this ?" appears.
After clicking "OK", a message box with title "Microsoft Office" and message "This website is using your identity to access a data source. If you trust this website, click OK to continue, otherwise click Cancel".
After clicking "OK", the pivottable opens.
How can I get rid of these messages ?
On the internet I found some suggestion to add a website in the Internet Explorer settings "Trusted sites". What's the meaning of that ? The data for the pivottable is in a local SQL Server database, so which website do I have to add to the "trusted sites" ?
Any solutions for this problem ?
One other question: is there an alternative for the "Microsoft Office PivotTable 9.0" control ?
View 1 Replies
View Related
Feb 10, 2008
Hi I've just signed up to the forum.
I'm currently working on a booking system, where clients can make appointments.
However as I am fairly new to access, I would like to know how I can inforce some sort of validation where for example if a client makes an appointment on 17/02/08, then it checks to see if there are any current appointments of theirs on the day, if there is, then an error message would occur
View 1 Replies
View Related
Jun 29, 2006
I have a medical database (Access 2003) containing information about patients. There is 1 form that is used to enter the information and a check box to say whether the patient has cancer.
I run a simple query to select the cancer patients.
I want to be able to add a large amount of information about (only) the cancer patients through a separate form. I also want a neighbouring hospital to be able to add cancer patients.
The ideal solution (I think) is to create an extra table to contain the extra information for cancer patients, and some of the informationfrom the original table. Cancer patients need to be automatically added to the cancer table once the "cancer" box is checked.
So far I have not ben able to achieve this - a create table query does not update automatically. I have tried creating a new table with the cancer information and creating a relationship between the tables and basing the form on the cancer table and a select cancer query. This does not work either.
Please can anyone suggest a solution! Do I need another database?
Thanks
View 2 Replies
View Related
Jul 11, 2007
I am trying to sum values of duplicate data in a table using Make Table query. Can anyone help? I have attached a sample of the data. At the end of the day, the objective is to have a single line with the total of the multiple lines.
BU_Asset IDYTD Depr
US031-00000001 12,962.82
US031-00000001 142,591.07
US031-00000002 356.87
US031-00000002 1,784.36
US031-00000002 2,141.23
US031-00000003 5,344.72
US031-00000003 58,791.92
US031-00000004 37,315.37
US031-00000005 -
US031-00000006 294.13
US031-00000006 1,470.66
US031-00000007 -
US031-00000008 -
US031-00000009 562.04
US031-00000009 2,810.23
US031-00000009 3,372.26
Thank you very much.
View 1 Replies
View Related
Aug 28, 2006
Hi,
Anyone has experience for barcode. Example, if you scan the first time then it will appear total 1 and if you scan again with the same barcode, then the total will change to 2 ...etc.
Anyone has experience, please share.
Thank you
Regards,
Richard Tan
View 2 Replies
View Related
Nov 2, 2006
Hi,
I have got a table that has following fields display on a form:
ClassNo Class Date ClassType ExecutionDate Order Quantity
The Table CLASSES has got many records which are duplicate. Mostly ClassNo is duplicated. I could have stopped duplication by restricting table not to accept duplicate values, but my requirement is such that i dont want to stop the table from accepting duplicate values. When i display the same table data on the form, all the duplicate entries are displayed.
All i need is to find a way to stop the FORM showing duplicate ClassNo Field. The Duplication restriction i wish to set only on ClassNo. If there are more than one ClassNo reptition than it should show that entry only once. For example the form looks like this:
ClassNo Class Date ClassType ExecutionDate Order Quantity
1 12-12-2006 A 12-01-2007 Ok 10
1
1
1
1
1
2 20-10-2006 A 20-11-2006 Ok 100
2
2
2
2
I wish to see the form this way:
ClassNo Class Date ClassType ExecutionDate Order Quantity
1 12-12-2006 A 12-01-2007 Ok 10
2 20-10-2006 A 20-11-2006 Ok 100
Regards,
Darno
Sorry i am posting it second time coz i got no response from any one, Hope this time some one genius might surface to help me.
View 1 Replies
View Related
Jun 13, 2007
I have a database that allows multiple entrys of the same information. I also have a report based on a query that pulls out said data.
I just need my query to pull out every record regardless if it is indentical to a previous one. I found under queries > design > properties there are two options unique values and unique records. According to a source online, these are what control allowing duplicates through, but I have swapped them around with no luck. Currently they are both set to "no".
Is there some other setting I have to switch up in order to allow the duplicates to come through?
Thanks
View 1 Replies
View Related
Mar 23, 2005
I have a form that contains 5 fields and want to ensure that duplicate information is not entered. I am aware that when creating the form you can set Duplicate Records to No but each record has an AutoID which I assume means that even if the information is the same it will not register as a duplicate record due to the different AutoID.
There should only ever be 5 records entered per day. One for each region
I have created a Sanity Check that is displayed as a sub form that shows if the relevant information has been entered for a given day in Bookings and Backlog but this doesn't stop the user from putting in the same information twice. i.e. the User starts inputting the information gets called away and then trys to put it in again but doesn't check the sub form.
The Table in which the information is stored is called Bookings and Backlog.
The 5 fields on the form which is called Bookings and backlog are
ID (Autonumber)
Date (Short Date)
Combo26 (Text) (5 Regions:- North, South, East, West, Central)
Bookings (Number)
Backlog (Number)
I have put the following statement in AfterUpdate on the Combo26 field
If Me.Date = DLookup("[Bookings and backlog]![Date]", "[Bookings and backlog]", "[Bookings and backlog]![Date]=[Date]") And Me.Region = DLookup("[Bookings and backlog]![Region]", "[Bookings and backlog]", "[Bookings and backlog]![Region]=[Combo26]") Then
'Define Message Box
MsgBox "The Region you have chosen already has data entered for this day." & vbCrLf & vbCrLf & "Please check your records and amend were neccessary.", vbCritical, "USER INPUT ERROR"
Else
End If
However I'm not to hot with writing VBA and probably got something wrong somewhere as it doesn't seem to do anything and lets the user progress regardless. I have tried amending the above script and asked it to look at just one field but this just seems to give the message regardless of the information selected in the Combo box.
Can someone give me an indication as to what I have done wrong so I can put it right and in a language that is easy to understand so that if I have a similar issue later on I don't need to post another question.
View 3 Replies
View Related
Jul 5, 2006
Hi,
I have searched through the whole posts but could not find my query. I have a question related to prevent duplicate data entry in a form field.
I have a Form with 5 fileds on it: SNO, NAME, ROLL NO, CLASS, SECTION.
in the table design i have set the option of NO DUPLICATES on SNO(Autonumber) ROLL NO (text).
I wish to stop a user entering duplicate data in ROLL NO field before he reaches at the last field. What i meant is as soon as a user enters data in ROLL No field, it should check in table if the same data is already there. If the same data is found then it should give a message to prompt user of duplicate data, else it should continue to next field.
I have searched the whole forum but in vain. I am positive that there must be some one who holds the key to this solution.
Regards,
Darno
View 1 Replies
View Related
Dec 1, 2006
Hi all: I currently have a database with a list of clients, and it is to be accessible to others to enter information on clients and to add new ones. However, I want to make it so that the user who wants to add a client can easily search the database for that particular client so that they won't enter a duplicate.
I have created a search function on my form, but then it occurred to me: what if there are two people with the same last name's? At first, I wanted a search operation to display a list of client names, along with their respective ID numbers and possibly a link next to each that would take the user to the corresponding record.
However, I have no idea how to do this, and I'm unsure as to whether or not this is the best way to tackle the problem. Please, someone show me the way!
View 7 Replies
View Related
May 29, 2005
This mind sound like a pretty lame question to the pro's....
Is it possible to duplicate an Access database creating a new blank database - retaining only the database/table structure? I have a website content management system and I want to duplicate the database for each new site.
Also, is it possible to reset the 'auto-numbering' to zero?
Whilst I've build a reasonably advanced web-based system based on a database containing a dozen or so relational tables plus queries, I'm no expert on advanced Access functions.
Cheers!
Penny.
View 1 Replies
View Related
May 21, 2007
Hello All,
I have a database where a user imports data into a table monthly. She saves the Excel file and then clicks a button in Access to import the data. My instructions say to only click that button once, but so far, she has clicked it THREE TIMES, thus importing the data three times. How can I prevent this? Here is a copy of my import code:
Option Compare Database
Option Explicit
Public Function ImportTableData()
On Error GoTo ImportTableData_Err
Dim strPeriod1 As String
Dim strMM1 As String
Dim strYY1 As String
Dim strYYYY As String
Dim strFormName As String
Dim strMDPath As String
Dim strMDFileName As String
Dim strDCPath As String
Dim strDCFileName As String
'No Warnings
DoCmd.SetWarnings False
'Import Current Month data in a temporary table
DoCmd.OpenForm "frmEnterAgingDates", , , , acFormReadOnly, acHidden
strFormName = "Forms!frmEnterAgingDates"
strPeriod1 = Forms!frmEnterAgingDates.Period1.Value
strPeriod1 = Format(strPeriod1, "mmddyyyy")
strMM1 = Left(strPeriod1, 2)
strYY1 = Right(strPeriod1, 2)
strYYYY = Right(strPeriod1, 4)
strMDPath = "S:FinanceAccounting OperationsNational AccountsAccount Recs116405.100 and 117630.300116405.100-AR Due from PAR Plans NASCO"
strMDFileName = "MD AR Due from PAR Plans_" & strMM1 & strYY1 & ".xls"
strDCPath = "S:FinanceAccounting OperationsNational AccountsAccount Recs116405.100 and 117630.300117630.300-AR NASCO Refund Due from PAR Plans117630-2007"
strDCFileName = "DC AR Due from PAR Plans_" & strMM1 & strYY1 & ".xls"
DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel9, "tblData_temp", strMDPath & strMDFileName, True
DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel9, "tblData_temp", strDCPath & strDCFileName, True
'Append tblData_temp to tblData
DoCmd.OpenQuery "qryAppend_tblData_temp_to_tblData", acViewNormal, acEdit
'Delete any blank rows that may have resulted from the import
DoCmd.OpenQuery "qryDeleteBlankRows", acViewNormal, acEdit
'Delete the temporary table
DoCmd.OpenQuery "qryDelete_tblData_temp"
Beep
MsgBox "The file has been successfully imported", vbInformation, "Files Imported"
ImportTableData_Exit:
Exit Function
ImportTableData_Err:
MsgBox Error$
Resume ImportTableData_Exit
End Function
Thank you for your help!
View 2 Replies
View Related
Jul 2, 2014
Any way to query duplicate data from two different fields from two different tables in the same access 2010 project. I first quried the first part since it combines the first 3 columns to create another value (i.e. 52 & 60 = 5260).
So I created the concatenative value but now I have to compare to another field to display what results are found in both the concatenative and the other (APN in the file). I tried using query wizard but it is for only one field. I understand you have to use joins but the destination filed is what gets me.
View 4 Replies
View Related
Aug 11, 2015
I am putting the student names in a form. I want it to alert me that a name is duplicated. But thats not this simple. For example a name is Jennifer Locus but I type jenifer with one . Can access guess that this might be the same person and suggest me to correct the spelling or enter it as a new person?
View 2 Replies
View Related
Jul 21, 2014
I am trying to find duplicate data from two columns and I want to query the all the duplicates. I tried the query wizard but it only finds duplicates in the column itself. I am trying to compare the columns together and see what is found in column A that is also found in column B and also Vice Versa.
View 3 Replies
View Related
Jul 18, 2005
I've read on this forum that memo fields can be a spot of bother. I only saw this after I put several memo data types in my tables. I wasn't going to do that much with them; it was intended as a electronic scratch pad of sorts for the user. Where does the trouble arise from with them? Should I just leave them or should I change them to text fields with 255 chars and hope that's good enough?
scratch
View 1 Replies
View Related
Dec 14, 2006
is there a way that I can compare the ID number from a combo box selection and a table... in order to avoid duplicating that same entry?
ie
If me.cbo1.column(0) = [table name].[field] then
me.cbo1.column(0) and me.cbo1.column(1), etc... = vbWhite
End if
What I am ultimatly trying to accomplish is a way to get rid of one selection in a combo box after it has been selected once and used for another record. is the .requery a better method... if it is, can someone please elaborate on how to use that a little more.
View 2 Replies
View Related
Mar 4, 2008
Hello All,
I'm trying to limit the data entered into a specifc field, but also the data must be unique with respect to other fields.
i.e.
Two fields: System A & System B.
Data entered into System A, can not be entered into System B.
Anyway to prevent this from recurring?
Would I use a validation rule?
Thanks in advance
View 4 Replies
View Related
Sep 12, 2007
Hi
I have a customer database and would like to merge anyone who has the same
phone number or mobile number.
The table is
First name Last name Phone Mobile Email
John Smith 123
Mary Smith 456 123
So I want to find these Mr&Mrs Smith because John phone number is the same
as Mary's mobile
Can you help??
View 2 Replies
View Related
Dec 25, 2014
I currently assign multiple 'skills' to departments using a listbox and a combo using the following code
Code:
Private Sub cmdSaveReq_Click()
Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
[Code]....
However as time goes on we may want to add new skills to the depts - ive used dcount to check if the data exists in one field before however how would it be possible to check to see if the data combination exists over deptid and skill
View 3 Replies
View Related
Nov 12, 2012
I'm having some issues with stopping duplicate entries in a table and have tried a copule different ways with no avail.
In one table in particular, I have a table holding employees as followed;
EmployeeID: PK
FirstName
MInitial
LastName
Company:FK
DepartmentFK
What I am trying to do is stop the ability to duplicating records over a series of fields (FirstName, MInitial, LastName, Company)rather than just one specific field.
I attempted to index the table as followed;
EmployeeID: PK/Unique
FirstName: Unique
MInitial: Unique
LastName: Unique
Company: Unique
The problem with this is it wont allow me to enter more than one person with the same first, middle, or last name or company.
I tried "DCount" Code in a form with no luck, that code reads as followed:
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If DCount("*", "tblEmployees", "[firstname]=" & Chr(34) & Me!firstname & Chr(34) & _
" And [MInitial]=" & Chr(34) & Me!MInitial & Chr(34) & _
" And [LastName]=" & Chr(34) & Me!LastName & "#") > 0 Then
Msgbox "Person already exists."
Cancel = True
End If
End Sub
Note: this code was initially used on a different database I was working on that accounted for an individuals First Name, Last Name, and Date of Birth which worked great. I am just trying to apply that similar concept to this new database with first name, middle initial, last name, and company; which will be a foreign key.
View 10 Replies
View Related
