Find Duplicates That Don't Match Single Field

Jun 5, 2007

Hello all,

I am sure this must be simple, but I can't get my head around it.

I have a query that looks at client bank account numbers, and finds duplicates.

There are a LOT of duplicates, because we have several accounts for the same person.

What I would like to do is find all the records where the account number and sort code match, but where the account NAME is different.

I am trying to find any records where the same account number is being used on more than one account.

All the fields are in a single table.

This is what I have so far, which just finds duplicate sort codes and account numbers;
SELECT CPL_ACCOUNT.ACCOUNT_NUMBER, CPL_ACCOUNT.SORT_CODE, CPL_ACCOUNT.BANK_NAME
FROM CPL_ACCOUNT
WHERE (((CPL_ACCOUNT.ACCOUNT_NUMBER) In (SELECT [ACCOUNT_NUMBER] FROM [CPL_ACCOUNT] As Tmp GROUP BY [ACCOUNT_NUMBER],[SORT_CODE] HAVING Count(*)>1 And [SORT_CODE] = [CPL_ACCOUNT].[SORT_CODE])))
ORDER BY CPL_ACCOUNT.ACCOUNT_NUMBER, CPL_ACCOUNT.SORT_CODE;


Thank you!

View Replies


ADVERTISEMENT

Queries :: Update A Field If Find A Match In Another Table

Jun 29, 2015

I have a table Billing_Temp that I need one field updated if I find a match in another table Random_Temp. I runt the query and it prompts for "Enter parameter value: Random_Temp.peopleID... what could be going on? Both tables have a field called peopleID and always Billing_temp has many more records than Random_temp:

UPDATE Billing_Temp SET Billing_Temp.audited = -1
WHERE (([Billing_Temp].[peopleID]=[Random_Temp].[peopleID]));

View 1 Replies View Related

Find Duplicates And Create Table With Dates Of Duplicates

Feb 12, 2008

Hi this is my first post... so hi all :)

ok what i have is a table with contact details 900k plus

there are about 90k of which are duplicates.

this is the basic feilds that are important in this case.

Id, data_source, data_recived, data_code,

what i want is to have a table with unique records (no dups in data_code)

this table will look like this...

Id, data_code, Num_dups, dup1_source, dup1_date, daysbtw_Dup1_dup2, dup2_source, dup2_date, daysbtw_Dup2_dup3 ,dup3_source, dup3_date, daysbtw_Dup3_dup4 ,dup4_source, dup4_date,

I know there is no more than 4 dups of each record.

what i want from this is a table that will give me a record of how many dups for each record then all the dates that they were added and the date between each record entry.

if anyone can help it would be great .

thanks in advance.

View 6 Replies View Related

Find Duplicates Different Than Duplicates Deleted

Dec 8, 2005

Hey, all! Thanks for helping, here is my situation.

I have a table with about 70,000 records that have duplicate Address field values. The rest of the field values for those records are different. When I do a find duplicate querry I get the result that 17,000 records have the same address. However, when I do the append qurrey as instructed here: http://support.microsoft.com/?kbid=209183 I get a total of only 600 records in the new table. I have tried deleting all of the indexes for both the new and old table, with no luck.

I'm using Access 2000 on XP Pro.

If anyone could help with this I would greatly appreciate it!

Thanks

Will

View 14 Replies View Related

Match And Find Missing Numbers

Jul 15, 2012

I need to write a query to find and match all numbers recorded against a list of numbers (1 - 200) and to identify those missing or not matching.

View 1 Replies View Related

Modules & VBA :: Textbox Value To Loop Through Table And Find Match

Jun 26, 2015

I have an MS Access 2010 db that has a main form called switchboard. This has 4 command buttons that open diffrent forms. Also on the main switchboard form i have an unbound textbox called TxtUserName that captures the users environ"username" when the switchboard form is opened.

I have a table called "tblAccessUsers" that i manually enter who i want to use my db. This table will have up to 50 names added to it. Their is only one field name in this table and it is "User Login".

When the user hits any of the commandbuttons on the main switchboard form i need some code that will look at the value in TxtUserName and loop through tblAccessUsers for an exact match. If it finds a match then it will carry out the open form command or if not prompt the user with a message box.

