Getting rid of duplicates in a union query

[Spudlover]

I have a union query that has birth dates and wedding dates.

Birthdates are fine.

The table has Last Name, Called, Spouse and Wed Da

SELECT [WED DA], [CALLED] & " & " & [SPOUSE] & " " & [LAST NAME] FROM DCC
Where [WED DA] IS NOT NULL
ORDER BY EDate

The query shows 9 23 1934 Jim & Jane Jones
and 9 23 1934 Jane & Jim Jones
How do I get only one entry for each couple?
Thanks

 

[Tom Ellison]

Dear Spud:

Is the object called DCC the union query? Does it say "UNION ALL"? You
could remove the ALL and just have UNION. This eliminates duplicates from
within either table and also between then. It can be a bit slow if there
are many columns and rows.

Tom Ellison

 

[Spudlover]

DCC is the original table

The union querey is called UNION BIRTH DA & WED DA. The Union Query does
say "UNION ALL" I just took out "ALL" and it made no difference. Is my
problem that Jim & Jane is not a duplicate of Jane & Jim. Can I do
anything to correct this?

Dear Spud:

Is the object called DCC the union query? Does it say "UNION ALL"? You
could remove the ALL and just have UNION. This eliminates duplicates from
within either table and also between then. It can be a bit slow if there
are many columns and rows.

Tom Ellison

I have a union query that has birth dates and wedding dates.

Birthdates are fine.

The table has Last Name, Called, Spouse and Wed Da

SELECT [WED DA], [CALLED] & " & " & [SPOUSE] & " " & [LAST NAME] FROM DCC
Where [WED DA] IS NOT NULL
ORDER BY EDate

The query shows 9 23 1934 Jim & Jane Jones
and 9 23 1934 Jane & Jim Jones
How do I get only one entry for each couple?
Thanks

 

[Van T. Dinh]

You may need to separate the "marriage relationship" (and [WED DA]) out to a
separate Table instead of including them in your current Table.

 

