Summing the Record Count from Multiple Tables

[Guest]

I have a table named tblBatchHeader with a primary key called
idsBatchHeaderID. It is linked to the primary key lngzBatchHeaderID in eight
other tables named tblCLines, tblDLines... etc. using a
one-many-relationship. For each instance of idsBatchHeaderID, I want to count
the number of records in each of these other tables and add the total of them
all together using a single select statement. For example, idsbatchHeaderID
345 has two tables that contain date - tblCLines has 4 records and tblDLines
has 6 records. I want a select statment that will return 10 which is the
total of both of them.

 

[Jamie Collins]

I have a table named tblBatchHeader with a primary key called
idsBatchHeaderID. It is linked to the primary key lngzBatchHeaderID in eight
other tables named tblCLines, tblDLines... etc.

For each instance of idsBatchHeaderID, I want to count
the number of records in each of these other tables and add the total of them
all together using a single select statement. For example, idsbatchHeaderID
345 has two tables that contain date - tblCLines has 4 records and tblDLines
has 6 records. I want a select statment that will return 10 which is the
total of both of them.

Something like:

SELECT SUM(DT1.tally) AS total_tally
FROM
(
SELECT COUNT(*) AS tally
FROM tblCLines
WHERE lngzBatchHeaderID = 345
UNION ALL
SELECT COUNT(*)
FROM tblDLines
WHERE lngzBatchHeaderID = 345
UNION ALL
SELECT COUNT(*)
FROM tblELines
WHERE lngzBatchHeaderID = 345
) AS DT1;

Extend the UNION ALLs to include all eight tables.

Of coure, you can use a parameter in place of a hard-coded value of 345
e.g.

CREATE PROCEDURE TestProc (
arg_ID INTEGER
) AS
SELECT SUM(DT1.tally) AS total_tally
FROM
(
SELECT COUNT(*) AS tally
FROM tblCLines
WHERE lngzBatchHeaderID = arg_ID
UNION ALL
SELECT COUNT(*)
FROM tblDLines
WHERE lngzBatchHeaderID = arg_ID
UNION ALL
SELECT COUNT(*)
FROM tblELines
WHERE lngzBatchHeaderID = arg_ID
) AS DT1;

Jamie.

--

 

[Gary Walter]

I have a table named tblBatchHeader with a primary key called
idsBatchHeaderID. It is linked to the primary key lngzBatchHeaderID in
eight
other tables named tblCLines, tblDLines... etc. using a
one-many-relationship. For each instance of idsBatchHeaderID, I want to
count
the number of records in each of these other tables and add the total of
them
all together using a single select statement. For example,
idsbatchHeaderID
345 has two tables that contain date - tblCLines has 4 records and
tblDLines
has 6 records. I want a select statment that will return 10 which is the
total of both of them.

If I understand correctly...

Bring all 9 tables into query designer

Give each table an alias by right-mouse
clicking on each table and choosing "Properties"
then changing "Alias"...for example:

tblBatchHeader M
tblCLines C
tblDLines D

LEFT JOIN M.idsBatchHeaderID
to each "id" in other 8 tables

{M-->C example:
drag and drop M.idsBatchHeaderID
over on C.lngzBatchHeaderID...
right-mouse click on join line,
choose "Properties" and choose
option that includes all records from
M and only matching from C

do same for M-->D, etc.}

double-click on every "id" to send them down
to the grid (double-click on M "id" twice)

change query from SELECT to GROUP BY
query by clicking on sigma icon in top menu

leave "Totals" row of one column of M "id" as "GroupBy"

Field: idsBatchHeaderID
Table: M
Total: Group By
Sort:
Show:
Criteria:
or:

change all other columns "Totals" row from
"Group By" to "Count"

give each count column an alias

Field: TotalCnt: idsBatchHeaderID
Table: M
Total: Count
Sort:
Show:
Criteria:
or:

Field: CCnt: lngzBatchHeaderID
Table: C
Total: Count
Sort:
Show:
Criteria:
or:

Field: DCnt: lngzBatchHeaderID
Table: D
Total: Count
Sort:
Show:
Criteria:
or:

loosely speaking, here is what we've done...

1) built a group for *every* M.idsBatchHeaderID
(we get every M "id" with LEFT JOIN)

