By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,145 Members | 1,619 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,145 IT Pros & Developers. It's quick & easy.

Help out with a difficult query?

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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.

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.