['69 Camaro]

Hi.

As Van mentioned, your table is not designed correctly, because it includes
two entities, persons and marital relationships, instead of just one entity.
This table should be separated into two tables, a process called
normalization. However, after reading in your previous post about how long
it took you to get this far, I suspect that this process isn't going to
happen any time soon.

You've got entries in your table that aren't really duplicates, but they are
logically equivalent to other entries in the table, such as "Jim & Jane
Jones" and "Jane & Jim Jones." You can separate these out, but the queries
are going to be a bit ugly.

You'll need a single field primary key for these queries, so if you don't
already have one, you can add an AutoNumber as the primary key temporarily to
make these queries work. Before you alter the table though, make a backup of
your table, just in case something goes wrong. The code I've written assumes
that the primary key is named ID, so if yours isn't the same, then you'll
need to replace ID in the code with your primary key's column name.

You're going to create two queries. The first one is going to separate by
last name and show each couple's names side by side, first with one name on
the left side in one column, then with the other name on the left side in the
next column. For example, JohnMary and MaryJohn. The second query will
compare these pairs of names in one column to determine whether there are any
matching duplicates in the other column but in different rows. Then it will
pick only the first pair found, no matter how many other duplicates or near
duplicates are stored in the table and use that record in your UNION query.

Create a new query and open it in SQL View. Copy and paste the following
into the SQL View pane:

SELECT ID, DCC.[LAST NAME],
(Called & Spouse) AS Pair,
(Spouse & Called) AS Swap
FROM DCC INNER JOIN
(SELECT [LAST NAME]
FROM DCC
GROUP BY [LAST NAME]
HAVING (COUNT([LAST NAME]) > 1)) AS Dups
ON DCC.[LAST NAME] = Dups.[LAST NAME];

Save this query as qryFindNearDuplicates. Create a new query and open it in
SQL View. Copy and paste the following into the SQL View pane:

SELECT [BIRTH DA] AS EDate,
[CALLED] & " " & [LAST NAME] AS who
FROM DCC
WHERE [BIRTH DA] IS NOT NULL
UNION ALL
SELECT [WED DA], (Called & " & " & Spouse &
" " & [LAST NAME]) AS Couple
FROM (SELECT IIf((P.ID>S.ID),P.ID,S.ID) AS Cut
FROM qryFindNearDuplicates AS P INNER JOIN
qryFindNearDuplicates AS S ON (P.Pair=S.Swap)
AND (P.[LAST NAME]=S.[LAST NAME])) AS Q
RIGHT JOIN DCC ON Q.Cut = DCC.ID
WHERE (ISNULL(Cut) = TRUE) AND [WED DA] IS NOT NULL
ORDER BY 1;

Save this query and name it anything you want. Hopefully, I've copied and
pasted these correctly from my example and your query listed in another post.
(If not and you can't get them to run correctly, please post back.) Run the
second query to see your records in the correct order and without duplicate
wedding dates.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

I have a union query that has birth dates and wedding dates.

Birthdates are fine.

The table has Last Name, Called, Spouse and Wed Da

SELECT [WED DA], [CALLED] & " & " & [SPOUSE] & " " & [LAST NAME] FROM DCC
Where [WED DA] IS NOT NULL
ORDER BY EDate

The query shows 9 23 1934 Jim & Jane Jones
and 9 23 1934 Jane & Jim Jones
How do I get only one entry for each couple?
Thanks

 

[Spudlover]

Thanks, I am in way over my head but want to continue.
The qryFindNearDuplicates works fine.

I do have a primary key INDEX.

I have tried 3 times for the second query inserting INDEX for ID, The
message is "the select statement includes a reversed word or an argument name
that is misspelled, or the the punctuation is incorrect."

Can you advise where I can learn more about writing these codes or fumulas
or even the terminology to use in this search?

Also, could you look over my other currently posted question about the same
database query "formatting dates in union queries". You are able to
communicate on a level that I can follow.

Your help is greatly appreciated.

Hi.

As Van mentioned, your table is not designed correctly, because it includes
two entities, persons and marital relationships, instead of just one entity.
This table should be separated into two tables, a process called
normalization. However, after reading in your previous post about how long
it took you to get this far, I suspect that this process isn't going to
happen any time soon.

You've got entries in your table that aren't really duplicates, but they are
logically equivalent to other entries in the table, such as "Jim & Jane
Jones" and "Jane & Jim Jones." You can separate these out, but the queries
are going to be a bit ugly.

You'll need a single field primary key for these queries, so if you don't
already have one, you can add an AutoNumber as the primary key temporarily to
make these queries work. Before you alter the table though, make a backup of
your table, just in case something goes wrong. The code I've written assumes
that the primary key is named ID, so if yours isn't the same, then you'll
need to replace ID in the code with your primary key's column name.

You're going to create two queries. The first one is going to separate by
last name and show each couple's names side by side, first with one name on
the left side in one column, then with the other name on the left side in the
next column. For example, JohnMary and MaryJohn. The second query will
compare these pairs of names in one column to determine whether there are any
matching duplicates in the other column but in different rows. Then it will
pick only the first pair found, no matter how many other duplicates or near
duplicates are stored in the table and use that record in your UNION query.

Create a new query and open it in SQL View. Copy and paste the following
into the SQL View pane:

SELECT ID, DCC.[LAST NAME],
(Called & Spouse) AS Pair,
(Spouse & Called) AS Swap
FROM DCC INNER JOIN
(SELECT [LAST NAME]
FROM DCC
GROUP BY [LAST NAME]
HAVING (COUNT([LAST NAME]) > 1)) AS Dups
ON DCC.[LAST NAME] = Dups.[LAST NAME];

Save this query as qryFindNearDuplicates. Create a new query and open it in
SQL View. Copy and paste the following into the SQL View pane:

SELECT [BIRTH DA] AS EDate,
[CALLED] & " " & [LAST NAME] AS who
FROM DCC
WHERE [BIRTH DA] IS NOT NULL
UNION ALL
SELECT [WED DA], (Called & " & " & Spouse &
" " & [LAST NAME]) AS Couple
FROM (SELECT IIf((P.ID>S.ID),P.ID,S.ID) AS Cut
FROM qryFindNearDuplicates AS P INNER JOIN
qryFindNearDuplicates AS S ON (P.Pair=S.Swap)
AND (P.[LAST NAME]=S.[LAST NAME])) AS Q
RIGHT JOIN DCC ON Q.Cut = DCC.ID
WHERE (ISNULL(Cut) = TRUE) AND [WED DA] IS NOT NULL
ORDER BY 1;

Save this query and name it anything you want. Hopefully, I've copied and
pasted these correctly from my example and your query listed in another post.
(If not and you can't get them to run correctly, please post back.) Run the
second query to see your records in the correct order and without duplicate
wedding dates.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

I have a union query that has birth dates and wedding dates.

Birthdates are fine.

The table has Last Name, Called, Spouse and Wed Da

SELECT [WED DA], [CALLED] & " & " & [SPOUSE] & " " & [LAST NAME] FROM DCC
Where [WED DA] IS NOT NULL
ORDER BY EDate

The query shows 9 23 1934 Jim & Jane Jones
and 9 23 1934 Jane & Jim Jones
How do I get only one entry for each couple?
Thanks

 

['69 Camaro]

Hi.

I do have a primary key INDEX.

Index is a Reserved word. You need to change this name to something else.
If you assign Reserved words as names for your objects, then when you use
them in a query or VBA code, the computer will try to use them for their
intended purposes instead, not as the names of your objects, and the query
or code will fail. So never use Reserved words as names, or identifiers, of
anything you create in Access, such as tables, fields, controls on forms,
variables in VBA procedures, et cetera. As a matter of fact, only use
alphanumeric characters and the underscore character for your identifiers,
and you'll avoid many other bugs, too. For lists of Reserved words to
avoid, please see the following Web pages:

http://support.microsoft.com/default.aspx?id=321266

http://support.microsoft.com/default.aspx?scid=286335

When reading this second page, pay particular attention to the myth on that
page because if you believe it, you will waste time chasing very hard to
find bugs that will teach you why it's not always true:

"For existing objects with names that contain reserved words, you can avoid
errors by surrounding the object name with brackets ([ ])."

Can you advise where I can learn more about writing these codes or
fumulas
or even the terminology to use in this search?

Perhaps someone else can advise you on what helped them, because I took the
easy road, myself. I got a job working with a top-notch Oracle DBA and an
Access guru who taught me about databases. Four months after I started, I
took the formal "Introduction to Oracle SQL, PL/SQL and SQL*Plus" course.
I'd have to say that I got a lot better at writing queries once I took the
formal course, but at the cost of $500 a day for a week, it's outside of
most people's budgets (and probably even more expensive nowadays, too).

You are able to
communicate on a level that I can follow.

I gotta tell ya, that's _not_ something an Oracle DBA hears every day! I'll
try my best though. But what the others are telling you is the same thing
that I'll tell you. We recommend that you to fix your database's problems
caused by the database design. We want to discourage you from trying to
work with a bad relational database design, because you'll have to jump
through hoops to get queries to work. And even when they appear to work,
these queries may actually be giving false results. In this case where I've
given you these two convoluted queries, I designed them so that they are
both giving correct results, but that's no guarantee that other queries you
create from this table will do the same.

If you want to learn some more about relational database design at a level
that's easy to follow, then please take a look at the Relational Database
Design Boot Camp series of articles. You'll find the links to these
articles on the following Web page:

http://www.Access.QBuilt.com/html/articles.html

This series of articles isn't finished yet, but it'll be enough to get you
started thinking about the guidelines to follow for proper relational
database design when you're designing tables.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

Thanks, I am in way over my head but want to continue.
The qryFindNearDuplicates works fine.

I do have a primary key INDEX.

I have tried 3 times for the second query inserting INDEX for ID, The
message is "the select statement includes a reversed word or an argument
name
that is misspelled, or the the punctuation is incorrect."

Can you advise where I can learn more about writing these codes or
fumulas
or even the terminology to use in this search?

Also, could you look over my other currently posted question about the
same
database query "formatting dates in union queries". You are able to
communicate on a level that I can follow.

Your help is greatly appreciated.

Hi.

As Van mentioned, your table is not designed correctly, because it
includes
two entities, persons and marital relationships, instead of just one
entity.
This table should be separated into two tables, a process called
normalization. However, after reading in your previous post about how
long
it took you to get this far, I suspect that this process isn't going to
happen any time soon.

You've got entries in your table that aren't really duplicates, but they
are
logically equivalent to other entries in the table, such as "Jim & Jane
Jones" and "Jane & Jim Jones." You can separate these out, but the
queries
are going to be a bit ugly.

You'll need a single field primary key for these queries, so if you don't
already have one, you can add an AutoNumber as the primary key
temporarily to
make these queries work. Before you alter the table though, make a
backup of
your table, just in case something goes wrong. The code I've written
assumes
that the primary key is named ID, so if yours isn't the same, then you'll
need to replace ID in the code with your primary key's column name.

You're going to create two queries. The first one is going to separate
by
last name and show each couple's names side by side, first with one name
on
the left side in one column, then with the other name on the left side in
the
next column. For example, JohnMary and MaryJohn. The second query will
compare these pairs of names in one column to determine whether there are
any
matching duplicates in the other column but in different rows. Then it
will
pick only the first pair found, no matter how many other duplicates or
near
duplicates are stored in the table and use that record in your UNION
query.

Create a new query and open it in SQL View. Copy and paste the following
into the SQL View pane:

SELECT ID, DCC.[LAST NAME],
(Called & Spouse) AS Pair,
(Spouse & Called) AS Swap
FROM DCC INNER JOIN
(SELECT [LAST NAME]
FROM DCC
GROUP BY [LAST NAME]
HAVING (COUNT([LAST NAME]) > 1)) AS Dups
ON DCC.[LAST NAME] = Dups.[LAST NAME];

Save this query as qryFindNearDuplicates. Create a new query and open it
in
SQL View. Copy and paste the following into the SQL View pane:

SELECT [BIRTH DA] AS EDate,
[CALLED] & " " & [LAST NAME] AS who
FROM DCC
WHERE [BIRTH DA] IS NOT NULL
UNION ALL
SELECT [WED DA], (Called & " & " & Spouse &
" " & [LAST NAME]) AS Couple
FROM (SELECT IIf((P.ID>S.ID),P.ID,S.ID) AS Cut
FROM qryFindNearDuplicates AS P INNER JOIN
qryFindNearDuplicates AS S ON (P.Pair=S.Swap)
AND (P.[LAST NAME]=S.[LAST NAME])) AS Q
RIGHT JOIN DCC ON Q.Cut = DCC.ID
WHERE (ISNULL(Cut) = TRUE) AND [WED DA] IS NOT NULL
ORDER BY 1;

Save this query and name it anything you want. Hopefully, I've copied
and
pasted these correctly from my example and your query listed in another
post.
(If not and you can't get them to run correctly, please post back.) Run
the
second query to see your records in the correct order and without
duplicate
wedding dates.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

I have a union query that has birth dates and wedding dates.

Birthdates are fine.

The table has Last Name, Called, Spouse and Wed Da

SELECT [WED DA], [CALLED] & " & " & [SPOUSE] & " " & [LAST NAME] FROM
DCC
Where [WED DA] IS NOT NULL
ORDER BY EDate

The query shows 9 23 1934 Jim & Jane Jones
and 9 23 1934 Jane & Jim Jones
How do I get only one entry for each couple?
Thanks

 

[Spudlover]

Thank you. There are no longer any duplicates.
Almost finished the project. Query sorts on year. Now I need to re-read
the posts to see if I can change the date format to sort chronologicaly
through the year.
Thanks for your patience. I have learned alot along the way.
Joy

Hi.

I do have a primary key INDEX.

Index is a Reserved word. You need to change this name to something else.
If you assign Reserved words as names for your objects, then when you use
them in a query or VBA code, the computer will try to use them for their
intended purposes instead, not as the names of your objects, and the query
or code will fail. So never use Reserved words as names, or identifiers, of
anything you create in Access, such as tables, fields, controls on forms,
variables in VBA procedures, et cetera. As a matter of fact, only use
alphanumeric characters and the underscore character for your identifiers,
and you'll avoid many other bugs, too. For lists of Reserved words to
avoid, please see the following Web pages:

http://support.microsoft.com/default.aspx?id=321266

http://support.microsoft.com/default.aspx?scid=286335

When reading this second page, pay particular attention to the myth on that
page because if you believe it, you will waste time chasing very hard to
find bugs that will teach you why it's not always true:

"For existing objects with names that contain reserved words, you can avoid
errors by surrounding the object name with brackets ([ ])."

Can you advise where I can learn more about writing these codes or
fumulas
or even the terminology to use in this search?

Perhaps someone else can advise you on what helped them, because I took the
easy road, myself. I got a job working with a top-notch Oracle DBA and an
Access guru who taught me about databases. Four months after I started, I
took the formal "Introduction to Oracle SQL, PL/SQL and SQL*Plus" course.
I'd have to say that I got a lot better at writing queries once I took the
formal course, but at the cost of $500 a day for a week, it's outside of
most people's budgets (and probably even more expensive nowadays, too).

You are able to
communicate on a level that I can follow.

I gotta tell ya, that's _not_ something an Oracle DBA hears every day! I'll
try my best though. But what the others are telling you is the same thing
that I'll tell you. We recommend that you to fix your database's problems
caused by the database design. We want to discourage you from trying to
work with a bad relational database design, because you'll have to jump
through hoops to get queries to work. And even when they appear to work,
these queries may actually be giving false results. In this case where I've
given you these two convoluted queries, I designed them so that they are
both giving correct results, but that's no guarantee that other queries you
create from this table will do the same.

If you want to learn some more about relational database design at a level
that's easy to follow, then please take a look at the Relational Database
Design Boot Camp series of articles. You'll find the links to these
articles on the following Web page:

http://www.Access.QBuilt.com/html/articles.html

This series of articles isn't finished yet, but it'll be enough to get you
started thinking about the guidelines to follow for proper relational
database design when you're designing tables.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

Thanks, I am in way over my head but want to continue.
The qryFindNearDuplicates works fine.

I do have a primary key INDEX.

I have tried 3 times for the second query inserting INDEX for ID, The
message is "the select statement includes a reversed word or an argument
name
that is misspelled, or the the punctuation is incorrect."

Can you advise where I can learn more about writing these codes or
fumulas
or even the terminology to use in this search?

Also, could you look over my other currently posted question about the
same
database query "formatting dates in union queries". You are able to
communicate on a level that I can follow.

Your help is greatly appreciated.

Hi.

As Van mentioned, your table is not designed correctly, because it
includes
two entities, persons and marital relationships, instead of just one
entity.
This table should be separated into two tables, a process called
normalization. However, after reading in your previous post about how
long
it took you to get this far, I suspect that this process isn't going to
happen any time soon.

You've got entries in your table that aren't really duplicates, but they
are
logically equivalent to other entries in the table, such as "Jim & Jane
Jones" and "Jane & Jim Jones." You can separate these out, but the
queries
are going to be a bit ugly.

You'll need a single field primary key for these queries, so if you don't
already have one, you can add an AutoNumber as the primary key
temporarily to
make these queries work. Before you alter the table though, make a
backup of
your table, just in case something goes wrong. The code I've written
assumes
that the primary key is named ID, so if yours isn't the same, then you'll
need to replace ID in the code with your primary key's column name.

You're going to create two queries. The first one is going to separate
by
last name and show each couple's names side by side, first with one name
on
the left side in one column, then with the other name on the left side in
the
next column. For example, JohnMary and MaryJohn. The second query will
compare these pairs of names in one column to determine whether there are
any
matching duplicates in the other column but in different rows. Then it
will
pick only the first pair found, no matter how many other duplicates or
near
duplicates are stored in the table and use that record in your UNION
query.

Create a new query and open it in SQL View. Copy and paste the following
into the SQL View pane:

SELECT ID, DCC.[LAST NAME],
(Called & Spouse) AS Pair,
(Spouse & Called) AS Swap
FROM DCC INNER JOIN
(SELECT [LAST NAME]
FROM DCC
GROUP BY [LAST NAME]
HAVING (COUNT([LAST NAME]) > 1)) AS Dups
ON DCC.[LAST NAME] = Dups.[LAST NAME];

Save this query as qryFindNearDuplicates. Create a new query and open it
in
SQL View. Copy and paste the following into the SQL View pane:

SELECT [BIRTH DA] AS EDate,
[CALLED] & " " & [LAST NAME] AS who
FROM DCC
WHERE [BIRTH DA] IS NOT NULL
UNION ALL
SELECT [WED DA], (Called & " & " & Spouse &
" " & [LAST NAME]) AS Couple
FROM (SELECT IIf((P.ID>S.ID),P.ID,S.ID) AS Cut
FROM qryFindNearDuplicates AS P INNER JOIN
qryFindNearDuplicates AS S ON (P.Pair=S.Swap)
AND (P.[LAST NAME]=S.[LAST NAME])) AS Q
RIGHT JOIN DCC ON Q.Cut = DCC.ID
WHERE (ISNULL(Cut) = TRUE) AND [WED DA] IS NOT NULL
ORDER BY 1;

Save this query and name it anything you want. Hopefully, I've copied
and
pasted these correctly from my example and your query listed in another
post.
(If not and you can't get them to run correctly, please post back.) Run
the
second query to see your records in the correct order and without
duplicate
wedding dates.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.


:

I have a union query that has birth dates and wedding dates.

Birthdates are fine.

The table has Last Name, Called, Spouse and Wed Da

SELECT [WED DA], [CALLED] & " & " & [SPOUSE] & " " & [LAST NAME] FROM
DCC
Where [WED DA] IS NOT NULL
ORDER BY EDate

The query shows 9 23 1934 Jim & Jane Jones
and 9 23 1934 Jane & Jim Jones
How do I get only one entry for each couple?
Thanks

 

['69 Camaro]

Hi.

Here is a list of some SQL tutorials you may be interested in. MSDN has
tutorials for fundamental, intermediate, and advanced Jet 4.0 SQL (Jet 4.0
is the database engine used by Access 2000, 2002, and 2003):

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/acfundsql.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/acintsql.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/acadvsql.asp

The following aren't specifically for the Jet 4.0 dialect, but they're still
good for a beginner. The following one shows SQL Server, Oracle and MySQL
syntax:

http://www.1keydata.com/sql/sql.html

In case you want to play with the sample queries in the tutorials, this
generic SQL tutorial provides an interactive query window:

http://www.sqlcourse.com

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

Hi.

I do have a primary key INDEX.

Index is a Reserved word. You need to change this name to something else.
If you assign Reserved words as names for your objects, then when you use
them in a query or VBA code, the computer will try to use them for their
intended purposes instead, not as the names of your objects, and the query
or code will fail. So never use Reserved words as names, or identifiers,
of anything you create in Access, such as tables, fields, controls on
forms, variables in VBA procedures, et cetera. As a matter of fact, only
use alphanumeric characters and the underscore character for your
identifiers, and you'll avoid many other bugs, too. For lists of Reserved
words to avoid, please see the following Web pages:

http://support.microsoft.com/default.aspx?id=321266

http://support.microsoft.com/default.aspx?scid=286335

When reading this second page, pay particular attention to the myth on
that page because if you believe it, you will waste time chasing very hard
to find bugs that will teach you why it's not always true:

"For existing objects with names that contain reserved words, you can
avoid errors by surrounding the object name with brackets ([ ])."

Can you advise where I can learn more about writing these codes or
fumulas
or even the terminology to use in this search?

Perhaps someone else can advise you on what helped them, because I took
the easy road, myself. I got a job working with a top-notch Oracle DBA
and an Access guru who taught me about databases. Four months after I
started, I took the formal "Introduction to Oracle SQL, PL/SQL and
SQL*Plus" course. I'd have to say that I got a lot better at writing
queries once I took the formal course, but at the cost of $500 a day for a
week, it's outside of most people's budgets (and probably even more
expensive nowadays, too).

You are able to
communicate on a level that I can follow.

I gotta tell ya, that's _not_ something an Oracle DBA hears every day!
I'll try my best though. But what the others are telling you is the same
thing that I'll tell you. We recommend that you to fix your database's
problems caused by the database design. We want to discourage you from
trying to work with a bad relational database design, because you'll have
to jump through hoops to get queries to work. And even when they appear
to work, these queries may actually be giving false results. In this case
where I've given you these two convoluted queries, I designed them so that
they are both giving correct results, but that's no guarantee that other
queries you create from this table will do the same.

If you want to learn some more about relational database design at a level
that's easy to follow, then please take a look at the Relational Database
Design Boot Camp series of articles. You'll find the links to these
articles on the following Web page:

http://www.Access.QBuilt.com/html/articles.html

This series of articles isn't finished yet, but it'll be enough to get you
started thinking about the guidelines to follow for proper relational
database design when you're designing tables.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

Thanks, I am in way over my head but want to continue.
The qryFindNearDuplicates works fine.

I do have a primary key INDEX.

I have tried 3 times for the second query inserting INDEX for ID, The
message is "the select statement includes a reversed word or an argument
name
that is misspelled, or the the punctuation is incorrect."

Can you advise where I can learn more about writing these codes or
fumulas
or even the terminology to use in this search?

Also, could you look over my other currently posted question about the
same
database query "formatting dates in union queries". You are able to
communicate on a level that I can follow.

Your help is greatly appreciated.

Hi.

As Van mentioned, your table is not designed correctly, because it
includes
two entities, persons and marital relationships, instead of just one
entity.
This table should be separated into two tables, a process called
normalization. However, after reading in your previous post about how
long
it took you to get this far, I suspect that this process isn't going to
happen any time soon.

You've got entries in your table that aren't really duplicates, but they
are
logically equivalent to other entries in the table, such as "Jim & Jane
Jones" and "Jane & Jim Jones." You can separate these out, but the
queries
are going to be a bit ugly.

You'll need a single field primary key for these queries, so if you
don't
already have one, you can add an AutoNumber as the primary key
temporarily to
make these queries work. Before you alter the table though, make a
backup of
your table, just in case something goes wrong. The code I've written
assumes
that the primary key is named ID, so if yours isn't the same, then
you'll
need to replace ID in the code with your primary key's column name.

You're going to create two queries. The first one is going to separate
by
last name and show each couple's names side by side, first with one name
on
the left side in one column, then with the other name on the left side
in the
next column. For example, JohnMary and MaryJohn. The second query will
compare these pairs of names in one column to determine whether there
are any
matching duplicates in the other column but in different rows. Then it
will
pick only the first pair found, no matter how many other duplicates or
near
duplicates are stored in the table and use that record in your UNION
query.

Create a new query and open it in SQL View. Copy and paste the
following
into the SQL View pane:

SELECT ID, DCC.[LAST NAME],
(Called & Spouse) AS Pair,
(Spouse & Called) AS Swap
FROM DCC INNER JOIN
(SELECT [LAST NAME]
FROM DCC
GROUP BY [LAST NAME]
HAVING (COUNT([LAST NAME]) > 1)) AS Dups
ON DCC.[LAST NAME] = Dups.[LAST NAME];

Save this query as qryFindNearDuplicates. Create a new query and open
it in
SQL View. Copy and paste the following into the SQL View pane:

SELECT [BIRTH DA] AS EDate,
[CALLED] & " " & [LAST NAME] AS who
FROM DCC
WHERE [BIRTH DA] IS NOT NULL
UNION ALL
SELECT [WED DA], (Called & " & " & Spouse &
" " & [LAST NAME]) AS Couple
FROM (SELECT IIf((P.ID>S.ID),P.ID,S.ID) AS Cut
FROM qryFindNearDuplicates AS P INNER JOIN
qryFindNearDuplicates AS S ON (P.Pair=S.Swap)
AND (P.[LAST NAME]=S.[LAST NAME])) AS Q
RIGHT JOIN DCC ON Q.Cut = DCC.ID
WHERE (ISNULL(Cut) = TRUE) AND [WED DA] IS NOT NULL
ORDER BY 1;

Save this query and name it anything you want. Hopefully, I've copied
and
pasted these correctly from my example and your query listed in another
post.
(If not and you can't get them to run correctly, please post back.)
Run the
second query to see your records in the correct order and without
duplicate
wedding dates.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and
tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.


:

I have a union query that has birth dates and wedding dates.

Birthdates are fine.

The table has Last Name, Called, Spouse and Wed Da

SELECT [WED DA], [CALLED] & " & " & [SPOUSE] & " " & [LAST NAME] FROM
DCC
Where [WED DA] IS NOT NULL
ORDER BY EDate

The query shows 9 23 1934 Jim & Jane Jones
and 9 23 1934 Jane & Jim Jones
How do I get only one entry for each couple?
Thanks