Help Finding Duplicate Of Reversed Fields

Feb 14, 2008

Hi

This is my first post and i searched to try and find an answer on the forums but can't seem to find one so thought i'd ask to see if anyone is able to help. Sorry if this has been answered before and i've been a bit thick not been able to find it. anyway here's my question...

I have 2 fields i want to check in my database DOB1 and DOB2 (date of birth of applicant 1 and date of birth of applicant 2.

i've written a query that looks for any records where the data in DOB1 and DOB2 match the data in another record for DOB1 and DOB2. i hope that makes sense. this is what i've written.

In (SELECT [App1 DOB] FROM [Data] As Tmp GROUP BY [App1 DOB],[App2 DOB] HAVING Count(*)>1 And [App2 DOB] = [Data].[App2 DOB])

this seems to work but please feel free to correct me.

Now the part i'm struggling with is trying to find duplicates where the fields have been reversed. eg App1 DOB and App2 DOB are the same as App1 DOB and App2 DOB in another record but applicant 1's Date of birth has been stored as Applicant 2 and Applicant 2 is now stored as Applicant 1. i hope thats not to confusing.

Basically i'm trying to check for duplicate mortgage applications in the database but sometimes applicant 1 and applicant 2 are reversed.

Any help would be greatly appreciated.

Thanks

View Replies


ADVERTISEMENT

Finding Duplicate Records

Jun 19, 2006

Hello

I've had to export data from an old third party database to Access with the task of reporting on the data. I have a client table and work tables, one for each year going back to 1992. In the original database, the clients do not have a date of when they were set up. I need to find out how long clients have been with us, so I thought of going back through the work tables 2006 to 1992 and finding out where they first occurred, by looking for duplicate records in the tables. Is this a simple enough thing to do? The client table and work tables are linked through the field Client.

Thanks

Dave, England

View 4 Replies View Related

Finding Duplicate Entries

Oct 12, 2006

I am running a query to find out duplicate in two fields in one table. Postcode and Address Line 1. Can anyone tell me the formula for only picking up the first 6 charaters in the Address Line 1 field??? I cant remember how to do it!

HELP!

View 2 Replies View Related

Finding Duplicate Data From Two Columns?

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

Finding Duplicate Values From The Table Having No Primary Key

May 18, 2005

Hi All,

I have one table where ID field is autogenerated, and another field orderno which is not primary key. Now it has 2 duplicate values in it so when I try to make field Orderno as primary key, it is not allowing me to do so.

Can any one help me how to find the duplicate values using query.

Thanks in advance,

Jigs

View 1 Replies View Related

Reversed Crosstab Query?

May 15, 2007

G’day all :D

I was wondering if there was a way to setup a Reversed Crosstab query:confused: . I am running a query which is pulling data from an import table. But the one thing is the data that I import is already setup like a Crosstab. Ex:

First Name, Last Name, Employee ID,Value 1,Value 2,Value 3,
Joe, Smith,38689, 4,2,8,
Bob, Smith,38957, 7,3,2,
Matt, Jones,38745, 4,7,7,
Jill, Brown,38467, 6,4,3,
Joan, Miller, 38058, 8,9,6,

I tried to setup multiple queries for each value then tried to pull it all into one query, but then I get all records multiplied 5 times.:eek:

Basicly I need the following results:
First Name,Last Name,Employee ID,Value,Number,
Joe, Smith, 38689, 1,4,
Joe, Smith, 38689, 2,2,
Joe, Smith, 38689, 3,8,
Bob, Smith, 38957, 1,7,
Bob, Smith, 38957, 2,3,
Bob, Smith, 38957, 3,2,
Matt, Jones, 38745, 1,4,
Matt, Jones, 38745, 2,7,
Matt, Jones, 38745, 3,7,
Jill, Brown, 38467, 1,6,
Jill, Brown, 38467, 2,4,
Jill, Brown, 38467, 3,3,

Anyone thing that will help, I will be very grateful,
Thanks in advance

Mur

View 6 Replies View Related

Finding Duplicates Out Of Differents Fields

May 16, 2005

Sorry for the question's wording.

Here is what bugs me:

- I have one table tblRefctc with the followings fields :
LCtcRef,../..,LRefServ0, LrefServ1,..LrefServ16.

Where all those Lref are long; LCtcRef being the primary key. This table describe a contact from different branches (account, etc..) so while each branch has the relevant information about this contact, they know that that branch too is in contact with the same person.

- I have this other table with describes Events, TblIdxEvt (meeting, whatever) :

LRefidx,IdxEvt,IdxCtc

where IdxCtc is filled with LCtcRef once an event is created.

What I must check for is : For the same IdxEvt, should another branch makes an appointement for LCtcRef (i), I must check if one of the LRefServ0 to LRefServ16 are not already added in the event table.

At this time, I am stuck with making functions which test the existence of the LRefServ(i) (i ranging from 0 to 16) in tblIdxEvt, but I wonder if there is a simpler way to do it with SQL (which, as you could guess, Im not good at).

Thanks in advance for any pointers.

View 2 Replies View Related

Queries :: Finding Top Three Values In Six Fields?

Oct 3, 2013

I'm doing a medical db, where I'm trying to calculate ISS

Code:
en.wikipedia.org/wiki/Injury_Severity_Score

There are 6 values, and i have to sum the largest three (squares to be precise). How can I get three highest values from 6 fields in the same record?

View 1 Replies View Related

Tables :: Data Input Writing Reversed?

Jan 27, 2014

I'm running pro bono a linked file database for a wildlife hospital in Australia. I have a Form in which there are several fields for vets to enter data about a wildlife patient. One of these is the Collection Plan (for the patient). This is a memo field from the TBL_Accession. The Collection Plan is written in straight English on the input form, but the data entered into the table is completely reversed, reading "etinuer ot ovra siht pu/p ot ronod". Translation for the line here is "donor to p/up this arvo to reunite".

View 2 Replies View Related

Finding Specific Fields In A Record (sorry, Newbie!)

Sep 5, 2005

Hi everybody,
My question may sound you stupid but i am a newbie and i think you can help me...
My problem is,
I have records that have the id of a stuff and the error codes of the stuff which are occured in production. Therefore not all of the fields in the record are non-zero. I want to find the non-zero field/fields in a record...
Any help will be greatly appreciated...
Thanks...

View 1 Replies View Related

Finding Sequence Of Fields Making A Unique Record

Aug 23, 2006

I have a MS Access 2000 database with 136 data tables in it. What I would like to do is execute a piece of VBA code which will list for me in another table, (for example: Field1: TABLE NAME Field2: FIELD NAME 1 Field2: FIELD NAME2 etc), starting left to right, how many fields would have to be combined in each table to represent a unique record.

For instance:

TYPE ID TEXT
1 1 "Cats"
1 2 "Dogs"
2 1 "Rabbits"

In this example a combination of the fields TYPE and ID give a unique record.

Any help most appreciated.

Dalien51

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

I Need To Match 3 Fields For Duplicate

Sep 26, 2005

I am exporting an Oracle report to excel, and using an Access macro to import into a table.
The records contain some duplicate info that I do want to capture, but I don't want to import the same records. (I import the file on one day, and someone comes in and imports the same file later).
This would work if I could get the table to not duplicate if three of the fields are the same as an existing record. I tried using the index, but it reconizes any/all of the fields that duplicate.
Any help would be appreciated.

View 3 Replies View Related

Querying Duplicate Fields

Jul 14, 2005

Please can anybody help.
I wanted to have a form containing a list of members forenames and surnames in one record. I wanted to be able to input the members reference number and then the forename and surname would be automatically filled in.
I have achieved this through a query based on a table with ten foreign keys for the members. The query uses the members table ten times to join the ten foreign keys.
This works fine and the form runs with space for ten members and I can enter a members reference number in each field and their forenames and surnames are filled in.
The problem is that I wish to run queries with members surnames as criteria to bring out all records containing those members. How can this be done without having to set the criteria in the query in each of the members surname fields.

I know I can do this if I have a separate record for each member in the form
but I really need ten members in each record.
I hope this makes sense and somebody can help.

Thanks in advance
John

View 2 Replies View Related

Check For Duplicate Record By 2 Fields

Mar 12, 2007

dear all

i managed to get checking for duplicate record by ONE field ie member_no

code :

If DCount("*", "runner", "member_no = " & Me.member_no) > 1 Then
MsgBox " This member is already exist!" & vbCrLf
Me.c_memberid.SetFocus
Else
DoCmd.CancelEvent
End If


how i want to get checking duplicate record by TWO fields ie member_no and run_no ?


thanks in advance

View 1 Replies View Related

Duplicate Record And Increment Several Fields

Jul 3, 2006

I have a document database that often deals with multiple copies of a document. Each copy must have a record of its own. With the add new record form I would like the user to be able to add the extra copies automatically by duplicating the first entry "n" times but also incrementing the copy number field by one for each copy. Got the duplicate copy done OK but am stuck on how to increment the copy number. This database forms part of my yearly assessment and is due in a couple of weeks so would welcome some help.

many thanks

View 1 Replies View Related

Tables :: Duplicate Over Multiple Fields

Sep 21, 2012

I have a table that is going to track people. First Name, Last Name, and Date of Birth in three separate fields.I dont want to be able to add the same person in the table. How do i do this when the data is in separate fields.

View 9 Replies View Related

Reports :: Duplicate Fields Not Always Hidden

Feb 22, 2015

I have a report based on a query.

I have this grouped by a jobid.

For each field on the report I have hide duplicates set to yes. for each job id most of the line should be hidden except for a couple of fields (part used and description).

This works in 99% of cases but I occasionally I get the whole line repeated

I've treble checked that the fields are duplicates (they are) and am at a loss what to look for.

View 2 Replies View Related

Queries :: Duplicate Data From Two Different Fields

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

General :: Day / Month In Date Form Control Reversed After Update Of Form

Sep 11, 2014

I have a data entry form which have a date field.This field retains the existing date after the record is saved with vba after update event.

Private Sub Form_AfterUpdate()
Me.ProductionDate.DefaultValue = Chr(35) & Me.ProductionDate.Value & Chr(35)
End Sub

Almost after a year of perfect working i am facing a problem.When i save the record , in date field my date and month values are reversed if date is between 1-12.although my database stores perfectly valid date. for example if date is "10-09-2014" (dd-mm-yyyy) after saving form date field becomes "09-10-2014". but if date is "13-09-2014" The date form field retains this value .

View 2 Replies View Related

Clicked Yes On "Block Unsafe Expressions" - How Is It Reversed?

Aug 25, 2006

OK, it was bound to happen eventually...

New user of Access 2003 has inadvertently clicked "yes" to "Block Unsafe Expressions" and now can't even click a simple command button to print a report. :confused: Db has not a single macro, nothing fancy, mostly wizard code.

Other folks logging onto that PC can still function fine.

I'm assuming it has set him into Sandbox mode & we need to back out of that setting. I've searched a ton of GHudson posts on this forum, read everything Jeff Conrad links to, looked at the following MS articles.

Understand how to enable or how to disable Sandbox mode
http://support.microsoft.com/default.aspx?kbid=294698

Enable or disable sandbox mode
http://office.microsoft.com/en-us/assistance/HP010504321033.aspx

One article issues a dire warning about dangers of setting registry keys and the other doesn't seem to fix the problem at all. We're in a severely locked down environment and cannot download anything or change system settings -heck, can't even run ScanDisk anymore.

Will re-installing Access solve this, or does anyone know of a solution that will work given our limitations?
Any help is appreciated!

View 5 Replies View Related

Duplicate/Multiple Fields In A Table (Recordset?)

Jan 30, 2006

Hi All,

A bit new to this, any help most appreciated.

I'm trying to set up a table which will have multiple fields (a recordset? is that right, if so I don't know how to set one up in access). Its for a skill set which is utilised by specific lines in a production plant.

