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

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

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?

$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;

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"