Creating query based on no-match

  • Thread starter Thread starter The Mysterious J
  • Start date Start date
T

The Mysterious J

I have to compare 2 databases. One I've received - all insurance billing
claim lines. I'm waiting for all services - whether or not they had
insurance. I know that if I create a query and I pull in the 2 tables into
the query, and I create a relationship between the provider, client, date of
service, type of service, et cetera, I will see only the total services that
had insurance. However, what I want is the opposite. I want to know who
DIDN"T have insurance. If I pull in 2 tables and create all relationships
between the like fields, it will EXCLUDE the lines I want to see. Is there a
way to somehow reverse this quality of the query so I can see what ones are
NOT insured? Both DBs are all text; the one I received has no key field
identified.
 
I have to compare 2 databases.  One I've received - all insurance billing
claim lines.  I'm waiting for all services - whether or not they had
insurance.  I know that if I create a query and I pull in the 2 tables into
the query, and I create a relationship between the provider, client, date of
service, type of service, et cetera, I will see only the total services that
had insurance.  However, what I want is the opposite.  I want to know who
DIDN"T have insurance.  If I pull in 2 tables and create all relationships
between the like fields, it will EXCLUDE the lines I want to see.  Is there a
way to somehow reverse this quality of the query so I can see what ones are
NOT insured?  Both DBs are all text; the one I received has no key field
identified.
Click to expand...

If you wanted to see all occurrences where Insurance_Paid_Service
matched Provider_Rendered_Service, you would probably write

SELECT A.ClientID, A.ProviderID, A.DateOfService, A.TypeOfService
FROM Provider_Rendered_Service as A
INNER JOIN Insurance_Paid_Service As B
ON A.ClientID = B.ClientID and
A.ProviderID = B.ProviderID and
A.DateOfService = B.DateOfService and
A.TypeOfService = B.TypeOfService

To find all the unmatched Provider_Rendered_Service rows, use the same
select statement but change the INNER JOIN to LEFT JOIN. Also add the
improbable WHERE clause of

WHERE B.ClientID is null


SELECT A.ClientID, A.ProviderID, A.DateOfService, A.TypeOfService
FROM Provider_Rendered_Service as A
LEFT JOIN Insurance_Paid_Service As B
ON A.ClientID = B.ClientID and
A.ProviderID = B.ProviderID and
A.DateOfService = B.DateOfService and
A.TypeOfService = B.TypeOfService
WHERE B.ClientID is null

An alternate method would be to

SELECT A.ClientID, A.ProviderID, A.DateOfService, A.TypeOfService
FROM Provider_Rendered_Service as A
WHERE NOT EXISTS
( SELECT 'true'
FROM Insurance_Paid_Service As B
WHERE ClientID = A.ClientID and
ProviderID = A.ProviderID and
DateOfService = A.DateOfService and
TypeOfService = A.TypeOfService )

The second methodology is said to be easier to read and understand.
However, it cannot be created in the Design View.


Some version of Access have a menu item labeled "Unmatch item
query..." which might help you do this in the Design View rather than
the SQL View.

To do any of this, you must link the foreign table into your local
database using the GetExternalData menu option.
 
Change the INNER JOIN to a Left or Right Join and then check for a field
in the table that is null.

Take you current query that finds all the matches.
Make a copy of it
-- Open it in design view
-- SELECT View: SQL from the menu
-- Find the part where the statement is something like

CLAIMS INNER JOIN INSURANCE ON CLAIMS.Provider = Insurance.Provider ...

--Change "INNER JOIN" to "LEFT JOIN"
--Switch back to Design view (View: Design)
-- Under any field of Insurance table enter the criteria
Is Null

Run the query and see if you get the results you want. If not post back
and tell us the problem. Why were the results incorrect.



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
I've only worked in the Design View - I don't even know how to get to SQL
view or how to use it.
 
I've only worked in the Design View - I don't even know how to get to SQL
view or how to use it.















- Show quoted text -
Click to expand...

I'm way behind the times, but my version of Access has a View menu
when I'm in the Design View. There is an option under the View item
for SQL View.
 
Thanks... I didn't know to go to VIEW then SQL. Can I ask you to re-phrase
the answer with the actual SQL? Since this is my first time working in SQL,
it would help.

This represents a match on the "RU" (provider), client number (unique ID for
client), date of service, Mode/Service function/procedure (all refer to the
kind of service), units of service (visits), units of time (minutes) and
amount (charge for service).

SELECT
FROM COST_SERVICE INNER JOIN TOTAL ON (TOTAL.[Claimed Minutes] =
COST_SERVICE.[Claimed Units-of-Time]) AND (COST_SERVICE.[Claimed Amount] =
TOTAL.[Claimed Amount]) AND (TOTAL.[Claimed Units] = COST_SERVICE.[Claimed
Units-of-Service]) AND (TOTAL.Procedure = COST_SERVICE.[Procedure Code]) AND
(TOTAL.[Service Function] = COST_SERVICE.[Service-Function-Code]) AND
(TOTAL.[Service Date] = COST_SERVICE.[Service Date]) AND (TOTAL.Mode =
COST_SERVICE.[Mode Of Service]) AND (COST_SERVICE.[Client Number] =
TOTAL.[Client Number]) AND (COST_SERVICE.RU = TOTAL.RU);
 
Try one of these two queries. The first will show all the Cost_Service
records that have no match in TOTAL. The second will show all Total records
that have no match in Cost_Service.


SELECT Cost_Service.*
FROM COST_SERVICE LEFT JOIN TOTAL
ON (TOTAL.[Claimed Minutes] = COST_SERVICE.[Claimed Units-of-Time])
AND (COST_SERVICE.[Claimed Amount] = TOTAL.[Claimed Amount])
AND (TOTAL.[Claimed Units] = COST_SERVICE.[Claimed Units-of-Service])
AND (TOTAL.Procedure = COST_SERVICE.[Procedure Code])
AND (TOTAL.[Service Function] = COST_SERVICE.[Service-Function-Code])
AND (TOTAL.[Service Date] = COST_SERVICE.[Service Date])
AND (TOTAL.Mode = COST_SERVICE.[Mode Of Service])
AND (COST_SERVICE.[Client Number] = TOTAL.[Client Number])
AND (COST_SERVICE.RU = TOTAL.RU)
WHERE Total.[Client Number] is Null

Or

SELECT Total.*
FROM COST_SERVICE RIGHT JOIN TOTAL
ON (TOTAL.[Claimed Minutes] = COST_SERVICE.[Claimed Units-of-Time])
AND (COST_SERVICE.[Claimed Amount] = TOTAL.[Claimed Amount])
AND (TOTAL.[Claimed Units] = COST_SERVICE.[Claimed Units-of-Service])
AND (TOTAL.Procedure = COST_SERVICE.[Procedure Code])
AND (TOTAL.[Service Function] = COST_SERVICE.[Service-Function-Code])
AND (TOTAL.[Service Date] = COST_SERVICE.[Service Date])
AND (TOTAL.Mode = COST_SERVICE.[Mode Of Service])
AND (COST_SERVICE.[Client Number] = TOTAL.[Client Number])
AND (COST_SERVICE.RU = TOTAL.RU)
WHERE Cost_Service.[Client Number] is Null

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
 
Back
Top