2) for each specific group, include matching "id's"
from other tables

3) within each group, count the total number
of records in the group by counting M "id"

4) with each group, count the contribution of
the other tables by counting their "id's"

Note:

if each tblxLines has the same structure,
a proper design would typically have only
*one* "tblLines" with one extra field to
differentiate "C" from "D," etc.

 

[Gary Walter]

sorry...on further thought what I gave
you would only give correct counts for
each line table, but not "total count"...

you have have to run additional query on
the previousquery I gave you where you add

CCnt + DCnt +....

to get "TotalCnt"

SELECT
q.idsBatchHeaderID,
q.CCnt,
q.DCnt,
[CCnt]+[DCnt] AS TotalCnt
FROM previousquery As q;


-------------

or you would have to "normalize" your
line tables (like Jamie said) with a
UNION query....something like:

qryunLineTables

SELECT
"C" As LineTbl,
lngzBatchHeaderID As fkey
FROM
tblCLines
UNION ALL
SELECT
"D",
lngzBatchHeaderID
FROM
tblDLines
UNION ALL
SELECT
"E",
lngzBatchHeaderID
FROM
tblELines
UNION ALL
SELECT
"F",
lngzBatchHeaderID
FROM
tblFLines

<etc (repeating for all 8 tables)>

then join tblBatchHeader to union
query to get all counts

SELECT
M.idsBatchHeaderID,
Count(M.idsBatchHeaderID) AS TotalCnt,
Sum(Abs(Q.LineTbl="C")) AS CCnt,
Sum(Abs(Q.LineTbl="D")) AS DCnt,
Sum(Abs(Q.LineTbl="E")) AS ECnt,
Sum(Abs(Q.LineTbl="F")) AS FCnt
FROM
tblBatchHeader As M
INNER JOIN
qryunLineTables As Q

 

[Guest]

Jamie, I tried your query and it worked well when I enter a single value for
(i.e. lngzBatchHeaderID = 345) but I would like the query to return a value
for each instance of the idsBatchHeaderID in tblBatchHeader table. Is there a
way to modify your SQL statement to accomodate this?

 

[Guest]

This worked well when I entered the value (i.e. lngzBatchHeaderID = 345) but
I won't always know this value. So is there are way to use your query and get
a result from every instance of idsBatchHeaderID in tblBatchHeader.

 

[Guest]

Thank You. These two queries did work and provided the result I was looking
for but I have no idea what it is doing can you explain further?

sorry...on further thought what I gave
you would only give correct counts for
each line table, but not "total count"...

you have have to run additional query on
the previousquery I gave you where you add

CCnt + DCnt +....

to get "TotalCnt"

SELECT
q.idsBatchHeaderID,
q.CCnt,
q.DCnt,
[CCnt]+[DCnt] AS TotalCnt
FROM previousquery As q;


-------------

or you would have to "normalize" your
line tables (like Jamie said) with a
UNION query....something like:

qryunLineTables

SELECT
"C" As LineTbl,
lngzBatchHeaderID As fkey
FROM
tblCLines
UNION ALL
SELECT
"D",
lngzBatchHeaderID
FROM
tblDLines
UNION ALL
SELECT
"E",
lngzBatchHeaderID
FROM
tblELines
UNION ALL
SELECT
"F",
lngzBatchHeaderID
FROM
tblFLines

<etc (repeating for all 8 tables)>

then join tblBatchHeader to union
query to get all counts

SELECT
M.idsBatchHeaderID,
Count(M.idsBatchHeaderID) AS TotalCnt,
Sum(Abs(Q.LineTbl="C")) AS CCnt,
Sum(Abs(Q.LineTbl="D")) AS DCnt,
Sum(Abs(Q.LineTbl="E")) AS ECnt,
Sum(Abs(Q.LineTbl="F")) AS FCnt
FROM
tblBatchHeader As M
INNER JOIN
qryunLineTables As Q

If I understand correctly...

Bring all 9 tables into query designer

Give each table an alias by right-mouse
clicking on each table and choosing "Properties"
then changing "Alias"...for example:

tblBatchHeader M
tblCLines C
tblDLines D

LEFT JOIN M.idsBatchHeaderID
to each "id" in other 8 tables

