How do I determine which table a distinct record is coming from?

[Guest]

I am using Access 2003 version. I have completed a distinct query on 2 tables
and the results are fine but I need to separate the results by each table
from which they originated.

 

[Duane Hookom]

Are you sure you don't mean a "union" query? Can you provide your SQL view
of the query?

 

[John Vinson]

On Wed, 23 Nov 2005 10:02:05 -0800, Suzy H <Suzy

I am using Access 2003 version. I have completed a distinct query on 2 tables
and the results are fine but I need to separate the results by each table
from which they originated.

A "distinct query"?

Please post the SQL of the query. If it's a UNION query you will need
to include the source table as a calculated field in the query:

SELECT TableA.*, "A" As TableName FROM TableA
UNION
SELECT TableB.*, "B" As TableName FROM TableB

but of course this will lose you the advantage of DISTINCT. If the two
records are identical, though, it should make no difference which
table it's from!

John W. Vinson[MVP]

 

[Guest]

In looking back on my work before the holidays, I have discovered that the
results I hoped were correct actually contained records from both tables
which would be considered a duplicate record (last name + first name as a
match in both tables). The union query also gave me the distinct records.
What I need is to be able use both fields (last name & first name) & find
unmatched but I need to distinguish from which table the record originates to
use the final cut in further work.

This is my union query:

SELECT [LAST_NAME], [FIRST_NAME] FROM [PPO+ Distinct]

UNION SELECT [DRLNAME], [DRFNAME] FROM [MHP Distinct]
ORDER BY [LAST_NAME];

I have tried several other queries but unsuccessful because of the two
fields that have to match.

Please help.
Suzy

 

[John Vinson]

In looking back on my work before the holidays, I have discovered that the
results I hoped were correct actually contained records from both tables
which would be considered a duplicate record (last name + first name as a
match in both tables). The union query also gave me the distinct records.
What I need is to be able use both fields (last name & first name) & find
unmatched but I need to distinguish from which table the record originates to
use the final cut in further work.

This is my union query:

SELECT [LAST_NAME], [FIRST_NAME] FROM [PPO+ Distinct]

UNION SELECT [DRLNAME], [DRFNAME] FROM [MHP Distinct]
ORDER BY [LAST_NAME];

I have tried several other queries but unsuccessful because of the two
fields that have to match.

I'm confused then. Unless you use UNION ALL, which returns all records
including duplicates, this should return only one instance of the name
if the names are in fact identical in the two tables. And if they ARE
identical, what difference does it make?

What you can do is include the tablename as a calculated field:

SELECT [LAST_NAME], [FIRST_NAME], "PPO" AS FromWHer
FROM [PPO+ Distinct]
UNION
SELECT [DRLNAME], [DRFNAME], "MHP"
FROM [MHP Distinct] ORDER BY [LAST_NAME];

Note that names are NOT unique; either table or both tables could have
two or more "Larry Jones" records which might or might not be the same
person. I'd be very leery of using UNION for that reason, you'll lose
data! UNION ALL may be safer.

John W. Vinson[MVP]

 

[Guest]

Ok when I use your statement & also when I add UNION ALL to the query, I get
the same results - all the records from both tables with identifiers. That's
cool, but how do I select:

1) all records that have a match by name & delete (I already have a
duplicates query from both tables)
2) all records that are in "PPO" that are not in "MHP"

Thanks for your help.
Suzy

In looking back on my work before the holidays, I have discovered that the
results I hoped were correct actually contained records from both tables
which would be considered a duplicate record (last name + first name as a
match in both tables). The union query also gave me the distinct records.
What I need is to be able use both fields (last name & first name) & find
unmatched but I need to distinguish from which table the record originates to
use the final cut in further work.

This is my union query:

SELECT [LAST_NAME], [FIRST_NAME] FROM [PPO+ Distinct]

UNION SELECT [DRLNAME], [DRFNAME] FROM [MHP Distinct]
ORDER BY [LAST_NAME];

I have tried several other queries but unsuccessful because of the two
fields that have to match.

I'm confused then. Unless you use UNION ALL, which returns all records
including duplicates, this should return only one instance of the name
if the names are in fact identical in the two tables. And if they ARE
identical, what difference does it make?

