Totals Query with Multiple Counts

[Mark]

I'm trying to run a single query that will give me the count of records in
each of three one-to-many tables. The first table consists of claims, the
second of invoices and the third of line items. The relationship is such
that one claim will consist of several invoices and each invoices will
consist of several line items. I've built a totals query to give me a count
of claims, a count of invoices and a count of line items. The problem is
that each column pulls the same count. I changed the query to a Select
Distinct, but I still get the same results. Is there a way to get the three
distinct counts in one query, or do I need to run separate query for each set?
I run into this type of problem often, so if there's an efficient way to
handle it, I'd sure like to know.

Thanks in advance. I already REALLY appreciate it.

 

[Marshall Barton]

I'm trying to run a single query that will give me the count of records in
each of three one-to-many tables. The first table consists of claims, the
second of invoices and the third of line items. The relationship is such
that one claim will consist of several invoices and each invoices will
consist of several line items. I've built a totals query to give me a count
of claims, a count of invoices and a count of line items. The problem is
that each column pulls the same count. I changed the query to a Select
Distinct, but I still get the same results. Is there a way to get the three
distinct counts in one query, or do I need to run separate query for each set?
I run into this type of problem often, so if there's an efficient way to
handle it, I'd sure like to know.

What else is your query doing?

If the counts are the only results of the query, you could
use:

SELECT Count(*) As CountForTable1,
(SELECT Count(*) FROM Table2) As CountForTable2,
(SELECT Count(*) FROM Table3) As CountForTable3,
FROM Table1

 

[Mark]

I think part of the challenge is that this totals query is looking at a
select query where the three tables are combined. And the reason I have to
combine them is that I need to limit the results to a given year, and that
date value is stored in the parent table (claims).

 

[Marshall Barton]

And the tables are linked in what manner?

You will still need to use subqueries as I demonstrated.
with the complexities you've just outlined, I believe you
will need to use each subquery's Where clause to restrict it
to the records that are related to the main table tecord.
This may be pretty vague, but I think you want something
along these lines:

SELECT tblClaims.ClaimID, tblClaims.fieldA,
tblClaims.fieldB, tblClaims.fieldC,
Count(*) CountOfClaims,
(SELECT Count(*) FROM tblInvoices As X
WHERE X.ClaimID = tblInvoices.ClaimID
) As CountOfInvoices,
(SELECT Count(*) FROM tblLineItems As Y
WHERE Y.InvoiceID = tblLineItems.InvoiceID
) As CountOfLineItems
FROM tblClaims INNER JOIN
(tblInvoices INNER JOIN tblLineItems
ON tblLineItems.InvoiceID = tblInvoices.InvoiceID)
ON tblClaims.ClaimID = tblInvoices.ClaimID
GROUP BY tblClaims.ClaimID, tblClaims.fieldA,
tblClaims.fieldB, tblClaims.fieldC,
Count(*) CountOfClaims,
(SELECT Count(*) FROM tblInvoices As X
WHERE X.ClaimID = tblInvoices.ClaimID),
(SELECT Count(*) FROM tblLineItems As Y
WHERE Y.InvoiceID = tblLineItems.InvoiceID)