Query Challenges

[Mary]

I have a table called tblBatchHeader and nine other tables called tbl<X>Lines
tables (where <X> is C, D, L, M, N, etc.). So the tblBatchHeader contains
fields that will make up a Batch Header and each of the "Lines" tables
contains fields that represent a different Line type/format. The Primary Key
idsBatchHeaderID in the tblBatchHeader is linked as a one-to-many using the
lngzBatchHeaderID in the "Lines" tables. This has become quit a complex
query. At least for me. Some great Access query experts Gary Walters and
Jamie Collins from this discussion group helped me get it to where it is now
but it still needs some tweeking.

Here is the query:

SELECT [idsBatchHeaderID], [chrLabel] & ";" & [chrDescription] & ";" &
[chrSystemCode] & ";" & Format([dtmAssumeDate],"yyyymmdd") & ";;;" &
(SELECT SUM([intFactors]) FROM tblLLines WHERE
tblBatchHeader.idsBatchHeaderID = tblLLines.lngzBatchHeaderID & ";" &
(SELECT SUM(DT1.tally) AS 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 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 & ";;" &
(SELECT ROUND(SUM([sngAmount]*100),0) FROM tblLLines WHERE
tblBatchHeader.idsBatchHeaderID = tblLLines.lngzBatchHeaderID & ";;" &
(SELECT ROUND(SUM([sngPaymentAllowanceAmount])*100,0) FROM tblPLines
WHERE tblBatchHeader.idsBatchHeaderID = tblPLines.lngzBatchHeaderID & ";;"
&
(SELECT Count(*) FROM tblPLines WHERE tblBatchHeader.idsBatchHeaderID =
tblPLines.lngzBatchHeaderID AS [Batch Header]
FROM (((((((((tblBatchHeader INNER JOIN tblCLines ON
tblBatchHeader.idsBatchHeaderID = tblCLines.lngzBatchHeaderID)
INNER JOIN tblDLines ON tblBatchHeader.idsBatchHeaderID =
tblDLines.lngzBatchHeaderID)
INNER JOIN tblLLines ON tblBatchHeader.idsBatchHeaderID =
tblLLines.lngzBatchHeaderID)
INNER JOIN tblMLines ON tblBatchHeader.idsBatchHeaderID =
tblMLines.lngzBatchHeaderID)
INNER JOIN tblNLines ON tblBatchHeader.idsBatchHeaderID =
tblNLines.lngzBatchHeaderID)
INNER JOIN tblPLines ON tblBatchHeader.idsBatchHeaderID =
tblPLines.lngzBatchHeaderID)
INNER JOIN tblSCLines ON tblBatchHeader.idsBatchHeaderID =
tblSCLines.lngzBatchHeaderID)
INNER JOIN tblSPLines ON tblBatchHeader.idsBatchHeaderID =
tblSPLines.lngzBatchHeaderID)
INNER JOIN tblTLines ON tblBatchHeader.idsBatchHeaderID =
tblTLines.lngzBatchHeaderID);

UNION SELECT [idsBatchHeaderID], [chrTransCode1] & ";" & [lngzPatientNumber]
& ";" & [chrFieldCodes] & ";" & [chrNewData] as [Lines]
FROM tblBatchHeader INNER JOIN tblCLines ON tblBatchHeader.idsBatchHeaderID
= tblCLines.lngzBatchHeaderID;

UNION SELECT [idsBatchHeaderID], [chrTransCode1] & ";" & [lngzPatientNumber]
& ";" & format( [dtmDischargeDate],"YYYYMMDD") & ";" &
[intDischargeStatusCode] & ";" & [intDischargeHour] as [Lines]
FROM tblBatchHeader INNER JOIN tblDLines ON tblBatchHeader.idsBatchHeaderID
= tblDLines.lngzBatchHeaderID;

UNION SELECT [idsBatchHeaderID], [chrTransCode1] & ";" & [lngzPatientNumber]
& ";" & [lngzProcedureCode] & ";" & format([dtmServiceDate],"YYYYMMDD")
& ";" & [intFactors] & ";" & ROUND([sngAmount]*100,0) & ";;;;;;" &
UCase([chrPatientName]) as [Lines]
FROM tblBatchHeader INNER JOIN tblLLines ON tblBatchHeader.idsBatchHeaderID
= tblLLines.lngzBatchHeaderID;

UNION SELECT [idsBatchHeaderID], [chrTransCode1] & ";" & [lngzPatientNumber]
& ";" & [chrDiagnosisIndicator] & ";" & [chrSex] & ";" &
[chrDiagnosis1] & ";" & [chrDiagnosis2] & ";" & [chrDiagnosis3] & ";" &
[chrDiagnosis4] & ";" & [chrDiagnosis5] & ";" & [chrDiagnosis6] & ";" &
[chrDiagnosis7] & ";" & [chrDiagnosis8] & ";" & [chrDiagnosis9] & ";" &
[chrDiagnosis10] as [Lines]
FROM tblBatchHeader INNER JOIN tblMLines ON tblBatchHeader.idsBatchHeaderID
= tblMLines.lngzBatchHeaderID;

UNION SELECT [idsBatchHeaderID], [chrTransCode1] & ";" & [lngzPatientNumber]
& ";" & format( [dtmEffectiveDate],"YYYYMMDD") & ";" & [chrRoomNumber] &
";;" & [chrAccomodationCode] & ";;" & [chrServiceCode] & ";;" &
[chrPatientType] as [Lines]
FROM tblBatchHeader INNER JOIN tblNLines ON tblBatchHeader.idsBatchHeaderID
= tblNLines.lngzBatchHeaderID;

UNION SELECT [idsBatchHeaderID], [chrTransCode1] & ";" & [lngzPatientNumber]
& ";" & [lngzProcedureCode] & ";" & format([dtmPaymentDate],"YYYYMMDD")
& ";" & Round([sngPaymentAllowanceAmount]*100,0) & ";" &
[chrPaymentAllowanceComments] as [Lines]
FROM tblBatchHeader INNER JOIN tblPLines ON tblBatchHeader.idsBatchHeaderID
= tblPLines.lngzBatchHeaderID;

UNION SELECT [idsBatchHeaderID], [chrTransCode1] & ";" & [chrTransCode2] &
";" & [lngzPatientNumber] & ";" & [chrActionCode] & ";" &
[intSequenceNumber] & ";" & format([dtmSurgicalServiceDate],"YYYYMMDD") &
";" & [chrHCPCSCPTCode] & ";" & [intRevenueCode] & ";" & [chrAPCCode] &
";" & [intSurgeon] as [Lines]
FROM tblBatchHeader INNER JOIN tblSCLines ON tblBatchHeader.idsBatchHeaderID
= tblSCLines.lngzBatchHeaderID;

UNION SELECT [idsBatchHeaderID], [chrTransCode1] & ";" & [chrTransCode2] &
";" & [lngzPatientNumber] & ";" & [chrActionCode] & ";" &
[chrProcedureIndicator] & ";" & [intProcedureNumber] & ";" &
[intSurgicalProcedureCode] & ";" & format([dtmProcedureDate],"YYYYMMDD") &
";" & format([dtmProcedureTime],"HHMM") & ";" & [chrHCSNumber] as [Lines]
FROM tblBatchHeader INNER JOIN tblSPLines ON tblBatchHeader.idsBatchHeaderID
= tblSPLines.lngzBatchHeaderID;

UNION SELECT [idsBatchHeaderID], [chrTransCode1] & ";" &
[lngzPatientNumber] & ";" & format( [dtmEffectiveDate],"YYYYMMDD") & ";" &
[chrRoomNumber] & ";;" & [chrAccomodationCode] & ";;" & [chrServiceCode]
& ";;" & [chrPatientType] as [Lines]
FROM tblBatchHeader INNER JOIN tblTLines ON tblBatchHeader.idsBatchHeaderID
= tblTLines.lngzBatchHeaderID;

Here are the OUTPUT results when I run the query.

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 L;1000293883;234324;20060725;3;5050;;;;;;LARRY MILLER
233 L;1000293888;64345;20060729;1;5000;;;;;;
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
-->(Batch Header 234 should go in here i.e.:
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
-->(Batch Header 235 should go in here i.e.:
235 $X687;840 Medical Records;B;20060729;;;1;2;;;;;<--
235 L;1000293878;887654;20060731;1;;;;;;;MATT MOORE
235 P;1000298377;349585;20060725;;
235 S;C;1000293888;C;2;20060723;;;;

The three problems with the output are:
1. There should be two additional batch Headers for idsBatchHeaderID 234 and
235. (See above output with my notes in parens)
2. The third select statement counts the total number of records in each of
the "Lines" tables except the "L" lines (those beginning with "L;"). I need
the total to count and return only the number of lines associated with the
specific Batch Header. So in row one of the batchHeader output above that
starts $X685, the 13 is currently a count for all non-"L" lines for all Batch
Headers in the database but I need it to count only those records associated
with the each individual idsBatchHeaderID 233, 234, 235 etc. which in this
case is 8 for 233, 3 for 234 and 2 for 235 (count all 233 lines except the
"L" Lines). I manually inserted the Batch Headers in the results output above
to show where they should go.
3. This is query is getting pretty harry any suggestions on how to simplify.
Do I have stuff in it I do not need?

Thanks in advance for any feedback.