Solved

Complex SQL calculation with single query

Posted on 1997-12-22
17
594 Views
Last Modified: 2008-02-01
The table below describes this situation:
- I have some Managers (let's say X and Y)
- Several of them report to a Senior Manager
- Each manager is in charge of several jobs (App1, App2, App3)
- Each job is running several times every week and the result is coded (A, B, C)
- The database is recording the number of times each code occured within a week
- I'm retrieving (and displaying) all data for last week and I have to calculate:
   - Sum by App (Row)
   - Sum by Code (Column)
   - Total by Manager
   - Total by Sr. Manager

Unfortunately, the text is not displayed in ASCII, if you
want to see the table, I can e-mail it. Or you can look at
this Page Source, it looks better.

                   A | B | C |TotRow
Sr. Mgr
  ManagerX   App1  1 | 0 | 0 | 1
             App2  2 | 0 | 2 | 4
                  --- --- --- ---
   TotalX          3   0   2   5

  ManagerY   App3  2 | 1 | 3 | 6
                  --- --- --- ---
    TotalY         2   1   3   6

Total Sr. Mgr                 11
 
Can all this be done with a single query (it has to be a single query, due to some middleware limitations)? And if yes, how?
Thanks for your help,
regards,
Ion
0
Comment
Question by:iamari
  • 9
  • 7
17 Comments
 
LVL 2

Author Comment

by:iamari
ID: 1963487
Edited text of question
0
 
LVL 2

Author Comment

by:iamari
ID: 1963488
Edited text of question
0
 
LVL 2

Author Comment

by:iamari
ID: 1963489
Edited text of question
0
 
LVL 2

Author Comment

by:iamari
ID: 1963490
Edited text of question
0
 
LVL 2

Author Comment

by:iamari
ID: 1963491
Edited text of question
0
 
LVL 2

Expert Comment

by:Bangerter
ID: 1963492
Is this something that is going to be put into a report? If so, if I understand you right, it can be done. If it is not going to a report then what are you doing with the results?
0
 
LVL 2

Author Comment

by:iamari
ID: 1963493
Yes, it's going to a report written in Cold Fusion. Because of
some grouping limitations in CF, it has to be done with a single
query
0
 
LVL 1

Accepted Solution

by:
IanHinson earned 150 total points
ID: 1963494
To demonstrate the crosstab query you require, which is shown below, I set up some tables which correspond to the situation you described.
Even though the structure/fieldnames might not match exactly what you currently have, I would ask that you create the tables and query (in a new database if necessary) as described at first, so that you can more readily see the crosstab working and follow other instructions I have provided about the report construction.

Senior Managers table
---------------------
Structure:
SnrMgrID autonumber
SnrMgrName Text

Data:
1 Sr.Mgr1
2 Sr.Mgr2

Managers table
--------------
Structure:
MgrID    autonumber
MgrName  text
SnrMgrID number(long) ;who the manager reports to

Data:
1  X  1
2  Y  1

Jobs table
----------
Structure:
JobID  autonumber
Job Name  text
MgrID     number(long)

Data:
1  App1  1
2  App2  1
3  App3  2

Job Results table
-----------------
Structure:
JobID  Number(long)
Date   Date/Time
Result Text

Data: (Date shown in D/M/Y format - enter as M/D/Y if required)
1 8/12/97 A
2 8/12/97 A
2 9/12/97 C
2 10/12/97 A
2 11/12/97 C
3 8/12/97 A
3 9/12/97 B
3 10/12/97 A
3 11/12/97 C
3 12/12/97 B

The query that provides the source for the report is:

TRANSFORM Count([Job Results].JobID) AS CountOfJobID
SELECT 1+[Date]-Weekday([Date]) AS WeekStart, [Senior Managers].SnrMgrID, [Senior Managers].SrMgrName, Managers.MgrID, Managers.MgrName, Jobs.JobID, Jobs.[Job Name], Count([Job Results].JobID) AS CountOfJobID1
FROM (([Senior Managers] INNER JOIN Managers ON [Senior Managers].SnrMgrID = Managers.SnrMgrID) INNER JOIN Jobs ON Managers.MgrID = Jobs.MgrID) INNER JOIN [Job Results] ON Jobs.JobID = [Job Results].JobID
GROUP BY 1+[Date]-Weekday([Date]), [Senior Managers].SnrMgrID, [Senior Managers].SrMgrName, Managers.MgrID, Managers.MgrName, Jobs.JobID, Jobs.[Job Name]
PIVOT [Job Results].Result In ("A","B","C");

To create this query:
1. Create a new query and switch it from design view into SQL view.
2. Paste the above text into the SQL view

The report does not need any subreports, but you should create grouping headers and footers to show some totals which are calculated directly on the report.
I'm not familiar with your report package hope it includes the abilty to group data based on certain fields in the underlying query and to include calculated controls in the headers/footers of those groups.

Here are the groupings you need to produce the format you set out in your question.
First Grouping: [WeekStart] Header - show WeekStart field, no footer

Second Grouping: [SnrMgrID]
Header - show SnrMgrName
Footer - include a textbox with control source =Sum([CountOfJobID1])

Third Grouping: [MgrID]
Header - show MgrName
Footer - show Sum([A]),Sum([B]),Sum([C]),Sum([CountOfJobID1]) in separate text boxes.

Fourth Grouping: [JobID] No header, no footer
In detail section show: [Job Name], [A],[B],[C],[CountOfJobID1]

It may be worthwhile experimenting with an Access report first.

Ian.

0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 1

Expert Comment

by:IanHinson
ID: 1963495
Oops. Typographic error: 'SnrMgrName' should be 'SrMgrName' to be consistent with fieldname referenced in the query.

You can check whether the query looks ok before running it by switching it back into design view after entering the Sql.
There shouldn't be any automatically named fields e.g.'Expr1'. If there is then the fieldname is unrecognised and spelling of the fieldname should be checked.

0
 
LVL 2

Author Comment

by:iamari
ID: 1963496
I'm using MSAccess 7.0 for Windows95. Using the query under MSAccess, I get the message: Syntax error in JOIN operation.
Under MS Query, I get: This program has performed an illegal operation and will be shut down
MSQUERY caused a general protection fault
in module ODBC.DLL at 0003:000003c8.
Registers:
EAX=00004667 CS=47f7 EIP=000003c8 EFLGS=00000202
EBX=3a9f1c6c SS=4547 ESP=00009c72 EBP=00009c9a
ECX=0000466f DS=4827 ESI=00021c6c FS=321f
EDX=ffff0000 ES=0000 EDI=00020000 GS=0000
Bytes at CS:EIP:
26 ff b7 1a 06 9a 0d 01 2f 01 89 46 ea 89 56 ec
Stack dump:
19b83a97 13b8ffff 1bd3466f 00000000 00000000 101a0000 002104c0 c0e00000 15be80eb 51000051 04af9cac 9eca3a9f 00024547 005119b8 9ed83a97 3a9f0e24
 
Any suggestions?

Ion
0
 
LVL 1

Expert Comment

by:IanHinson
ID: 1963497
First, check that the query runs OK in MS Access. (I expect you would have done that already anyway.) I ran it in Access ok here, so sort that part out first before continuing with MS Query.

MSQuery is not as powerful a query engine as Access, and may not be able to handle this syntax directly. But you can still use the query in MSQuery by:
1) After verifying that you have got it working in Access, then Save it in Access with some name.
2) Refer to the saved query directly by name using MSQuery just as you would refer to a table contained inside the Access mdb.

