Help out with a difficult query?

[Keith Work]

I'm working on migrating about 2000 documents from one knowledge
management system to another and am having a hard time with 1 report.
Each record has (among other things) 1 of 3 units (categories)
assigned to it (EBI, Network or Mainframe) and a few different
statuses (which for arguments sake we can boil down to 3). I want the
report to look like this (the numbers are just examples):

Complete Incomplete Total
EBI 10 20 30
Network 15 15 30
Mainframe 20 10 30

Right now I have 3 queries set up. 1 calculates the total for each
unit, another calculates the complete for each unit, and the third
calculates the difference and displays my final table above.

There must be an easier (and more elegant) way though. Any ideas would
be greatly appreciated.

For those interested, here's the details:

The table I'm pulling from is tblKSMaster.
I'm counting the timestamp field becuase I know each record will have
a value in that field.

Here's the queries:

qrySubDataComplete:
SELECT tblKSMaster.AssignedUnit, Count(tblKSMaster.Timestamp) AS
CountOfTimestamp
FROM tblKSMaster
WHERE (((tblKSMaster.Status)="REWRITE_DONE" Or
(tblKSMaster.Status)="EDIT_DONE" Or (tblKSMaster.Status)="INVALID" Or
(tblKSMaster.Status)="PUBFOLDER"))
GROUP BY tblKSMaster.AssignedUnit
HAVING (((tblKSMaster.AssignedUnit)="network" Or
(tblKSMaster.AssignedUnit)="ebi" Or
(tblKSMaster.AssignedUnit)="mainframe"))
ORDER BY tblKSMaster.AssignedUnit;
qrySubDataTotal:
SELECT tblKSMaster.AssignedUnit, Count(tblKSMaster.Timestamp) AS
CountOfTimestamp
FROM tblKSMaster
GROUP BY tblKSMaster.AssignedUnit
HAVING (((tblKSMaster.AssignedUnit)="network" Or
(tblKSMaster.AssignedUnit)="ebi" Or
(tblKSMaster.AssignedUnit)="mainframe"))
ORDER BY tblKSMaster.AssignedUnit;
qryTotalCompleteByTeam:
SELECT qrySubDataComplete.AssignedUnit,
qrySubDataComplete.CountOfTimestamp AS Complete,
[qrySubDataTotal].[Countoftimestamp]-[qrySubDataComplete].[countoftimestamp]
AS Incomplete, qrySubDataTotal.CountOfTimestamp AS Total
FROM qrySubDataComplete INNER JOIN qrySubDataTotal ON
qrySubDataComplete.AssignedUnit = qrySubDataTotal.AssignedUnit;

Thanks!

Keith

[Salad]

Keith Work wrote:

I'm working on migrating about 2000 documents from one knowledge
management system to another and am having a hard time with 1 report.
Each record has (among other things) 1 of 3 units (categories)
assigned to it (EBI, Network or Mainframe) and a few different
statuses (which for arguments sake we can boil down to 3). I want the
report to look like this (the numbers are just examples):

Complete Incomplete Total
EBI 10 20 30
Network 15 15 30
Mainframe 20 10 30

Right now I have 3 queries set up. 1 calculates the total for each
unit, another calculates the complete for each unit, and the third
calculates the difference and displays my final table above.

There must be an easier (and more elegant) way though. Any ideas would
be greatly appreciated.

For those interested, here's the details:

The table I'm pulling from is tblKSMaster.
I'm counting the timestamp field becuase I know each record will have
a value in that field.

Here's the queries:

qrySubDataComplete:
SELECT tblKSMaster.AssignedUnit, Count(tblKSMaster.Timestamp) AS
CountOfTimestamp
FROM tblKSMaster
WHERE (((tblKSMaster.Status)="REWRITE_DONE" Or
(tblKSMaster.Status)="EDIT_DONE" Or (tblKSMaster.Status)="INVALID" Or
(tblKSMaster.Status)="NVALID"))
GROUP BY tblKSMaster.AssignedUnit
HAVING (((tblKSMaster.AssignedUnit)="network" Or
(tblKSMaster.AssignedUnit)="ebi" Or
(tblKSMaster.AssignedUnit)="mainframe"))
ORDER BY tblKSMaster.AssignedUnit;

SELECT tblKSMaster.AssignedUnit, Sum(1) AS TotalCount, _
Sum(IIF(Instr("REWRITE_DONE,EDIT_DONE,INVALID,PUBF OLDER",[Status]) > 0;1,0)
As Complete,
Sum(IIF(Instr("REWRITE_DONE,EDIT_DONE,INVALID,PUBF OLDER",[Status]) = 0;1,0)
As InComplete
FROM tblKSMaster
WHERE (((tblKSMaster.AssignedUnit)="network" Or
(tblKSMaster.AssignedUnit)="ebi" Or
(tblKSMaster.AssignedUnit)="mainframe"))
GROUP BY tblKSMaster.AssignedUnit
ORDER BY tblKSMaster.AssignedUnit;

This should work....or should be close to what you want.