I am quite frustrated at not being able to solve this problem in a Db I am developing for my wife. :eek: I have searched through a number of solutions to similar thread questions. I tried using "Distinct" in the SQL statement, Grouping using Max, Min, First and Last all without success.
So my db is a simple 2 table set up with a one to many relationship as follows:
Whenever I run a query I am getting duplicated records of Visit information which should happen. However, I would like to have only one of the visits displaying (first or last doesnt matter.) The Visit No is a unique autonumber.
Can anyone help, any assistance would be greatly appreciated.
I've been given a file with 165,000 records that was saved as an .xls file. I'm trying to import it into Access so I can view all of the records. When I try to import the file I get an error that "C:AllFiles.xls" is not in the expected format. I've tried the external data wizard and transferspreadsheet method.
I can open the file in excel and see the first 65,000 records. If I copy them and paste them into a new .xls file, I can import them just fine.
Form A is used for data entry into SampleTable. On this form I have a some fields including these two: ReplicateA, LabBatchCode.
The database contains another table, IndividualSampleTable. This table contains two fields: ReplicateB, LabBatchCodeReplicateB.
When the user enters information into Form A, the data in fields Sample.ReplicateA and Sample.LabBatchCode are used to populate IndividuallSampleTable. But here's the twist: On IndividualSample Table, IndividualSample.ReplicateB needs to be autogenerated (I guess). Then the field IndividualSample.LabBatchCodeReplicateB is comprised of LabBatchCode entered in Form A, concatenated to the IndividualSample.ReplicateB field.
To make it tricker, the number of records added to IndividualSample table is determined by the value supplied on Form A in the field ReplicateA.
So, Form A could look like this: ReplicateA: 3 LabBatchCode: CRBR05151998
IndividualSample table should end up with three records like this: ReplicateB: 1 LabBatchCode: CRBR051519981
ReplicateB: 2 LabBatchCode: CRBR051519982
ReplicateB: 3 LabBatchCode: CRBR051519983
Hope this makes sense! And I appreciate the help!!!!
Greetings all! We have a mentor-mentee database that's been setup with a many-to-many design. We're storing essentially the same data in each table except for the field names. We thought we were good to go but one additional request was made to determine a count of employees participating in the program (regardless of whether mentor or mentee) in each section of the company. We don't want each person counted multiple times if they are both a mentor and a mentee, or if they have >1 mentee or >1. I created a couple of queries to pull unique empIDs from each table to narrow down each table BUT I'm stuck on being able to pull unique id's from both tables.
I first tried the unmatched query wizard from both viewpoints but then I don't get those cases where a mentor is also a mentee. In other attempts, I end up with a cartesian product. I've cruised through the queries section but haven't come up with any examples related to this type of situation.
I need a way to pull both unmatched records from each table while also getting essentially the first occurrence of instances where mentorID=MenteeId. I'm no SQL expert so if someone can think of a statement that would suffice, it would be greatly appreciated.
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.
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.
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.
