Mutiple Tables Duplicate Field Query

[DaveAP]

I am trying to write a query that will show me duplicate information from one
field across seven different tables.

Example:

The Claims Tracking Number field from Table A222 matches Claims Tracking
Number from Table A333 and A444.

Is this possible?

 

[Jeff Boyce]

That sounds like ... a spreadsheet!

If you have multiple tables ("A222", "A333", ...) with identical structure,
there's a good chance you're committing spreadsheet on Access.

Access' relationally-oriented features and functions don't work well if you
feed them 'sheet data.

What about a single table with all the fields you already have, plus one
more, to hold "A222" or "A333" or ... (whatever those represent?!)?

With a single table design, you can very easily use an Access query to get
what you are seeking.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Adrian]

You could use a query to look where the same value in the same field in each
table appeared and then set the query to count the number of appearances and
only pull back those values where the the count was greater than 1.

Like thi
---------------------------------------------------------------------------------------------
SELECT A222.[Claims Tracking Number], Count(A222.[Claims Tracking Number])
AS [CountOfClaims Tracking Number]
FROM (A222 INNER JOIN A333 ON A222.[Claims Tracking Number] = A333.[Claims
Tracking Number]) INNER JOIN A444 ON A333.[Claims Tracking Number] =
A444.[Claims Tracking Number]
GROUP BY A222.[Claims Tracking Number]
HAVING (((Count(A222.[Claims Tracking Number]))>1))

 

[John Spencer]

Yes and No.

You could include all seven tables and do a standard inner join on the
Claims tracking number. That will show you where any claims number that
is in all seven tables. Or if you are interested in just three tables
then just build a query joining the three table on the claims number.

OR try a UNION query as the base query

SELECT "TableA" as TableName, [ClaimsNumber]
FROM TableA
UNION
SELECT "TableB" as TableName, [ClaimsNumber]
FROM TableB
UNION
....
UNION "TableG" as TableName, [ClaimsNumber]
FROM TableG

Now you can use that saved query to identify which tables have the claim
number.

For instance, to get claim numbers that are in TableA, TableB, and
TableC, the query would look like:

SELECT [ClaimsNumber]
FROM TheUnionQuery
WHERE TableName in ("TableA","TableB","TableC")
GROUP BY [ClaimsNumber]
HAVING COUNT(*) = 3

In all tables except TableC.

SELECT [ClaimsNumber]
FROM TheUnionQuery
WHERE TableName <> "TableC"
GROUP BY [ClaimsNumber]
HAVING COUNT(*) = 6

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

 

[DaveAP]

The different forms have different fields but some of fields are the same.

Would it be possible to combine these separate tables into one master table
with current data already entered?

I am very new to this, thank you all for your responses.

 

[DaveAP]

Thank you John!



I'll try this out, and in the future remember to have one master table for
data


DaveAP

Yes and No.

You could include all seven tables and do a standard inner join on the
Claims tracking number. That will show you where any claims number that
is in all seven tables. Or if you are interested in just three tables
then just build a query joining the three table on the claims number.

OR try a UNION query as the base query

SELECT "TableA" as TableName, [ClaimsNumber]
FROM TableA
UNION
SELECT "TableB" as TableName, [ClaimsNumber]
FROM TableB
UNION
....
UNION "TableG" as TableName, [ClaimsNumber]
FROM TableG

Now you can use that saved query to identify which tables have the claim
number.

For instance, to get claim numbers that are in TableA, TableB, and
TableC, the query would look like:

SELECT [ClaimsNumber]
FROM TheUnionQuery
WHERE TableName in ("TableA","TableB","TableC")
GROUP BY [ClaimsNumber]
HAVING COUNT(*) = 3

In all tables except TableC.

SELECT [ClaimsNumber]
FROM TheUnionQuery
WHERE TableName <> "TableC"
GROUP BY [ClaimsNumber]
HAVING COUNT(*) = 6

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

I am trying to write a query that will show me duplicate information from one
field across seven different tables.

Example:

The Claims Tracking Number field from Table A222 matches Claims Tracking
Number from Table A333 and A444.

Is this possible?