{M-->C example:
drag and drop M.idsBatchHeaderID
over on C.lngzBatchHeaderID...
right-mouse click on join line,
choose "Properties" and choose
option that includes all records from
M and only matching from C

do same for M-->D, etc.}

double-click on every "id" to send them down
to the grid (double-click on M "id" twice)

change query from SELECT to GROUP BY
query by clicking on sigma icon in top menu

leave "Totals" row of one column of M "id" as "GroupBy"

Field: idsBatchHeaderID
Table: M
Total: Group By
Sort:
Show:
Criteria:
or:

change all other columns "Totals" row from
"Group By" to "Count"

give each count column an alias

Field: TotalCnt: idsBatchHeaderID
Table: M
Total: Count
Sort:
Show:
Criteria:
or:

Field: CCnt: lngzBatchHeaderID
Table: C
Total: Count
Sort:
Show:
Criteria:
or:

Field: DCnt: lngzBatchHeaderID
Table: D
Total: Count
Sort:
Show:
Criteria:
or:

loosely speaking, here is what we've done...

1) built a group for *every* M.idsBatchHeaderID
(we get every M "id" with LEFT JOIN)

2) for each specific group, include matching "id's"
from other tables

3) within each group, count the total number
of records in the group by counting M "id"

4) with each group, count the contribution of
the other tables by counting their "id's"

Note:

if each tblxLines has the same structure,
a proper design would typically have only
*one* "tblLines" with one extra field to
differentiate "C" from "D," etc.

 

[Jamie Collins]

is there are way to use your query and get
a result from every instance of idsBatchHeaderID in tblBatchHeader.

SELECT DT1.lngzBatchHeaderID,
SUM(DT1.tally) AS total_tally
FROM
(
SELECT lngzBatchHeaderID, COUNT(*) AS tally
FROM tblCLines
GROUP BY lngzBatchHeaderID
UNION ALL
SELECT lngzBatchHeaderID, COUNT(*)
FROM tblDLines
GROUP BY lngzBatchHeaderID
UNION ALL
SELECT lngzBatchHeaderID, COUNT(*)
FROM tblELines
GROUP BY lngzBatchHeaderID
) AS DT1
GROUP BY DT1.lngzBatchHeaderID;

Jamie.

--

 

[Guest]

Here is the final code and it did work! At leadt temporarily while I was
running it in SQL mode but after saving the query to the name qrySumNCT, I am
now getting following error when I try to go into design mode. "The Micorsoft
Jet engine cannot find the input table or query 'Select
DT1.lngzBatchHeaderID...' Make sure it exists and the the table or query is
spelled correctly. (Error 3078). Any ideas what I did wrong?

SELECT DT1.lngzBatchHeaderID,
SUM(DT1.tally) AS total_tally
FROM
(
SELECT lngzBatchHeaderID, COUNT(*) AS tally
FROM tblCLines
GROUP BY lngzBatchHeaderID
UNION ALL
SELECT lngzBatchHeaderID, COUNT(*)
FROM tblDLines
GROUP BY lngzBatchHeaderID
UNION ALL
SELECT lngzBatchHeaderID, COUNT(*)
FROM tblMLines
GROUP BY lngzBatchHeaderID
UNION ALL
SELECT lngzBatchHeaderID, COUNT(*)
FROM tblNLines
GROUP BY lngzBatchHeaderID
UNION ALL
SELECT lngzBatchHeaderID, COUNT(*)
FROM tbPLines
GROUP BY lngzBatchHeaderID
UNION ALL
SELECT lngzBatchHeaderID, COUNT(*)
FROM tblSCLines
GROUP BY lngzBatchHeaderID
UNION ALL
SELECT lngzBatchHeaderID, COUNT(*)
FROM tblSPLines
GROUP BY lngzBatchHeaderID
UNION ALL
SELECT lngzBatchHeaderID, COUNT(*)
FROM tblTLines
GROUP BY lngzBatchHeaderID
) AS DT1
GROUP BY DT1.lngzBatchHeaderID;
This worked but after saving it as a query I am getting an error message

 

[Guest]

Sorry that error message happens when I try to Open the query. I am getting a
error "The expression you entered exceeds the 1,024-character limit for query
Design grid." Thoughts

 

