How to select just one record

[JoJo Sharp via AccessMonster.com]

Hi gurus, I have benefited tremendously from reading your postings, more
than from my work or books. Thank you in advance for your help here.

I have two tables: DrVisits and HealthCoverage. I would like to find if a
doctor's visit (tbl: DrVisits) is covered by a plan (tbl: HealthPlan). If
covered, I marked the resulting table as "y"; Otherwise, "n". My query shown
below.

DrVisits
MemID dos
1 5/1/2002
1 6/8/2003
1 12/31/2004

HealthPlan
MemID plan start end
1 HealthNet 1/1/2002 6/1/2002
1 Pacificare 1/1/2003 12/31/2003
1 SecureHorizon 1/1/2005 12/31/9999

My qry: SELECT DrVisits.memID, DrVisits.dos, HealthPlan.plan, IIf(DrVisits!
dos Between HealthPlan!start And HealthPlan!end,"y","n") AS covered
FROM DrVisits LEFT JOIN HealthPlan ON DrVisits.memID = HealthPlan.memID

However, this results is not ideal. See result please:
memID dos plan covered
1 5/1/2002 hp y
1 5/1/2002 pc n
1 5/1/2002 sh n
1 6/8/2003 hp n
1 6/8/2003 pc y
1 6/8/2003 sh n
1 12/31/2004 hp n
1 12/31/2004 pc n
1 12/31/2004 sh n

How can I just do this qry so that I only have three qualified entries:
1 5/1/2002 hp y
1 6/8/2003 pc y
1 12/31/2004 n

 

[Marshall Barton]

Hi gurus, I have benefited tremendously from reading your postings, more
than from my work or books. Thank you in advance for your help here.

I have two tables: DrVisits and HealthCoverage. I would like to find if a
doctor's visit (tbl: DrVisits) is covered by a plan (tbl: HealthPlan). If
covered, I marked the resulting table as "y"; Otherwise, "n". My query shown
below.

DrVisits
MemID dos
1 5/1/2002
1 6/8/2003
1 12/31/2004

HealthPlan
MemID plan start end
1 HealthNet 1/1/2002 6/1/2002
1 Pacificare 1/1/2003 12/31/2003
1 SecureHorizon 1/1/2005 12/31/9999

My qry: SELECT DrVisits.memID, DrVisits.dos, HealthPlan.plan, IIf(DrVisits!
dos Between HealthPlan!start And HealthPlan!end,"y","n") AS covered
FROM DrVisits LEFT JOIN HealthPlan ON DrVisits.memID = HealthPlan.memID []

How can I just do this qry so that I only have three qualified entries:
1 5/1/2002 hp y
1 6/8/2003 pc y
1 12/31/2004 n

Try something more like:

SELECT DrVisits.memID, dos, plan,
IIf(dos Is Not Null,"y","n") AS covered
FROM DrVisits LEFT JOIN HealthPlan
ON DrVisits.memID = HealthPlan.memID
AND dos Between [start] And [end]

 

[JoJo Sharp via AccessMonster.com]

Thank you for the reply.

This qry still does not not give me the right result. I only get the dos that
is covered (5/1/2002, 6/8/2003). How do I also get the dos that is not
covered by a health plan (12/31/204)? Maybe a sub-query? Thanks in advance.

JoJo S.

 

[Marshall Barton]

This qry still does not not give me the right result. I only get the dos that
is covered (5/1/2002, 6/8/2003). How do I also get the dos that is not
covered by a health plan (12/31/204)? Maybe a sub-query? Thanks in advance.

I think what I posted should do that. Please post a
Copy/Paste of your current query.

 

[JoJo Sharp via AccessMonster.com]

Thank you very much. Here is the sql: It selects the "y" coverage but
neglects "n" coverage due to the Where clause at the end.