0
 
LVL 2

Author Comment

by:iamari
ID: 1963498
I made the following changes:
- I eliminated all spaces from field names, it seems to create problems.
- I renamed Date to JDate, I think is kind of confusing for the ODBC driver.
- Renamed the SnrMgrID and SnrMgrName to SrMgrID and SrMgrID
- I've added a Max clause to the date field, because that's what the report is supposed to show anyway.
The modified query is:

TRANSFORM Count(JobResults.JobID) AS CountOfJobID
SELECT SeniorManagers.SrMgrID, SeniorManagers.SrMgrName, Managers.MgrID, Managers.MgrName, Jobs.JobID, Jobs.JobName, Count(JobResults.JobID) AS CountOfJobID1
FROM ((SeniorManagers INNER JOIN Managers ON SeniorManagers.SrMgrID = Managers.SrMgrID) INNER JOIN Jobs ON Managers.MgrID = Jobs.MgrID) INNER JOIN JobResults ON Jobs.JobID = JobResults.JobID
WHERE (((JobResults.JDate)=(SELECT Max(JDate) FROM JobResults)))
GROUP BY JobResults.JDate, SeniorManagers.SrMgrID, SeniorManagers.SrMgrName, Managers.MgrID, Managers.MgrName, Jobs.JobID, Jobs.JobName
PIVOT JobResults.JobResults In ("A","B","C");