What you can do is include the tablename as a calculated field:

SELECT [LAST_NAME], [FIRST_NAME], "PPO" AS FromWHer
FROM [PPO+ Distinct]
UNION
SELECT [DRLNAME], [DRFNAME], "MHP"
FROM [MHP Distinct] ORDER BY [LAST_NAME];

Note that names are NOT unique; either table or both tables could have
two or more "Larry Jones" records which might or might not be the same
person. I'd be very leery of using UNION for that reason, you'll lose
data! UNION ALL may be safer.

John W. Vinson[MVP]

 

[John Vinson]

Ok when I use your statement & also when I add UNION ALL to the query, I get
the same results - all the records from both tables with identifiers. That's
cool, but how do I select:

1) all records that have a match by name & delete (I already have a
duplicates query from both tables)

You can't delete or otherwise update a Union query at all. You may be
able to use a Subquery, but it'll be complicated - how will you decide
which record to delete?

A better solution might be to create a new table with a unique Index
on the columns which constitute a duplicate; run Append queries from
both the PPO and MHP tables into this new table. Only one instance of
each will be saved, you'll get a warning message that "x records were
not appended due to key violations" - that's your dups.

2) all records that are in "PPO" that are not in "MHP"

A UNION query is inappropriate here. Use a Frustrated Outer Join query
instead:

DELETE PPO.*
FROM PPO LEFT JOIN MHP
ON PPO.[LAST_NAME] = MHP.[DRLNAME]
AND PPO.[FIRST_NAME] = MHP.[DRFNAME]
WHERE MHP.LAST_NAME IS NULL;

Note that this will - again - delete records for MULTIPLE PEOPLE if
they happen to share the same name. This is quite common; I know three
gentlemen named Fred Brown, right here in little Parma, Idaho.


John W. Vinson[MVP]

 

[Guest]

Never heard of Little Parma, Idaho down here in New Orleans & I can't believe
you actually know 3 people with the same name there. But for what I am doing,
I am not worried about the dups but will use this to my advantage.

I am not sure how I ened up with the data in the table I created. I followed
your advice on step one, but could not continue with the query because the
tables needed a relationship. I was forced to close the query, build the
relationship, then run the query. I created the indexes on the new table, ran
the append query, received no error message & ended up with a table with all
records from PPO & only those that matched from MHP. I think I can use this
for my project without having to do the frustrated outer join.

I hope this hasn't been too painful for you because I absolutely appreciate
all the help you have given me.

This is a great resource for Access strugglers.

Thanks,
Suzy

Ok when I use your statement & also when I add UNION ALL to the query, I get
the same results - all the records from both tables with identifiers. That's
cool, but how do I select:

1) all records that have a match by name & delete (I already have a
duplicates query from both tables)

You can't delete or otherwise update a Union query at all. You may be
able to use a Subquery, but it'll be complicated - how will you decide
which record to delete?

A better solution might be to create a new table with a unique Index
on the columns which constitute a duplicate; run Append queries from
both the PPO and MHP tables into this new table. Only one instance of
each will be saved, you'll get a warning message that "x records were
not appended due to key violations" - that's your dups.

2) all records that are in "PPO" that are not in "MHP"

A UNION query is inappropriate here. Use a Frustrated Outer Join query
instead:

DELETE PPO.*
FROM PPO LEFT JOIN MHP
ON PPO.[LAST_NAME] = MHP.[DRLNAME]
AND PPO.[FIRST_NAME] = MHP.[DRFNAME]
WHERE MHP.LAST_NAME IS NULL;

Note that this will - again - delete records for MULTIPLE PEOPLE if
they happen to share the same name. This is quite common; I know three
gentlemen named Fred Brown, right here in little Parma, Idaho.


John W. Vinson[MVP]

 

[John Vinson]

I hope this hasn't been too painful for you because I absolutely appreciate
all the help you have given me.

This is a great resource for Access strugglers.

Glad you got it working - and believe me, it's this kind of
appreciation that keeps us volunteering!

You might not know three people named Fred Brown in New Orleans... but
you might know three people named Thibadeaux <g>!

Hope you and yours are recovering well from the hurricanes.

John W. Vinson[MVP]