Use a subquery to pull particular records including null fields

[RiesbeckP]

Hi All,

I have a DB where there are customer numbers and a few other fields. I
want to be able to pull all of the null records for a particular field
as well as all the other customer numbers that have null and non null
fields (see below).

CustNo Funding ClaimAmt Prod
123 R 100 PPO
123 R 200 Null Value
456 N 150 POS
456 N 125 Null Value

I want to be able to get all the customer numbers with null values for
the Prod field, but I want the other records associated with that
customer as well. How do I go about doing that? Thank you.

Paul

[Wayne Morgan]

By default all records will be returned. Are you wanting to limit CustNo to
only those records where CustNo is Null? If so, create a query and add this
table to the query. Add the desired fields to the query (i.e. the fields you
want to see). In the Criteria box for CustNo, add the criteria Is Null. Save
the query with a unique name and open the query. You should have all records
from the table where CustNo is null. Since no criteria was placed on the
other fields, there will be nothing limiting the records by what is in the
other fields, null or not null.

--
Wayne Morgan
MS Access MVP
<Ri*******@aetna.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

Hi All,

I have a DB where there are customer numbers and a few other fields. I
want to be able to pull all of the null records for a particular field
as well as all the other customer numbers that have null and non null
fields (see below).

CustNo Funding ClaimAmt Prod
123 R 100 PPO
123 R 200 Null Value
456 N 150 POS
456 N 125 Null Value

I want to be able to get all the customer numbers with null values for
the Prod field, but I want the other records associated with that
customer as well. How do I go about doing that? Thank you.

Paul