query drops records

[Daniel Collison]

I have created a report, which shows data related to insurance claims. The
report includes four different groupings: Service Category (mental health vs.
substance abuse claims); Provider; Region and Statewide.

The detail level of the report shows summary information (i.e. billed
charges, paid charges, etc.) related to procedures that are associated with
claims. A procedure will be allowed or not allowed.

For every procedure, the report should show an unduplicated count of clients
for allowed and unallowed claims. It should also show the unduplicated count
of clients associated with the procedure. See below:

Procedure Code Claim Status Unduplicated Client Count
90804 Allowed 124
Not Allowed 72
TOTAL 142

Note that the TOTAL is not the sum of allowed and not allowed because one
client can have both allowed and unallowed claims. Similarly, one client can
be represented between service categories, between providers and between
regions.

The report is based on a query, containing a number of sub-queries that
provide unduplicated client counts at each group level. I have tested the
unduplicated client totals in these sub-queries against the dataset and they
exactly match client counts in the data. The client totals from three
separate queries are pulled into one query [qry Category], which is then
pulled into the report query [qry Claim Report].

Problem: If a provider has no unallowed claims in a service category, then
the query/report drops allowed claims associated with a service category.
Client counts for Provider X might be


Service Category Client Count Allowed Claims Client Count Unallowed
Mental Health 1 3
Substance Abuse 3 0
ALL 3 3

In “qry Categoryâ€, the record for Provider X shows no substance abuse client
counts because there are no unallowed claims associated with this service
category.

I tried eliminating qry Category and pulling into qry Claim Report each of
the underlying queries to qry Category. However, I yet get the same results
in my report.

Any suggestions how to remedy this problem?

 

[Jerry Whittle]

Open the query in design view. Double click on a join line between the
tables/queries. Try the second option. If that doesn't work, try the third
option. Might even have to try different combinations if you have more than
two tables or queries in the final query.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have created a report, which shows data related to insurance claims. The
report includes four different groupings: Service Category (mental health vs.
substance abuse claims); Provider; Region and Statewide.

The detail level of the report shows summary information (i.e. billed
charges, paid charges, etc.) related to procedures that are associated with
claims. A procedure will be allowed or not allowed.

For every procedure, the report should show an unduplicated count of clients
for allowed and unallowed claims. It should also show the unduplicated count
of clients associated with the procedure. See below:

Procedure Code Claim Status Unduplicated Client Count
90804 Allowed 124
Not Allowed 72
TOTAL 142

Note that the TOTAL is not the sum of allowed and not allowed because one
client can have both allowed and unallowed claims. Similarly, one client can
be represented between service categories, between providers and between
regions.

The report is based on a query, containing a number of sub-queries that
provide unduplicated client counts at each group level. I have tested the
unduplicated client totals in these sub-queries against the dataset and they
exactly match client counts in the data. The client totals from three
separate queries are pulled into one query [qry Category], which is then
pulled into the report query [qry Claim Report].

Problem: If a provider has no unallowed claims in a service category, then
the query/report drops allowed claims associated with a service category.
Client counts for Provider X might be


Service Category Client Count Allowed Claims Client Count Unallowed
Mental Health 1 3
Substance Abuse 3 0
ALL 3 3

In “qry Categoryâ€, the record for Provider X shows no substance abuse client
counts because there are no unallowed claims associated with this service
category.

I tried eliminating qry Category and pulling into qry Claim Report each of
the underlying queries to qry Category. However, I yet get the same results
in my report.

Any suggestions how to remedy this problem?

 

[Daniel Collison]

Jerry:

Thanks for your assistance. Your guess was correct...correcting the join
properties in the query fixed the problem!

Again, I'm most appreciative.

Daniel

Open the query in design view. Double click on a join line between the
tables/queries. Try the second option. If that doesn't work, try the third
option. Might even have to try different combinations if you have more than
two tables or queries in the final query.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have created a report, which shows data related to insurance claims. The
report includes four different groupings: Service Category (mental health vs.
substance abuse claims); Provider; Region and Statewide.

The detail level of the report shows summary information (i.e. billed
charges, paid charges, etc.) related to procedures that are associated with
claims. A procedure will be allowed or not allowed.

For every procedure, the report should show an unduplicated count of clients
for allowed and unallowed claims. It should also show the unduplicated count
of clients associated with the procedure. See below:

Procedure Code Claim Status Unduplicated Client Count
90804 Allowed 124
Not Allowed 72
TOTAL 142

Note that the TOTAL is not the sum of allowed and not allowed because one
client can have both allowed and unallowed claims. Similarly, one client can
be represented between service categories, between providers and between
regions.

The report is based on a query, containing a number of sub-queries that
provide unduplicated client counts at each group level. I have tested the
unduplicated client totals in these sub-queries against the dataset and they
exactly match client counts in the data. The client totals from three
separate queries are pulled into one query [qry Category], which is then
pulled into the report query [qry Claim Report].

Problem: If a provider has no unallowed claims in a service category, then
the query/report drops allowed claims associated with a service category.
Client counts for Provider X might be


Service Category Client Count Allowed Claims Client Count Unallowed
Mental Health 1 3
Substance Abuse 3 0
ALL 3 3

In “qry Categoryâ€, the record for Provider X shows no substance abuse client
counts because there are no unallowed claims associated with this service
category.

I tried eliminating qry Category and pulling into qry Claim Report each of
the underlying queries to qry Category. However, I yet get the same results
in my report.

Any suggestions how to remedy this problem?