[Jamie Collins]

after saving the query to the name qrySumNCT, I am
now getting following error when I try to go into design mode. "The Micorsoft
Jet engine cannot find the input table or query 'Select
DT1.lngzBatchHeaderID...' Make sure it exists and the the table or query is
spelled correctly. (Error 3078). Any ideas what I did wrong?

Opening it in the query builder is probably what you did wrong. My
recollection of the Access UI is that if you wrote the query in SQL the
default 'design mode' view is the 'SQL view'. If this is not the case
then... don't open it in the query builder, unless you can explicitly
specify the 'SQL view'.

Jamie.

--

 

[Guest]

Thanks for your help on this query. I got it to work but do not understand
exactly what the script is doing. Can you elaborate?

sorry...on further thought what I gave
you would only give correct counts for
each line table, but not "total count"...

you have have to run additional query on
the previousquery I gave you where you add

CCnt + DCnt +....

to get "TotalCnt"

SELECT
q.idsBatchHeaderID,
q.CCnt,
q.DCnt,
[CCnt]+[DCnt] AS TotalCnt
FROM previousquery As q;


-------------

or you would have to "normalize" your
line tables (like Jamie said) with a
UNION query....something like:

qryunLineTables

SELECT
"C" As LineTbl,
lngzBatchHeaderID As fkey
FROM
tblCLines
UNION ALL
SELECT
"D",
lngzBatchHeaderID
FROM
tblDLines
UNION ALL
SELECT
"E",
lngzBatchHeaderID
FROM
tblELines
UNION ALL
SELECT
"F",
lngzBatchHeaderID
FROM
tblFLines

<etc (repeating for all 8 tables)>

then join tblBatchHeader to union
query to get all counts

SELECT
M.idsBatchHeaderID,
Count(M.idsBatchHeaderID) AS TotalCnt,
Sum(Abs(Q.LineTbl="C")) AS CCnt,
Sum(Abs(Q.LineTbl="D")) AS DCnt,
Sum(Abs(Q.LineTbl="E")) AS ECnt,
Sum(Abs(Q.LineTbl="F")) AS FCnt
FROM
tblBatchHeader As M
INNER JOIN
qryunLineTables As Q

If I understand correctly...

Bring all 9 tables into query designer

Give each table an alias by right-mouse
clicking on each table and choosing "Properties"
then changing "Alias"...for example:

tblBatchHeader M
tblCLines C
tblDLines D

LEFT JOIN M.idsBatchHeaderID
to each "id" in other 8 tables

{M-->C example:
drag and drop M.idsBatchHeaderID
over on C.lngzBatchHeaderID...
right-mouse click on join line,
choose "Properties" and choose
option that includes all records from
M and only matching from C

do same for M-->D, etc.}

double-click on every "id" to send them down
to the grid (double-click on M "id" twice)

change query from SELECT to GROUP BY
query by clicking on sigma icon in top menu

leave "Totals" row of one column of M "id" as "GroupBy"

Field: idsBatchHeaderID
Table: M
Total: Group By
Sort:
Show:
Criteria:
or:

change all other columns "Totals" row from
"Group By" to "Count"

give each count column an alias

Field: TotalCnt: idsBatchHeaderID
Table: M
Total: Count
Sort:
Show:
Criteria:
or:

Field: CCnt: lngzBatchHeaderID
Table: C
Total: Count
Sort:
Show:
Criteria:
or:

Field: DCnt: lngzBatchHeaderID
Table: D
Total: Count
Sort:
Show:
Criteria:
or:

loosely speaking, here is what we've done...

1) built a group for *every* M.idsBatchHeaderID
(we get every M "id" with LEFT JOIN)

2) for each specific group, include matching "id's"
from other tables

3) within each group, count the total number
of records in the group by counting M "id"

4) with each group, count the contribution of
the other tables by counting their "id's"

Note:

if each tblxLines has the same structure,
a proper design would typically have only
*one* "tblLines" with one extra field to
differentiate "C" from "D," etc.

 

[Guest]

This WORKED GREAT!! Thanks again. Can I ask you another question? I am trying
to create a query that will concatenate all of the fields in the
tblBatchHeader and each of the tbl(X)Lines. I then want the results so that
it looks like the following:

idsBatch
HeaderID Batch Header
233 $X685;908 Data Processing;B;20060729;;;6;13;;15001;;5023;;1
233 C;1000394843;ATIM;0001
233 D;1000298373;20060725;3;
233 L;1000293878;234688;20060723;2;4951;;;;;;
233 M;1002093387;I;M;;;;;;;;;;
233 N;1029386633;20060730;203B;;C3;;EMER;;E
233 P;1000002938;239384;20060725;5023;
233 S;C;1000293877;A;2;20060729;323434;2343;;
233 S;P;1000399376;A;I;2;;;;
233 T;1002928365;20060728;205b;;C4;;DXTX;;O
234 $X686;736 Laboratory;B;20060729;;;;3;;;;;;
234 C;1000293847;ACHR;03
234 D;1999999999;20060729;2;1
234 T;1000002938;20060729;203B;;C4;;EMER;;E
235 $X687;840 Medical Records;B;20060729;;;1;2;;;;;;
235 L;1000293878;887654;20060731;1;;;;;;;
235 P;1000298377;349585;20060725;;
235 S;C;1000293888;C;2;20060723;;;;

I actually only need the BatchHeader column because I put this in a report
and export it. Any thought on how I can achieve this? If you want to see my
select statement you can find it under the question entitled "Query
Challenges" submitted 8/5/2006 12:30 PM PST

sorry...on further thought what I gave
you would only give correct counts for
each line table, but not "total count"...

you have have to run additional query on
the previousquery I gave you where you add

CCnt + DCnt +....

to get "TotalCnt"

SELECT
q.idsBatchHeaderID,
q.CCnt,
q.DCnt,
[CCnt]+[DCnt] AS TotalCnt
FROM previousquery As q;


-------------

or you would have to "normalize" your
line tables (like Jamie said) with a
UNION query....something like:

qryunLineTables

SELECT
"C" As LineTbl,
lngzBatchHeaderID As fkey
FROM
tblCLines
UNION ALL
SELECT
"D",
lngzBatchHeaderID
FROM
tblDLines
UNION ALL
SELECT
"E",
lngzBatchHeaderID
FROM
tblELines
UNION ALL
SELECT
"F",
lngzBatchHeaderID
FROM
tblFLines

<etc (repeating for all 8 tables)>

then join tblBatchHeader to union
query to get all counts

SELECT
M.idsBatchHeaderID,
Count(M.idsBatchHeaderID) AS TotalCnt,
Sum(Abs(Q.LineTbl="C")) AS CCnt,
Sum(Abs(Q.LineTbl="D")) AS DCnt,
Sum(Abs(Q.LineTbl="E")) AS ECnt,
Sum(Abs(Q.LineTbl="F")) AS FCnt
FROM
tblBatchHeader As M
INNER JOIN
qryunLineTables As Q

If I understand correctly...

Bring all 9 tables into query designer

Give each table an alias by right-mouse
clicking on each table and choosing "Properties"
then changing "Alias"...for example:

tblBatchHeader M
tblCLines C
tblDLines D

LEFT JOIN M.idsBatchHeaderID
to each "id" in other 8 tables

{M-->C example:
drag and drop M.idsBatchHeaderID
over on C.lngzBatchHeaderID...
right-mouse click on join line,
choose "Properties" and choose
option that includes all records from
M and only matching from C

do same for M-->D, etc.}

double-click on every "id" to send them down
to the grid (double-click on M "id" twice)

change query from SELECT to GROUP BY
query by clicking on sigma icon in top menu

leave "Totals" row of one column of M "id" as "GroupBy"

Field: idsBatchHeaderID
Table: M
Total: Group By
Sort:
Show:
Criteria:
or:

change all other columns "Totals" row from
"Group By" to "Count"

give each count column an alias

Field: TotalCnt: idsBatchHeaderID
Table: M
Total: Count
Sort:
Show:
Criteria:
or:

Field: CCnt: lngzBatchHeaderID
Table: C
Total: Count
Sort:
Show:
Criteria:
or:

Field: DCnt: lngzBatchHeaderID
Table: D
Total: Count
Sort:
Show:
Criteria:
or:

loosely speaking, here is what we've done...