The query works, I get all the data and the total by row (Count of JobID). However, I still need a count by column for "A", "B" and "C" grouped by Manager and then a grand total grouped by Senior Manager.
Is this possible within the same query?

Regards,

Ion
0
 
LVL 1

Expert Comment

by:IanHinson
ID: 1963499
On re-reading your comment, I realise that you may already be doing what I suggested in 1) and 2).
Since obviously there is no point trying to hook up MSQuery to an Access query which is known not to work properly, I assumed you were trying to reconstruct the query inside of MSQuery instead.

I retested the query in MSAccess, and after fixing the incorrect field reference to 'SrMgrName' which I mentioned in an earlier comment, it works fine.
In order to debug the join problem you are having I would need:
1)the Sql of the query you are using, and
2)the structures of the tables you have created.


0
 
LVL 1

Expert Comment

by:IanHinson
ID: 1963500
PLEASE DISREGARD THE ABOVE COMMENT TO THE EXTENT THAT IT WAS WRITTEN OFF-LINE BEFORE YOUR COMMENT ABOVE IT WAS KNOWN ABOUT BY ME.  THANKS FOR THE EXTRA INFO. WILL GET BACK TO YOU.
0
 
LVL 1

Expert Comment

by:IanHinson
ID: 1963501
Some of the problems, as I see it, with your new query are:
1) The criteria ((JobResults.JDate)=(SELECT Max(JDate) FROM JobResults)) means that the ONLY rows (i.e. job results) that will be counted are those which occurred on the latest job-result day. I'm sure you want to include rows other than that in the count.
2) Your major grouping in JobResults.JDate means that totals you obtain will each relate to a given day, not a given week.

Do you still require the totals to be over a one week period? If so, do you want the immediate past week or the previous Sun-Sat week?
I modified the query to give only the previous Sun-Sat week's results.
(Also I guess that, since you are pivoting on JobResults.JobResults, the 'Results' field has been renamed to 'JobResults' and have made that assumption for the query below.)

PARAMETERS [Enter Date:] DateTime;
TRANSFORM Count(JobResults.JobID) AS CountOfJobID
SELECT 1+[JDate]-Weekday([JDate]) AS WeekStart, SeniorManagers.SrMgrID, SeniorManagers.SrMgrName, Managers.MgrID, Managers.MgrName, Jobs.JobID, Jobs.JobName, Count(JobResults.JobID) AS CountOfJobID1
FROM ((SeniorManagers INNER JOIN Managers ON SeniorManagers.SrMgrID = Managers.SrMgrID) INNER JOIN Jobs ON Managers.MgrID = Jobs.MgrID) INNER JOIN JobResults ON Jobs.JobID = JobResults.JobID
WHERE (((1+[JDate]-Weekday([JDate]))=1+[Enter Date:]-Weekday([Enter Date:])))
GROUP BY 1+[JDate]-Weekday([JDate]), SeniorManagers.SrMgrID, SeniorManagers.SrMgrName, Managers.MgrID, Managers.MgrName, Jobs.JobID, Jobs.JobName
PIVOT JobResults.Result In ("A","B","C");