SELECT DrVisits.MemID, DrVisits.dos, HealthPlan.plan, IIf([dos] Is Not Null,
"y","n") AS covered
FROM DrVisits LEFT JOIN HealthPlan ON DrVisits.MemID = HealthPlan.MemID
WHERE (((DrVisits.dos) Between [HealthPlan]![start] And [HealthPlan]![end]));

My plan now is to do three queries and one union qry: The 1st is to use the
above sql statements WITHOUT THE WHERE CLAUSE. The result is as following:

MemID dos plan covered
1 5/1/2002 hn y
1 5/1/2002 pc n
1 5/1/2002 sh n
1 6/8/2003 hn n
1 6/8/2003 pc y
1 6/8/2003 sh n
1 12/31/2004 hn n
1 12/31/2004 pc n
1 12/31/2004 sh n

The 2nd qry is to select "y" ONLY to get the dos that have plan coverage:

SELECT result1.MemID, result1.dos, result1.plan, result1.covered INTO result2
FROM result1
WHERE (((result1.covered)="y"));

MemID dos plan covered
1 5/1/2002 hn y
1 6/8/2003 pc y

The 3rd qry is to select "n" ONLY dos:

SELECT DISTINCT result1.MemID, result1.dos, "none" AS plan, "n" AS covered
INTO result3
FROM result1 LEFT JOIN result2 ON (result1.MemID = result2.MemID) AND
(result1.dos = result2.dos)
WHERE (((result2.MemID) Is Null) AND ((result2.dos) Is Null));

MemID dos
1 12/31/2004

I finally will use union qry to get the final result:

SELECT MemID, dos, plan, covered
FROM result2
Union all
SELECT MemID, dos, plan, covered
FROM result3

MemID dos plan covered
1 5/1/2002 hn y
1 6/8/2003 pc y
1 12/31/2004 none n

This seems cumbersome, but I can't think of a better way to get the final
result. Do you have better way of handling this? Thank you in advance for all
of your input.

 

[Marshall Barton]

Thank you very much. Here is the sql: It selects the "y" coverage but
neglects "n" coverage due to the Where clause at the end.

SELECT DrVisits.MemID, DrVisits.dos, HealthPlan.plan, IIf([dos] Is Not Null,
"y","n") AS covered
FROM DrVisits LEFT JOIN HealthPlan ON DrVisits.MemID = HealthPlan.MemID
WHERE (((DrVisits.dos) Between [HealthPlan]![start] And [HealthPlan]![end]));

Forget the alternate plan, one way or another, it has to be
unnecessary.

I think the Where clause is correct and is not the cause of
the problem.

I'm pretty sure that I made a mistake in the IIf. Try this:

. . . IIf(HealthPlan.MemID Is Not Null, "y","n") AS covered

 

[JoJo Sharp via AccessMonster.com]

No, it still does not work after I changed the IIF statement. I still only
get the "y"s (the dos with health plan). I think because of the WHERE
statement, the qry only looks at the dos that falls in between the
corresponding health plan period. There is no data in the health plan table
if there is no plan (such as period of 12/31/2004)

Appreciate your input.

 

[Marshall Barton]

No, it still does not work after I changed the IIF statement. I still only
get the "y"s (the dos with health plan). I think because of the WHERE
statement, the qry only looks at the dos that falls in between the
corresponding health plan period. There is no data in the health plan table
if there is no plan (such as period of 12/31/2004)

Well, yes, the where clause is dropping those records. But
you were not supposed to use a where clause. Check back and
you'll see that I use an AND in the ON clause, not a where
clause.

SELECT DrVisits.MemID, DrVisits.dos, HealthPlan.plan,
IIf(HealthPlan.MemID Is Not Null, "y","n") AS covered
FROM DrVisits LEFT JOIN HealthPlan
ON DrVisits.MemID = HealthPlan.MemID
AND DrVisits.dos Between [HealthPlan]![start]
And [HealthPlan]![end]

 

[JoJo Sharp via AccessMonster.com]

You are marvelous. Thank you so much for your patience with me!