Duplicate Recs Problem(could End In Divorce)

May 22, 2005

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:

tblPatient Details (RecNo (PK), Surname, FirstName, DOB, Postcode)

tblVisit Details (VisitNo(PK), VisitDate, RecNo(FK), Location, Category, Diagnosis, Drug1, Drug2, etc)

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.

View Replies


ADVERTISEMENT

Import Large Xls (165K Recs)

Mar 1, 2006

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.

How can I get to the other 100,000 records?

Thanks,

Sup

View 2 Replies View Related

Insert Recs Based On A Given Number:

Feb 25, 2005

Here's what I'm trying to do:

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!!!!

View 1 Replies View Related

Pull Unique Recs From Many-to-Many Related Tables

May 13, 2005

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.

View 2 Replies View Related

Forms :: Prevent Duplicate Values With Alert After User Enters Duplicate Value

Dec 30, 2013

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....

=IIF(newEMPID=dlookup([UPDATED HEADCOUNT]![EMPID]), newEMPID, "Error, EMPID already exists")

View 11 Replies View Related

De-duplicate??

Mar 11, 2007

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:)

View 2 Replies View Related

Duplicate Last Name And First Name

Feb 23, 2008

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

View 3 Replies View Related

Help On Duplicate ID Please

Mar 19, 2006

: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.

View 3 Replies View Related

Duplicate Tag #

Mar 19, 2008

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.

View 4 Replies View Related

Duplicate

Feb 22, 2005

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

View 9 Replies View Related

Duplicate

Sep 13, 2007

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 1 Replies View Related

Duplicate Records

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

Duplicate Posts

Jul 11, 2005

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.

View 1 Replies View Related

A Little Duplicate Problem...

Dec 2, 2005

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:

View 4 Replies View Related

Duplicate Validation

Feb 22, 2006

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

View 2 Replies View Related

Duplicate Data

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

Help With Duplicate Fields Please!

Dec 9, 2004

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

View 5 Replies View Related

Duplicate Values

Apr 19, 2005

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

View 4 Replies View Related

Trying To Duplicate Data

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

Duplicate Records

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

Duplicate Identifier

Mar 5, 2008

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

View 1 Replies View Related

No Duplicate In Combo

Oct 9, 2005

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

View 2 Replies View Related

Duplicate Values (Please Help)

May 18, 2006

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

View 2 Replies View Related

Duplicate Values

Jul 6, 2006

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?

View 1 Replies View Related

Duplicate Accounts

Sep 15, 2006

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.

View 1 Replies View Related

Duplicate's Query

Oct 24, 2006

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

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved