Im having a problem with controling duplicate records in a table
i want to allow duplicates in all feilds individually, but not if 3 feilds are the same in more than one record. (ei. if the entries in a1,b1,c1 are equal to entries in a2,b2,c2; prevent the second record from being entered)
so far, i've writen a macro to identify duplicates in an existing table, but i dont know how to delete records, or access values being entered in a form
Is their a way to highlight a feild. I am using the duplicate function but after the record is duplicated their are certain feilds that have to be changed, how can i highlight them somehow. ANy ideas??? The feild is Price???
The situation I am in at the moment is that I have a database for a company who does work at different building sites. I have created a database so that it has the date the work was completed. With this company in particular the last possible dates of payment are 30 or 60 days after the work was completed, depending on the work. I would like to know how I would be able to create a calculated field that would display the first day that the payment is late.
I have have 2 feilds in my database, there can be the same value in one feild but not both.....for example say NAME and ADDRESS i can have the same NAME for more then one entery and i can have the same ADDRESS for more then one entery but i cant have the same NAME And the same ADDRESS....anyone know how to do this? :confused:
Hi, I'm trying to create a table which needs some feilds to be automatically filled up when the control goes to it. Say my table has feilds, "size" and "result". In each row, the result cell should be filled up corresponding to the size value of that row. This needs to be looked up from an existing table.
I have been struggling with this and would appreciate any help. It sounds so simple!!! I have 4 fields (Unit Price 1, Unit Price 2, Unit Price 3, Unit Price 4) in a Orders table that I want to add together and store in a 5th field (Sub Total) in the same table using a form. I am having problems with the sum and I am unsure how to get it to show in the Sub Total box in the form as well as the table. Thanks for your help!!
We are working on an Access (2007) database that is on a SharePoint Site (2007).
Currently the form is operational, but there is one last thing that would be nice to have.
The table is "Updated Headcount" which contains "EMP_ID" which are unique numbers stored as text.
In the event a new employee is entered in the system by another user on this site we would like to prevent any duplicate "EMPID"s from being entered and saved on the SharePoint, we would also like to alert the user and prevent the data from being saved.
All data is currently bound, so once the user makes a change it is made, no submit button is required.
We are running into some difficulties in doing a dlookup from the value entered and comparing to a column in the table.
TABLE - UPDATED HEADCOUNT COLUMN in UPDATED HEADCOUNT - EMPID FORM CELL user will input an EMPID - newEMPID FORM CELL used for a dlookup to compare what user has entered to what is already in the table - duplicateEMPID
So below is what we are trying to do, we are sure there are a few commands missing....
I have a table with duplicate rows. How do I de-duplicate the table so that there are no duplicates and find out how many duplicates there were in the table.
Please i am trying to create a form that will keep records of perspnnel but i want a situation whereby if someone enters a last name and first name that is already in the record, the database should send a message telling the user that such a name exists.
:confused: I have got to create a car hire db for a course i'm doing. I have a vehicle table I have a customer table I have to be able to book more than one vehicle to a customer. I tried putting vehicleID into my appointments table twice but that is wrong. Any solution would be great.
Hi All, I have a table that has a device column and a tag # column, I want to pull out a report that shows duplicate tag # for different devices, the tag # can only be assigned to one device, I received an excel sheet that has to be fixed, I have the sheet in an access table. Please help. For example: device 123 and device 345 have tag #abc. I can’t simply create a query to show duplicate for tag# because I can have duplicate # only if it’s the same device.
I have a form with a bunch of fields. Now their are three im concered with. CFR_EHPID,CFR_SVCDATE,CFR_PROVTAXID. If thier is a vaule in the database when the user tries to enter it again, i want a dup message to show...... SO i dont want any duplicates for those three feilds. is their a way when the user is done keying and tries to move to the next record it displays that thier is a duplicate??????. below is the code i use but its for the before update event
Dim SID As String Dim stLinkCriteria As String Dim rsc As DAO.Recordset Set rsc = Me.RecordsetClone SID = Me.CFR_EHPID.Value stLinkCriteria = "[CFR_EHPID]=" & "'" & SID & "'" 'Check StudentDetails table for duplicate StudentNumber If DCount("CFR_EHPID", "CFR", stLinkCriteria) > 0 Then
'Undo duplicate entry
'Message box warning of duplication MsgBox "Warning Student Number " _ & SID & " has already been entered." _ & vbCr & vbCr & "You will now been taken to the record.", vbInformation _ , "Duplicate Information" 'Go to record of original Student Number
I currently have a table that had 30 fields, the unique fields are survey, date, and business name. I'd like to create a query that will display all of these records so that i can get rid of them. They were imported from another table that another user created. If survey, date, and business name are identical i'd like to get rid of them.
i have a table with 3 fields: ID, line_item, and pay_app.
line_item and pay_app are linked to other tables in a one-to-many.
For every pay_app, there can be up to 100 line_items. However, I only want each line item to appear once (at most) in each pay_app. I want to prevent the same line item from showing up multiple times in the same pay_app. Essentially, I want to prevent duplicate entries, but with a stipulation, if that makes sense.
So this is a very basic question, listen up: I have only one table in my database. The table contains several text fields ( about 8 ). The table is filled with very very much data. The problem is that i have a multiple instances of the same data ( i mean apsolutly same, all the fields are same ) so i need to delete copies. Help please. Those copies are making me mad :mad:
I need to make a database which wont allow any double bookings.
I've got all my bookings in a query and when a new date is entered (via a new bookings form) I need to make sure that date entered doesn't already exist in the query. I have tried a DLookup but it didn't work, however that is more than likely to be a user error, heh.
I've jumped in at the deep end doing this database. I've only done a few step by step databases before hand and the Access Help has sometimes helped my problems but I just can't find the solution on my own for this, I need somebody elses help.
Thanks a lot
Forg
EDIT: sorry if this is in the wrong area, I couldnt think where to put it
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
Hi, doing my project at college, decided to do a EPOS simulator for my sisters business based around barcodes etc and tied to a database. i've just knocked up something after normalising my data to prototype it to see if it is viable. I'm having problems with duplicate field entries. My db consists of essentially 4 tables at this point:
Transaction: Transaction Number, Date, User ID, Transaction Total
Transaction/Product - a compound key which is to manage the many to many that exists between Product and Transaction tables.
I am wanting to allow multiple entries for the same item, i.e. multiple scans, not a single sacn followed by a quantity. I keep getting an error message though that syas I am duplicating fields. I have tried altering the Index property for the fields in the Product/Transaction table but htis has had little effect.
Any thoughts please as to how to do this? I have attached the db for examination.
I have a many to many relationship, and one of my tables (the junction table) has only 2 fields (BookID & AuthorID). These fields together are set as primary key, in order to avoid duplicate records. Eventually, if i will try to put a duplicate record, i will get Access error's message "The changes you requested to the table were not successful because they would create duplicate values in the index, primarykey or relationship ..."
What is the best approach in order to check whether a duplicate record is going to be created, and if yes, i will get a customised error message rather than the above mentioned default error message ?
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?
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?
In excel i used the following formula =IF(COUNTIF(U$2:U$900,U3)>1,"Duplicate","")on one field to find duplicate, but I cannot come up with similar formula on column fields in a table.