The prompt [Enter Date:] purely temporary to enable the query to be tested with different notional Date's. The [Enter Date:] should be replaced by Date() when you are satisfied the query works - and also remove the PARAMETERS clause at the same time.

As for getting a total for each Senior Manager in the same query:
1) It can only be done by writing a separate query that will calculate those totals, and then joining that query with the one above.
2) Are you sure you can't calculate that total in the SrMgr's group footer of the report?


0
 
LVL 2

Author Comment

by:iamari
ID: 1963502
Perhaps I should have explained this: I get an ASCII file to load in my database. The file contains only jobs ran during last week, so the Max function would do just fine, since all jobs will show the same "ending date".
The reporting ability of CF is very limited, indeed, there are no footers. On top of that, it only allows output from a single query (identified by name) within one grouping.
So, my biggest problem is really to count jobs by Manager and by Senior Manager within the same query (I'm not allowed to assign the same name to 2 different queries either).
If I join a main query to another one for a Senior Manager grand total, it might work, if I can use a single query name.
As for the new query you posted, it's still not showing a total for managers, but just a total by row (CountOfJobID1)

Regards,

Ion
0
 
LVL 1

Expert Comment

by:IanHinson
ID: 1963503
The query you posted is fine, given that JDates represent week-ending dates.
The only change I made to it is to include JDate as a RowHeading for joining it with other queries later. So it now reads as:

TRANSFORM Count(JobResults.JobID) AS CountOfJobID
SELECT JobResults.JDate, SeniorManagers.SrMgrID, SeniorManagers.SrMgrName, Managers.MgrID, Managers.MgrName, Jobs.JobID, Jobs.JobName, Count(JobResults.JobID) AS CountOfJobID1
FROM ((SeniorManagers INNER JOIN Managers ON SeniorManagers.SrMgrID = Managers.SrMgrID) INNER JOIN Jobs ON Managers.MgrID = Jobs.MgrID) INNER JOIN JobResults ON Jobs.JobID = JobResults.JobID
WHERE (((JobResults.JDate)=(SELECT Max(JDate) FROM JobResults)))
GROUP BY JobResults.JDate, SeniorManagers.SrMgrID, SeniorManagers.SrMgrName, Managers.MgrID, Managers.MgrName, Jobs.JobID, Jobs.JobName
PIVOT JobResults.JobResults In ("A","B","C");

Please save the above query as JobCounts as it is referred to in other queries below.

Construct a second query and save it as SrMgrJobCounts having Sql:
SELECT JobResults.JDate, Managers.SrMgrID, Count(JobResults.JobID) AS SrMgrJobs
FROM (Managers INNER JOIN Jobs ON Managers.MgrID = Jobs.MgrID) INNER JOIN JobResults ON Jobs.JobID = JobResults.JobID
GROUP BY JobResults.JDate, Managers.SrMgrID;

Construct a third query and save it as MgrJobCounts having Sql:
SELECT JobResults.JDate, Jobs.MgrID, Count(JobResults.JobID) AS MgrJobs
FROM Jobs INNER JOIN JobResults ON Jobs.JobID = JobResults.JobID
GROUP BY JobResults.JDate, Jobs.MgrID;

Now the final query joins JobCounts with the other two queries which becomes the recordsorce for the report.

SELECT SrMgrJobCounts.SrMgrJobs, MgrJobCounts.MgrJobs, JobCounts.*
FROM (JobCounts INNER JOIN MgrJobCounts ON (JobCounts.MgrID = MgrJobCounts.MgrID) AND (JobCounts.JDate = MgrJobCounts.JDate)) INNER JOIN SrMgrJobCounts ON (JobCounts.SrMgrID = SrMgrJobCounts.SrMgrID) AND (JobCounts.JDate = SrMgrJobCounts.JDate);

Hope this helps,
Ian.


0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now