query question

  • Thread starter Thread starter Wei
  • Start date Start date
W

Wei

I have a table with two fields:

Policy Number and Coverage.

For each policy number, there are several records with
different coverages.

Example:

Policy Number Coverage
PP123 CL
PP123 CM
PP123 BI

PP124 BI
PP124 AP
PP124 UM
PP124 OP

"CL" and "AP" cannot exist for the same policy at the same
time. Now, for each policy, I want to check whether it has
a coverage called "CL" or "AP".
How should I do this in a query?

Thanks a lot...

Wei
 
Dear Wei:

For a list of all Policy Numbers that DO have either CL or AP, you can
start with this:

SELECT [Policy Number]
FROM YourTable
WHERE Coverage IN ("CL", "AP")

However, this could list a policy twice if it had both CL and AP. You
said this couldn't happen, so maybe that is OK as it is, or you could
make a slight change:

SELECT DISTINCT [Policy Number]
FROM YourTable
WHERE Coverage IN ("CL", "AP")

This will guarantee the Policies are not listed twice, but is a waste
of time if there is truly no chance of having both CL and AP for a
policy.

You can also see the Policy and whether it has CL or AP if desired.

SELECT [Policy Number], Coverage
FROM YourTable
WHERE Coverage IN ("CL", "AP")

Do you need to see those that do NOT have CL or AP?

SELECT DISTINCT [Policy Number]
FROM YourTable T
WHERE NOT EXISTS (SELECT * FROM YourTable T1
WHERE T1.[Policy Number] = T.[Policy Number]
AND T1.Coverage IN ("CL", "AP"))

In each query above, substitute the actual name of your table where it
says YourTable.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Thank you very much Tom! It works!

What does the "IN("CL", "AP")" do?

Thanks again
Wei
-----Original Message-----
Dear Wei:

For a list of all Policy Numbers that DO have either CL or AP, you can
start with this:

SELECT [Policy Number]
FROM YourTable
WHERE Coverage IN ("CL", "AP")

However, this could list a policy twice if it had both CL and AP. You
said this couldn't happen, so maybe that is OK as it is, or you could
make a slight change:

SELECT DISTINCT [Policy Number]
FROM YourTable
WHERE Coverage IN ("CL", "AP")

This will guarantee the Policies are not listed twice, but is a waste
of time if there is truly no chance of having both CL and AP for a
policy.

You can also see the Policy and whether it has CL or AP if desired.

SELECT [Policy Number], Coverage
FROM YourTable
WHERE Coverage IN ("CL", "AP")

Do you need to see those that do NOT have CL or AP?

SELECT DISTINCT [Policy Number]
FROM YourTable T
WHERE NOT EXISTS (SELECT * FROM YourTable T1
WHERE T1.[Policy Number] = T.[Policy Number]
AND T1.Coverage IN ("CL", "AP"))

In each query above, substitute the actual name of your table where it
says YourTable.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


I have a table with two fields:

Policy Number and Coverage.

For each policy number, there are several records with
different coverages.

Example:

Policy Number Coverage
PP123 CL
PP123 CM
PP123 BI

PP124 BI
PP124 AP
PP124 UM
PP124 OP

"CL" and "AP" cannot exist for the same policy at the same
time. Now, for each policy, I want to check whether it has
a coverage called "CL" or "AP".
How should I do this in a query?

Thanks a lot...

Wei
Click to expand...

.
Click to expand...
 
Dear Wei:

It is the same as saying:

WHERE Coverage = "CL" OR Coverage = "AP"

It saves wear and tear on the brain, expecially where other filters
may exist. Mixing AND and OR in a criteria statement becomes complex
logic in a hurry! The Query Design Grid often makes this even worse.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Thank you very much Tom! It works!

What does the "IN("CL", "AP")" do?

Thanks again
Wei
-----Original Message-----
Dear Wei:

For a list of all Policy Numbers that DO have either CL or AP, you can
start with this:

SELECT [Policy Number]
FROM YourTable
WHERE Coverage IN ("CL", "AP")

However, this could list a policy twice if it had both CL and AP. You
said this couldn't happen, so maybe that is OK as it is, or you could
make a slight change:

SELECT DISTINCT [Policy Number]
FROM YourTable
WHERE Coverage IN ("CL", "AP")

This will guarantee the Policies are not listed twice, but is a waste
of time if there is truly no chance of having both CL and AP for a
policy.

You can also see the Policy and whether it has CL or AP if desired.

SELECT [Policy Number], Coverage
FROM YourTable
WHERE Coverage IN ("CL", "AP")

Do you need to see those that do NOT have CL or AP?

SELECT DISTINCT [Policy Number]
FROM YourTable T
WHERE NOT EXISTS (SELECT * FROM YourTable T1
WHERE T1.[Policy Number] = T.[Policy Number]
AND T1.Coverage IN ("CL", "AP"))

In each query above, substitute the actual name of your table where it
says YourTable.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


I have a table with two fields:

Policy Number and Coverage.

For each policy number, there are several records with
different coverages.

Example:

Policy Number Coverage
PP123 CL
PP123 CM
PP123 BI

PP124 BI
PP124 AP
PP124 UM
PP124 OP

"CL" and "AP" cannot exist for the same policy at the same
time. Now, for each policy, I want to check whether it has
a coverage called "CL" or "AP".
How should I do this in a query?

Thanks a lot...

Wei
Click to expand...

.
Click to expand...
Click to expand...
 
Back
Top