My knowledge of Access and especially VB is quite limited. I managed to create this using a DLookup but that only returns the first record in the table. The logic works but it will not look past the first record.

View 3 Replies View Related

Find Exact Match In Access Table Column

Oct 12, 2013

How to find an exact match in a Access DB table using Sql Query in VB6 ?I know that "Like" keyword will give out all those rows which contain the search-for-string. But I want exact match.

View 4 Replies View Related

General :: Find Records That Match Criteria Of Multiple Fields

Mar 12, 2013

Using sql or access query I would like to create an expression that aggregates the first field and I would like to see all records grouped by the relationship with another field. Let me show an example.

My query shows:

field1 field2
apple a
apple b
banana a
carrot a
carrot b
dog b
elephant b

I would like my query to now display a third field and group field :

field1 field3
apple both
banana a
carrot both
dog b
elephant b

View 1 Replies View Related

Find Duplicates

Mar 4, 2008

Hi!
I used the find duplicates from the query wizard and it is not finding all of the duplicates. How can I fix this?

I have only a few fields
Last name, First name, address, parent name, email.

But when I ran the query the first time it found some.

Then I went into the table and I saw several that it did not find.

I don't get it.

View 5 Replies View Related

Possible To Remove Duplicates From A Query Based On Values In Single Column?

May 14, 2015

I have a query that displays this

Column1 Column 2 Column 3 Column 4
TripTitleA TravelerA N/A N/A
TripTItleB TravelerB N/A N/A
TripTitleC TravelerC TravelerD TravelerE
TripTitleC TravelerD TravelerC TravelerE
TripTItleC TravelerE TravelerC TravelerD
TripTitleD TravelerF N/A N/A

I want to filter Column1 to have no duplicates so it looks like this

Column1 Column 2 Column 3 Column 4
TripTitleA TravelerA N/A N/A
TripTItleB TravelerB N/A N/A
TripTitleC TravelerC TravelerD TravelerE
TripTitleD TravelerF N/A N/A

Is this possible?

View 1 Replies View Related

Find Duplicates - Sort Of

Nov 9, 2006

say i have a table called tblEmployees and i want to find people who have the same name but different employee numbers, can that be done with a query or will that require VBA code? i won't know the names ahead of time, i don't know who has the same name.

for example, columns:
first_name, last_name, employee_num, phone_num, office_num

and i want it to return James Smith with employee_num = 1234 and James Smith with employee_num = 9876.

View 5 Replies View Related

Find Duplicates, Sum And Recombine!?!?!

Jan 29, 2007

Hi everyone,
I have a query as below:

Row1 Row2 Row3 Row 4 Row 5
1, 2, 2 , 0 , 15
1 , 2 , 2 , 1 , 7
0 , 1 , 1 , 4 , 2
1 , 2 , 2 , 0 , 29


I need to pull our records which have the same entries in Rows 1-4 and then add up Row 5. For example The above query would make three more queries i.e
One
1,2,2,0, 44

Two
1,2,2,1, 7

Three
0,1,1,4, 2

I would then wish to recombine them into one table
Final
1,2,2,0, 44
1,2,2,1 , 7
0,1,1,4, 2.

Is this possible and how would I go about doing this!?
Been scratching my head for awhile now!
thanks eveyrone!
Sue

View 2 Replies View Related

How Do I Run A Query To Find Duplicates??

Jun 6, 2007

hi all, im trying to make a query so that when someone updates a field in a form it searches a table (Companytable) which is a list of company names. If it exists i want it to then display a msgbox " already exists" sort of thing! trying to do this in the criteria field on the query. anyone have any ideas or useful resources, ive not done this for a while and cant remember much of IF THEN sort of stuff. mainly not sure if im barking up the right tree, i can search dependent on a set value but not sure how to do it dependant on whats been typed into a form..any pointers would be appreciated! thanks! James

View 2 Replies View Related

Query To Find Duplicates

Jun 15, 2007

Hi,

Our Contacts database has around 4000 individual contacts (and growing), which have been pulled together from four or five seperately maintained databases. As such there are bound to be duplicate records.

