Microsoft Access duplicate query

  • Thread starter Thread starter billmuir
  • Start date Start date
B

billmuir

My table has four fields, I want the records were the first 3 fields
match and the fourth field does not.

Thks
 
Can I suggest you expand a little on what you are trying to do. What you have
asked has no real explanation. It would help people to assist you if youe
describe more fully what you are trying to do.
 
Is there a reason you aren't doing this in a spreadsheet?

Are you saying you want to know which records have values like:

ColumnA = ColumnB = ColumnC <> ColumnD

You could try a query to get those, but without more information about what,
precisely, is being stored in those fields, this is only a wild hunch...

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
My table has four fields, I want the records were the first 3 fields
match and the fourth field does not.

Thks
Click to expand...

match... what? Each other?

If so, and assuming that your fields are named Field1, Field2, Field3 and
Field4 (you don't say), try this SQL:

SELECT * FROM mytable
WHERE Field1 = Field2 AND Field1 = Field3
AND Field1 <> Field4;

The need to do this query strongly suggests a non-normalized table design but
that's another issue.
 
Can I suggest you expand a little on what you are trying to do. What you have
asked has no real explanation. It would help people to assist you if youe
describe more fully what you are trying to do.
--
Advice to Posters.
Check your post for replies or request for more information.
Consider  providing some feed back to the response you have recieved.
Kindest Regards Mike B






- Show quoted text -
Click to expand...

Thks for responding. I'm doing a duplicate query, as an example of a
customer table, I want to find all the John Doe born in 1960 who live
in a different state. So field1, field2 and field3 match, field4 does
not.
thks.
 
Thks for responding. I'm doing a duplicate query, as an example of a
customer table, I want to find all the John Doe born in 1960 who live
in a different state. So field1, field2 and field3 match, field4 does
not.
Click to expand...

A "Self Join" query will work here. Create a new Query; add your table to the
query grid twice (Access will alias the second instance by appending _1 to the
name). Join Field1 to Field1, Field2 to Field2, Field3 to Field3. Include both
tables' Field4.

As a criterion on table_1.field4 put
table.field4
Click to expand...

Don't use <> or it will find each mismatch twice.
 
Is there a reason you aren't doing this in a spreadsheet?

Are you saying you want to know which records have values like:

    ColumnA = ColumnB = ColumnC <> ColumnD

You could try a query to get those, but without more information about what,
precisely, is being stored in those fields, this is only a wild hunch...

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP







- Show quoted text -
Click to expand...

Lets say I have a table with 4 fields: name, address, phone, age. when
a do a duplicate query I want to see those persons who's name are the
same, live at the same address, have the same phone, but have a
different age. I'm loking for father and sons living in the same
house. I hope this helps. Thank you so very much. Bill
 
Try this query.

SELECT A.*
FROM TableName as A INNER JOIN TableName as B
On A.Name = B.Name
AND A.Address = B.Address
AND A.Phone = B.Phone
WHERE A.Age <> B.Age
ORDER BY A.Name, A.Address, A.Phone, A.Age

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Try this query.

SELECT A.*
FROM TableName as A INNER JOIN TableName as B
On A.Name = B.Name
AND A.Address = B.Address
AND A.Phone = B.Phone
WHERE A.Age <> B.Age
ORDER BY A.Name, A.Address, A.Phone, A.Age

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County






- Show quoted text -
Click to expand...

Let me add a sample:
First - Middle - Last
Al - R - Johnson
Bob - M - Doe
Fred - J - Smith
Bob - W - Doe
Marc - - Lewis
Al - R - Johnson

I would like a query of those with the same first & last, but a
different middle, so the query will only show:
Bob - M - Doe
Bob - W - Doe

Thank you very much for all your help.
Bill
 
I think that the following should work.

SELECT A.First, A.Middle, A.Last
FROM [YourTable] as A INNER JOIN [YourTable] As B
ON A.First = B.First
AND A.Last = B.Last
WHERE A.Middle <> B.Middle

If this fails, you might post back and explain what results you got and
why they are different from what you want.

For instance, if one name had a null value for Middle then you would not
get the results you wanted. However, that could be rectified by
changing the WHERE clause to

Where A.Middle <. B.Middle
OR A.Middle is Null and B.Middle is Not Null
OR A.Middle is Not Null and B.Middle is Null

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
I think that the following should work.

SELECT A.First, A.Middle, A.Last
FROM [YourTable] as A INNER JOIN [YourTable] As B
ON A.First = B.First
AND A.Last = B.Last
WHERE A.Middle <> B.Middle

If this fails, you might post back and explain what results you got and
why they are different from what you want.

For instance, if one name had a null value for Middle then you would not
get the results you wanted.  However, that could be rectified by
changing the WHERE clause to

Where A.Middle <. B.Middle
OR A.Middle is Null and B.Middle is Not Null
OR A.Middle is Not Null and B.Middle is Null

'====================================================
  John Spencer
  Access MVP 2002-2005, 2007-2009
  The Hilltop Institute
  University of Maryland Baltimore County
'====================================================




Let me add a sample:
First - Middle - Last
Al    -     R     - Johnson
Bob  -     M   -  Doe
Fred  -    J     - Smith
Bob  -   W    -  Doe
Marc -          -  Lewis
Al     -    R    -  Johnson
Click to expand...
I would like a query of those with the same first & last, but a
different middle, so the query will only show:
Bob - M - Doe
Bob - W - Doe
Click to expand...
Thank you very much for all your help.
Bill- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

John, your solution worked perfectly on our test table, thks. We are
looking to expand our Access & SQL knowledge, what options could you
recommend? Again thank you very much.
Bill
 
SQL Queries for Mere Mortals is a book I liked to help me learn more about SQL
queries.

As far as books on Access, my best suggestion is go to a book store and look
through the books they have on the subject. Use the index and try to locate a
section that helps you solve a current problem. See if you are comfortable
with understanding what is there.

One of my best resources for learning was (and still is) these newsgroups.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I think that the following should work.

SELECT A.First, A.Middle, A.Last
FROM [YourTable] as A INNER JOIN [YourTable] As B
ON A.First = B.First
AND A.Last = B.Last
WHERE A.Middle <> B.Middle

If this fails, you might post back and explain what results you got and
why they are different from what you want.

For instance, if one name had a null value for Middle then you would not
get the results you wanted. However, that could be rectified by
changing the WHERE clause to

Where A.Middle <. B.Middle
OR A.Middle is Null and B.Middle is Not Null
OR A.Middle is Not Null and B.Middle is Null

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================




Let me add a sample:
First - Middle - Last
Al - R - Johnson
Bob - M - Doe
Fred - J - Smith
Bob - W - Doe
Marc - - Lewis
Al - R - Johnson
I would like a query of those with the same first & last, but a
different middle, so the query will only show:
Bob - M - Doe
Bob - W - Doe
Thank you very much for all your help.
Bill- Hide quoted text -
Click to expand...
- Show quoted text -
Click to expand...

John, your solution worked perfectly on our test table, thks. We are
looking to expand our Access & SQL knowledge, what options could you
recommend? Again thank you very much.
Bill
Click to expand...
 
SQL Queries for Mere Mortals is a book I liked to help me learn more about SQL
queries.

As far as books on Access, my best suggestion is go to a book store and look
through the books they have on the subject.  Use the index and try to locate a
section that helps you solve a current problem.  See if you are comfortable
with understanding what is there.

One of my best resources for learning was (and still is) these newsgroups..

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County



[email protected] said:
I think that the following should work.
SELECT A.First, A.Middle, A.Last
FROM [YourTable] as A INNER JOIN [YourTable] As B
ON A.First = B.First
AND A.Last = B.Last
WHERE A.Middle <> B.Middle
If this fails, you might post back and explain what results you got and
why they are different from what you want.
For instance, if one name had a null value for Middle then you would not
get the results you wanted.  However, that could be rectified by
changing the WHERE clause to
Where A.Middle <. B.Middle
OR A.Middle is Null and B.Middle is Not Null
OR A.Middle is Not Null and B.Middle is Null
'====================================================
  John Spencer
  Access MVP 2002-2005, 2007-2009
  The Hilltop Institute
  University of Maryland Baltimore County
'====================================================
Let me add a sample:
First - Middle - Last
Al    -     R     - Johnson
Bob  -     M   -  Doe
Fred  -    J     - Smith
Bob  -   W    -  Doe
Marc -          -  Lewis
Al     -    R    -  Johnson
I would like a query of those with the same first & last, but a
different middle, so the query will only show:
Bob - M - Doe
Bob - W - Doe
Thank you very much for all your help.
Bill- Hide quoted text -
- Show quoted text -
Click to expand...
Click to expand...
John, your solution worked perfectly on our test table, thks. We are
looking to expand our Access & SQL knowledge, what options could you
recommend? Again thank you very much.
Bill- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Again & Again thank you,

When we tested on the real data we run into a problem when more than 2
records meet the criteria (match, match & no match). If we have 3
input records, it shows 6, when we have 6 input rec, it show 30.

Any suggestions?

Bill
 
Perhaps adding the distinct keyword will help

SELECT DISTINCT A.First, A.Middle, A.Last
FROM [YourTable] as A INNER JOIN [YourTable] As B
ON A.First = B.First
AND A.Last = B.Last
WHERE A.Middle <> B.Middle

If the query needs to be updatable then you will need to use a subquery -
which can be slow if there are a LOT of records.

SELECT *
FROM [YourTable]
WHERE [YourTable].First & " " & [YourTable].Last IN
( SELECT A.First & " " & A.Last
FROM [YourTable] as A INNER JOIN [YourTable] As B
ON A.First = B.First
AND A.Last = B.Last
WHERE A.Middle <> B.Middle )

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top