1) built a group for *every* M.idsBatchHeaderID
(we get every M "id" with LEFT JOIN)

2) for each specific group, include matching "id's"
from other tables

3) within each group, count the total number
of records in the group by counting M "id"

4) with each group, count the contribution of
the other tables by counting their "id's"

Note:

if each tblxLines has the same structure,
a proper design would typically have only
*one* "tblLines" with one extra field to
differentiate "C" from "D," etc.

 

[Guest]

Hi Jamie:

Can you help me with the query you gave me the other day? I am using the
query you provided below which works great by itself but when I concatenate
it to main SELECT statement as a subquery, I am getting a message that says
"at most one record can be returned by this subquery. Below is the
concatenated result I am trying to achieve. All SELECT statements work except
3. In other words, if I remove it the select statement 3 it runs and I get
the results below minus the "11;" but when I add it in I get the error. What
can I do to resolve this problem?

|SELECT Statement 1 |2|3 | 4 | 5 |6
$X685;908 DATA PROCESSING;B;20060729;;;9;11;28158;9573;;2

Here is the SELECT statement which concatenates all of the results. I have
added comments to clarify.

'SELECT Statement 1 works
SELECT BH.idsBatchHeaderID, [chrLabel] & ";" & UCASE([chrDescription]) & ";"
& [chrSystemCode] & ";" & FORMAT([dtmAssumeDate],"yyyymmdd") & ";;;" &