What would be a good way of querying the database to find duplicate entries (i.e. entries where the first name and surname are the same)? Short of putting everything in alphabetical order and manually scanning them to see if there are two names the same next to each other I don't know of a way to do it. Also, the manually-scanning method won't work if the names are spelled a little differently (Davies and Davis).

Any ideas?

View 1 Replies View Related

Find Duplicates Query

Jan 4, 2008

I know I have duplicate records in a table and therefore I used the "Find duplicates Query Wizard" to identify the duplicates.

I am positive - there are duplicates and the query does not perform the way it should be.

Any suggestion will be appreciated.

TKS
JLA

View 9 Replies View Related

Error In Code To Find Duplicates

Sep 29, 2005

Greetings all,

I have the code below which is giving me grief as I have copied it from a previous usage and it worked fine. I have tried it in a beforeupdate and now as a seperate button on the form and it gives me the same error message; I have tried to put a save command in to save the record without any luck

the code is

If DCount("[Batch]", "tbl_Data_Wine_Batch", "[Batch]=" & Me.Batch) > 0 Then
Cancel = True
End If


The error message is
Run time error 2001
you cancelled the previous operation.

The problem comes up in the dcount expression.

Any help would be great as it is an important part of the project and we can move on once we have overcome this

rbinder

View 1 Replies View Related

General :: Preventing Duplicates - Can't Find A Table

Aug 15, 2013

Access 2007 - I am using the oft used, frequently posted and shard code from srfreemen - modified as below. now I know I sort of broke commandment # 8 - Thou shalt not copy and paste other people's code without at least attempting to understand what it does. but I honestly did try to understand what it does.

