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.
View RepliesWe 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....
=IIF(newEMPID=dlookup([UPDATED HEADCOUNT]![EMPID]), newEMPID, "Error, EMPID already exists")
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.
Thanks:)
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.
Regards
Henry
: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
End If
Set rsc = Nothing
End Sub
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.
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:
Hi,
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:
User : user ID, User Details
Product: Product ID, Barcode, Description, Cost Price, Sell Price
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.
many thanks, Lol :D
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 ?
Thanks
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
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
hello,
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.
Any guidance?
Thank you in advance
Niri
Hi,
I have a table containing 100s of records. One of its field is Entry_Date. There may be more than one record entered in same date.
Example:
Entry_Date Item
24/09/2005 abc
24/09/2005 pqr
25/09/2005 xyz
24/09/2005 lmn
and so on..
I created a combo on a form based on a query to get all the data from the above table. When I click the combo it shows me all the records. But I need my combo not to show duplicate records.
Example : The combo box should show the records as follows:
24/09/2005
25/09/2005
I do not need other fields in the combo except date field. I have some further actions to take based upon the date I selected in the combo. I dont find 'Hide duplicate' option in its property.
How could I do that?
With kind regards,
Ashfaque
I need help creating a report that summarizes duplicated values.
I need to write a query that groups payments of equal amounts (duplicate values only) for each vendor together. I need to show a group header containing the vendor number and name.
I was using the Having Count(*) > 1 function but that only tells me which values are duplicates It doesn't display them. I need to display the duplicates.
-WM
is there a way to stop duplicate values appearing in a query result.
i querying a table but i dont want to see the same value twice ca i prevent this?
Hey all,
I have a table with account numbers, but need to find duplicate matches only, can't get it going.
AccountRef
Total: Group By
Criteria HAVING COUNT AccountRef >1
Thanks.
I need to make a query that will return all records that have the same field (ex. street address)
how can i do this?
for instance
1|123 street
2|123 street
3|444 block
4|444 block
5|839 town
the query should return:
1|123 street
2|123 street
3|444 block
4|444 block
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?
I'm having a problem and I just wondered if anyone could help,
I have a query that returns about 9000 records, and one of the fields in each record is for a telephone number. There are no complete duplicates of each record but if a record has a duplicate telephone number field I need it to be removed. Does anyone have any ideas?
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.
I have a table that has mutliple records for the same person. I know how to create a query to remove duplicates but i don't know how to create a table to remove duplicates but keep the most recent record. Is this possible?
ie
client status date
123 A 1/1/07
123 C 2/5/06
123 A 9/3/07
results
client status date
123 A 9/3/07