'SELECT Statement 2 works
(SELECT SUM([intFactors]) FROM tblLLines WHERE BH.idsBatchHeaderID =
L.lngzBatchHeaderID & ";" &

'SELECT Statement 3 does not work and is giving me grief.
SELECT SUM(DT1.tally) AS total_tally
FROM
(
SELECT lngzBatchHeaderID, COUNT(*) AS tally FROM tblCLines GROUP BY
lngzBatchHeaderID
UNION ALL SELECT lngzBatchHeaderID, COUNT(*) FROM tblDLines GROUP BY
lngzBatchHeaderID
UNION ALL SELECT lngzBatchHeaderID, COUNT(*) FROM tblLLines GROUP BY
lngzBatchHeaderID
UNION ALL SELECT lngzBatchHeaderID, COUNT(*) FROM tblMLines GROUP BY
lngzBatchHeaderID
UNION ALL SELECT lngzBatchHeaderID, COUNT(*) FROM tblNLines GROUP BY
lngzBatchHeaderID
UNION ALL SELECT lngzBatchHeaderID, COUNT(*) FROM tblPLines GROUP BY
lngzBatchHeaderID
UNION ALL SELECT lngzBatchHeaderID, COUNT(*) FROM tblSCLines GROUP BY
lngzBatchHeaderID
UNION ALL SELECT lngzBatchHeaderID, COUNT(*) FROM tblSPLines GROUP BY
lngzBatchHeaderID
UNION ALL SELECT lngzBatchHeaderID, COUNT(*) FROM tblTLines GROUP BY
lngzBatchHeaderID
) AS DT1
GROUP BY DT1.lngzBatchHeaderID; & ";" &

'SELECT Statement 4 works
(SELECT ROUND(SUM([sngAmount]*100),0) FROM tblLLines WHERE
BH.idsBatchHeaderID = L.lngzBatchHeaderID & ";" &

'SELECT Statement 5 works
(SELECT ROUND(SUM([sngPaymentAllowanceAmount])*100,0) FROM tblPLines WHERE
BH.idsBatchHeaderID = P.lngzBatchHeaderID & ";;" &

'SELECT Statement 6 works
(SELECT Count(*) FROM tblPLines WHERE BH.idsBatchHeaderID =
P.lngzBatchHeaderID AS [Batch Header]

FROM ((((((((tblBatchHeader AS BH LEFT JOIN tblLLines AS L ON
BH.idsBatchHeaderID = L.lngzBatchHeaderID)
LEFT JOIN tblCLines AS C ON BH.idsBatchHeaderID = C.lngzBatchHeaderID)
LEFT JOIN tblDLines AS D ON BH.idsBatchHeaderID = D.lngzBatchHeaderID)
LEFT JOIN tblMLines AS M ON BH.idsBatchHeaderID = M.lngzBatchHeaderID)
LEFT JOIN tblNLines AS N ON BH.idsBatchHeaderID = N.lngzBatchHeaderID)
LEFT JOIN tblPLines AS P ON BH.idsBatchHeaderID = P.lngzBatchHeaderID)
LEFT JOIN tblSCLines AS SC ON BH.idsBatchHeaderID = SC.lngzBatchHeaderID)
LEFT JOIN tblSPLines AS SP ON BH.idsBatchHeaderID = SP.lngzBatchHeaderID)
LEFT JOIN tblTLines AS T ON BH.idsBatchHeaderID = T.lngzBatchHeaderID;

 

[Jamie Collins]

I am using the
query you provided below which works great by itself but when I concatenate
it to main SELECT statement as a subquery, I am getting a message that says
"at most one record can be returned by this subquery.

Stripped down to the main table and the subquery, it should look
something like this:

SELECT BH.lngzBatchHeaderID,
(
SELECT SUM(DT1.tally) AS total_tally
FROM
(
<<subquery snipped>>
) AS DT1
WHERE BH.lngzBatchHeaderID = DT1.lngzBatchHeaderID
) AS subquery_value
FROM tblBatchHeader AS BH;

Jamie.

--

 

[Gary Walter]

Are you asking about how the UNION query works,
or how the (as John Vinson used to call it) "sneaky count" works?

SELECT
M.idsBatchHeaderID,
Count(M.idsBatchHeaderID) AS TotalCnt,
Sum(Abs(Q.LineTbl="C")) AS CCnt,
Sum(Abs(Q.LineTbl="D")) AS DCnt,
Sum(Abs(Q.LineTbl="E")) AS ECnt,
Sum(Abs(Q.LineTbl="F")) AS FCnt
FROM
tblBatchHeader As M
INNER JOIN
qryunLineTables As Q
ON M.idsBatchHeaderID = Q.fkey
GROUP BY M.idsBatchHeaderID;

In query above you first will get groups
for each idsBatchHeaderID.

Within each group there may some records
where LineTbl = "C"

the equality stmt

Q.LineTbl = "C"

for each record in a group
will either be True (-1) or False (0)

so the absolute value of the equality
will either be 1 or 0

so, if you *sum* those,
you will actually get the "sneaky count"
of all the records in the group
where LineTbl = "C"

Thanks for your help on this query. I got it to work but do not understand
exactly what the script is doing. Can you elaborate?

sorry...on further thought what I gave
you would only give correct counts for
each line table, but not "total count"...

you have have to run additional query on
the previousquery I gave you where you add

CCnt + DCnt +....

to get "TotalCnt"

SELECT
q.idsBatchHeaderID,
q.CCnt,
q.DCnt,
[CCnt]+[DCnt] AS TotalCnt
FROM previousquery As q;


-------------

or you would have to "normalize" your
line tables (like Jamie said) with a
UNION query....something like:

qryunLineTables

SELECT
"C" As LineTbl,
lngzBatchHeaderID As fkey
FROM
tblCLines
UNION ALL
SELECT
"D",
lngzBatchHeaderID
FROM
tblDLines
UNION ALL
SELECT
"E",
lngzBatchHeaderID
FROM
tblELines
UNION ALL
SELECT
"F",
lngzBatchHeaderID
FROM
tblFLines

<etc (repeating for all 8 tables)>

then join tblBatchHeader to union
query to get all counts

SELECT
M.idsBatchHeaderID,
Count(M.idsBatchHeaderID) AS TotalCnt,
Sum(Abs(Q.LineTbl="C")) AS CCnt,
Sum(Abs(Q.LineTbl="D")) AS DCnt,
Sum(Abs(Q.LineTbl="E")) AS ECnt,
Sum(Abs(Q.LineTbl="F")) AS FCnt
FROM
tblBatchHeader As M
INNER JOIN
qryunLineTables As Q

:
I have a table named tblBatchHeader with a primary key called
idsBatchHeaderID. It is linked to the primary key lngzBatchHeaderID in
eight
other tables named tblCLines, tblDLines... etc. using a
one-many-relationship. For each instance of idsBatchHeaderID, I want to
count
the number of records in each of these other tables and add the total of
them
all together using a single select statement. For example,
idsbatchHeaderID
345 has two tables that contain date - tblCLines has 4 records and
tblDLines
has 6 records. I want a select statment that will return 10 which is the
total of both of them.

If I understand correctly...

Bring all 9 tables into query designer

Give each table an alias by right-mouse
clicking on each table and choosing "Properties"
then changing "Alias"...for example:

tblBatchHeader M
tblCLines C
tblDLines D

LEFT JOIN M.idsBatchHeaderID
to each "id" in other 8 tables

{M-->C example:
drag and drop M.idsBatchHeaderID
over on C.lngzBatchHeaderID...
right-mouse click on join line,
choose "Properties" and choose
option that includes all records from
M and only matching from C

do same for M-->D, etc.}

double-click on every "id" to send them down
to the grid (double-click on M "id" twice)

change query from SELECT to GROUP BY
query by clicking on sigma icon in top menu

leave "Totals" row of one column of M "id" as "GroupBy"

Field: idsBatchHeaderID
Table: M
Total: Group By
Sort:
Show:
Criteria:
or:

change all other columns "Totals" row from
"Group By" to "Count"

give each count column an alias

Field: TotalCnt: idsBatchHeaderID
Table: M
Total: Count
Sort:
Show:
Criteria:
or:

Field: CCnt: lngzBatchHeaderID
Table: C
Total: Count
Sort:
Show:
Criteria:
or:

Field: DCnt: lngzBatchHeaderID
Table: D
Total: Count
Sort:
Show:
Criteria:
or:

loosely speaking, here is what we've done...

1) built a group for *every* M.idsBatchHeaderID
(we get every M "id" with LEFT JOIN)

2) for each specific group, include matching "id's"
from other tables

3) within each group, count the total number
of records in the group by counting M "id"

4) with each group, count the contribution of
the other tables by counting their "id's"