I keep getting runtime error 3078 - cannot find the input table or query 'tblEnrolment_Committee_Master'. It is the name of a table in my database but I am missing the very basic element of how to get my form (which draws all of it's fields from that table) to look it up or identify it.

Likely because of a silly basic error. Normally - I wouldn't attempt such things but my work needs me to ensure no duplicates exist in this case AND it's already a primary key - but I don't want to wait until the whole form is filled out for it to identify the duplicate!

How to get the tblEnrolment_Committee_Master to be included in the search and erase this error ...

Private Sub Entitlement_File_Number_BeforeUpdate(Cancel As Integer)
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset
Set rsc = Me.RecordsetClone

[Code] .....

View 3 Replies View Related

Modules & VBA :: Find Duplicates Of Values In Array

Oct 4, 2014

I have to deal with string arrays that store text. I need info on copying, comparing, appending arrays. Also on passing arrays as parameters to subs or functions. Where I can get to this info quickly without having to browse through many screens.

In addition to this I have some questions:

I have to find the duplicates of values in an array. Here is the code that I use.

Code:
Sub FindDuplicates()
Dim I As Integer, J As Integer, IEND As Integer, text() As String
ReDim text(IEND)
For I = 1 To IEND - 1
For J = I + 1 To IEND
If text(I) = text(J) Then text(J) = ""
Next J
Next I
End Sub

It works but is not performing well. Are there more efficient ways of doing this?

View 14 Replies View Related

Queries :: Amend SQL For Find Duplicates Query

Oct 23, 2014

I have a find duplicates query with the following SQL:

Code:
SELECT tblData.Vendor, tblData.[Loccurramount EUE], tblData.Last4, tblData.ID, tblData.Line, tblData.CoCd, tblData.[Document record number], tblData.PurchDoc, tblData.Reference, tblData.Curr, tblData.[Entry dte], tblData.Status, tblData.Version, tblData.Outcome
FROM tblData
WHERE (((tblData.Vendor) In (SELECT [Vendor] FROM [tblData] As Tmp GROUP BY [Vendor],[Loccurramount EUE],[Last4] HAVING Count(*)>1 And [Loccurramount EUE] = [tblData].[Loccurramount EUE] And [Last4] = [tblData].[Last4])))
ORDER BY tblData.Vendor, tblData.[Loccurramount EUE], tblData.Last4;

This works fine however I want to add another clause to the WHERE and I'm not sure how. At the moment the query highlights duplicates where the Vendor, Loccurramount EUE and Last4 match. I want to further restrict it so that it only finds duplicates where the Vendor, Loccurramount EUE and Last4 match BUT the number shown in version Does Not Match

So if two records have the same details for Vendor, Loccurramount EUE and Last4 and also have the same Version number then they don't show in the result.

View 3 Replies View Related

Writing Query To Find Duplicates Across Tables?

Apr 24, 2013

Employees submit information into a form which translates the information into a table. The table has been in use for years. By accident some employees were writing to an archived table while others were writing the the active table. This resulted in a field called "WorkID" being duplicated across the two tables.

Bottom line I am trying to write a query finding duplicates across tables but basing the duplicate only on certain columns.

View 5 Replies View Related

Find Duplicates Query Problems In Access 2007

May 1, 2008

I get a very strange error when trying to create a find duplicates query. When i try to select a table that has duplicate values it brings up a message box and it says that i must pick a table that has at least one field that's not a memo or OLE data type. I look at my table and I have niether of those in it...pretty much just text and number fields. Does anyone know what's going on?

View 3 Replies View Related

Queries :: Wizard To Create A Find Duplicates Query

May 29, 2014

I used the wizard to create a find duplicates query. I know for a fact that 15 records are duplicates. I'm getting over 300 records. When I dumped the data into Excel and did a pivot table to count the number of records by account number only 15 had a total count of 2. All others only had 1. Why are the ones with no duplicates showing? I just want to see the 15.

View 2 Replies View Related

Queries :: Find Duplicates - Resultant Datasheet Won't Close

Apr 24, 2013

I have a Find Duplicates query that I created with the Query Wizard.

IF I run the query and find duplicates, I can close the resultant datasheet with the close button.

IF I run the query and find duplicates and delete them, I can close the resultant datasheet with the close button.

IF I run the query after I have found and deleted duplicates and closed the window, I can close the resultant datasheet (with no records) with the close button.

BUT IF I run the query when there are no duplicates, it opens the datasheet with no records, but you can't close it with the close button. You have to exit the database completely.

I don't know SQL or VB. Have to do this in Access.

I thought about creating an If statement in a macro, but am not sure of the syntax, but this would be the basic statement I would make:

IF Find Duplicates query returns no records,
THEN Cancel Event.
End IF

View 2 Replies View Related

Queries :: Find Duplicates From 2 Tables And Remove From 1 Table

Jun 29, 2014

I have a report with 2 access tables (1 Master table and another a daily feed table)

The Master table keeps a log of all incoming records. (once append it to this table, should not show in future reporting)

The Daily feed information within the last 48 hours. (uploaded from an excel report into access temporary table)

When the daily feed table gets completed, I append the records and updated them into the Master to avoid duplication.

When I upload the daily feed table and I match it against the Master table to find duplicates, how can I delete the duplicates from the Daily Feed table?

This is my code to find duplicates:

SELECT CMPreport.ID, CMPreport.MbrName, tblMaster.ID
FROM CMPreport LEFT JOIN tblMaster ON CMPreport.ID = tblMaster.ID
WHERE (((tblMaster.ID) Is Not Null));

View 9 Replies View Related

Find Record + SINGLE FORM

Mar 30, 2006

Is it possible to create text box for search for records if I use SINGLE FORM?
i try but without effect :(

pls see my database (I want to use for record "Title")

Thanks

View 2 Replies View Related

Queries :: Search Many To Many Relationships To Find Single Record

May 22, 2013

I am trying to use a query to search 2 many to many relationships but i keep getting duplicate results because each record is slightly different.

I have a table with cameras, another users and another formats. I have a table linking cameras to users and another linking cameras to formats so it looks somthing like this:

tblCameras - tblFormatlink - tblFormats
tblcameras - tblUserslink - tblUsers

When i add all the tables into a query i get loads of results as expected because i get a result for every possible combination.

What i want is when i search for all cameras that are suitable for a user who is a beginner is to get just a list of unique cameras. When i do that search at the moment i get a duplicate record for each different format the camera can do.

View 4 Replies View Related







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