Each line in the plant has a set of skills required. I have a skills table (SkillID and Skill), what I want to do is have a Skillset which I can then link to each line

So for example Skillset 1 with a SkillsetID will also have in that table skill 1, skill 2, skill 3, but all taken from the Skill table. However that would involve having the SkillID field numerous times in the same table, but this cannot be done?

The idea is that for each line I can link the Line table to the Skillset table and that tells you what skills are needed for that line.

I hope this makes sense.

Thanks all.

View 2 Replies View Related

Preventing Duplicate Data From Multiple Fields

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

Deleting Duplicate Values Based On 2 Fields

Jan 3, 2006

Hi guys,

I have a question I've been trying to figure out with no luck. I have 2 fields, companyname and zip, these 2 fields may have duplicate values or they may be uniquei.e.

companyname zip
billybobs 68135
dilberts 68137
billybobs 68135

what I need to do is delete all of the duplicate records where both companyname and zip match. Is there a way I can do that with a query?

Any help is greatly appreciated

Thanks

James

View 14 Replies View Related

Modules & VBA :: Prevent Duplicate Data Over 2 Fields

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

Storing Data In Two Fields Without Allowing Duplicate Entries

Mar 19, 2005

I need to know if I can store data in more than one field, without allowing Duplicate entries.

Example: We have to send out letters with a claim number on it, sometimes we need multiple claim numbers on a letter, up to about 10 claim #'s is the most I've seen thus far.

We mail merge into Word to create the letters.

However, these claim numbers cannot be duplicated within the database and in order for this to work with the mail merge, I need several fields to store the claim numbers.

Fields are:
ClaimNumber
ClaimNumber2
ClaimNumber3
etc....

To be more specific. I probably have a bad data structure as I was kind of rushed and a little inexperience with putting the database together. It's for work and sometimes management just doesn't want to wait, they are so negative.

In brief, most everything is in 1 table and I know from reading this forum that I shouldn't do that, but that's another story.

Further, a claim# is tied together by the same claimant, the relationship code is what separates the claimant with the family, the insured id# is used for all claimants within the family.

You can also tie the claim# to a provider id#, some providers such as clinics and large hospitals have multiple providers that use the same provider id#, so a suffix code is used to separate the providers within that same #.

Please feel free to ask additional questions and I will give you the additional information you need to help me. Thanks again.

P.S. - Last year I created the database and this year I am improving it, so expect more posts from me in the future. I really appreciate all the help I get from everyone here!!!!

View 9 Replies View Related







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