Note:

if each tblxLines has the same structure,
a proper design would typically have only
*one* "tblLines" with one extra field to
differentiate "C" from "D," etc.

 

[Guest]

I have learned so much from just the few queries that you have shown me. It
has been invaluable. Unfortunately, I am now getting an error "You have
written a subquery that can return more than one field without using the
EXISTS reserve work in the main query's FROM clause. Revise the SELECT
Statement of the subquery to request only one field." Any suggestions?

I made just one small change to your query to get it to run. The
tblBatchHeader primary key is "ids"BatchHeaderID and the tblXLines primary
keys are "lngz"BatchHeaderID.

Here is the query that I used.

(SELECT BH.idsBatchHeaderID,
(
SELECT Sum(DT1.tally) AS total_tally
FROM
(
SELECT lngzBatchHeaderID, COUNT(*) AS tally FROM tblCLines GROUP BY
lngzBatchHeaderID
UNION ALL SELECT lngzBatchHeaderID, COUNT(*) FROM tblDLines GROUP BY
lngzBatchHeaderID
UNION ALL SELECT lngzBatchHeaderID, COUNT(*) FROM tblLLines GROUP BY
lngzBatchHeaderID
UNION ALL SELECT lngzBatchHeaderID, COUNT(*) FROM tblMLines GROUP BY
lngzBatchHeaderID
UNION ALL SELECT lngzBatchHeaderID, COUNT(*) FROM tblNLines GROUP BY
lngzBatchHeaderID
UNION ALL SELECT lngzBatchHeaderID, COUNT(*) FROM tblPLines GROUP BY
lngzBatchHeaderID
UNION ALL SELECT lngzBatchHeaderID, COUNT(*) FROM tblSCLines GROUP BY
lngzBatchHeaderID
UNION ALL SELECT lngzBatchHeaderID, COUNT(*) FROM tblSPLines GROUP BY
lngzBatchHeaderID
UNION ALL SELECT lngzBatchHeaderID, COUNT(*) FROM tblTLines GROUP BY
lngzBatchHeaderID
) AS DT1
WHERE BH.idsBatchHeaderID = DT1.lngzBatchHeaderID
) AS subquery_value
FROM tblBatchHeader AS BH & ";" &

I have run the query by itself and it WORKs by returning the following
results which appear to be correct.

idsBatch
HeaderID subquery_value
233 11
234 2
235 3
236 1
237 4
238