Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations MikeeOK on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

TOTALS BY SHIFT AND TIME 1

Status
Not open for further replies.

netrusher

Technical User
Feb 13, 2005
952
US
Everyone, I have been asked to do something that is way over my head. Below is the SQL of a query that I currently have. I need to throw some more criteria in for the totals. I have two Fields: FirstShift & SecondShift. They are yes/no boxes. I need the query to give me the same totals as it does now but by FirstShift & Secondshift. I also then would need the grand totals for the day which of course would be a combination of the two shifts. To take it a step further one of The FaultCategory's is NoFault. I would like to know the percentage of WorkUnits for each shift and total that were NoFaults. I know you need to divde the number of NoFaults by the Total Number of WorkUnits per shift and then per day. Is this doable? I sure hope so. I have been given this as a challenge and am hoping someone out there can get me the answer.

Thanks in advance.

Code:
Select FaultCategory, Count(*) As [Faults/NoFaults]
From WorkUnitsFaultsMainTBL
Where [TodaysDate] Between [Forms]![WorkUnitsFaultsMainFRM]![StartDateTxt] And [Forms]![WorkUnitsFaultsMainFRM]![EndDateTxt]
Group By FaultCategory

UNION ALL Select 'Total Work Units', count([WorkUnit]) from (select distinct [WorkUnit] from WorkUnitsFaultsMainTBL
Where [TodaysDate] Between [Forms]![WorkUnitsFaultsMainFRM]![StartDateTxt] And [Forms]![WorkUnitsFaultsMainFRM]![EndDateTxt]);
 
yep, I knew that, but it's actually a combination of my name & my husband's (I'm Leslie, he's Paul)...I've thought about changing it, but it's an interesting conversation piece!

Les
 
Alex,

Hope you had a wonderful Thanksgiving!

Yes the query that I posted in my initial post works just great! Anymore ideas?
 
NetRusher -

I had a fantastic Thanksgiving, hope you did the same.

I think I just realized the problem. The "Yes" in your where clauses should not be in quotes. So it should read like this:

Code:
and FirstShift = Yes
and
Code:
and SecondShift = Yes

Let me know if this works for you.

Alex

A wise man once said
"The only thing normal about database guys is their tables".
 
Alex,

I had a wonderful Thanksgiving also!

That kind of works but is not quite how I want to display the results.

I would like for them to appear something like this:

FaultCategory First Shift Second Shift Totals
Mechanical---------------16--------------5------------21
Cosmetic-----------------13--------------2------------15
NoFaults-------------------2--------------0-------------2
Total Work Units------18--------------3------------21

Is this doable?
 
I'm sure it is doable. Can you show how your current output looks, using the same numbers from the example above? I think it will be fairly easy.

Alex

A wise man once said
"The only thing normal about database guys is their tables".
 
I think I know what you are shooting for, after reading a bit more closely. Try this:


Code:
Select FaultCategory, 
sum(IIf(FirstShift = Yes, 1, 0)) as [First Shift],
sum(IIf(SecondShift = Yes, 1, 0)) as [Second Shift],
Count(*) As [Totals]
From WorkUnitsFaultsMainTBL
Where [TodaysDate] Between [Forms]![WorkUnitsFaultsMainFRM]![StartDateTxt] And [Forms]![WorkUnitsFaultsMainFRM]![EndDateTxt]
Group By FaultCategory

UNION Select 'TOTAL', 
sum(IIf(FirstShift = Yes, 1, 0)) as [First Shift],
sum(IIf(SecondShift = Yes, 1, 0)) as [Second Shift],
Count(*) As [Totals]
From WorkUnitsFaultsMainTBL
Where [TodaysDate] Between [Forms]![WorkUnitsFaultsMainFRM]![StartDateTxt] And [Forms]![WorkUnitsFaultsMainFRM]![EndDateTxt]

I think that this will give you the desired output.

Note that your First and Second shift numbers may not add up to the total if there are rows where first and second shift are checked.

Hope it helps,

Alex



A wise man once said
"The only thing normal about database guys is their tables".
 
WOW!!!

That did it! Thanks a lot. Can we try for the final part of my original post. Can we also show a percentage of the total work units per shift that have no faults?

Example

First Shift had 30 Work Units and 2 No Faults. That would be a pct of .667 Work Units with no faults for First Shift.
Second Shift had 5 Work Units with 0 No Faults. That would mean that 0% of Work Units had no Faults.

First & Second Shift combined had 35 Work Units with 2 No Faults and that would be a pct .571 of Work Units with No Faults.

Is this doable also???
 
Alex,

Also in my original post I have the following code that I need to keep in the new SQL you provided: How do I do that?

Code:
from (select distinct [WorkUnit] from WorkUnitsFaultsMainTBL

My Work Units Totals must be Distince Work Units because I might have multiple Faults per Work Unit and I only want to see the Work Unit Totals distinctly.
 
The trick with this part will be to get your TOTAL work units into the calculation. I have not tested this, but I think it will do the trick.

Code:
Select FaultCategory, 
sum(IIf(FirstShift = Yes, 1, 0)) as [First Shift],
formatpercent(sum(IIf(FirstShift = Yes, 1, 0))/(select sum(1) from WorkUnitsFaultsMainTBL where FirstShift = Yes)) as [PCT Shift 1],
sum(IIf(SecondShift = Yes, 1, 0)) as [Second Shift],
formatpercent(sum(IIf(SecondShift = Yes, 1, 0))/(select sum(1) from WorkUnitsFaultsMainTBL where SecondShift = Yes)) as [PCT Shift 2],
Count(*) As [Totals],
formatpercent(count(*)/(select sum(1) from WorkUnitsFaultsMainTBL)) as [PCT Total],
From WorkUnitsFaultsMainTBL
Where [TodaysDate] Between [Forms]![WorkUnitsFaultsMainFRM]![StartDateTxt] And [Forms]![WorkUnitsFaultsMainFRM]![EndDateTxt]
Group By FaultCategory

UNION Select 'TOTAL', 
sum(IIf(FirstShift = Yes, 1, 0)) as [First Shift],
formatpercent(sum(IIf(FirstShift = Yes, 1, 0))/(select sum(1) from WorkUnitsFaultsMainTBL where FirstShift = Yes)) as [PCT Shift 1],
sum(IIf(SecondShift = Yes, 1, 0)) as [Second Shift],
formatpercent(sum(IIf(SecondShift = Yes, 1, 0))/(select sum(1) from WorkUnitsFaultsMainTBL where SecondShift = Yes)) as [PCT Shift 2],
Count(*) As [Totals],
formatpercent(count(*)/(select sum(1) from WorkUnitsFaultsMainTBL)) as [PCT Total],
From WorkUnitsFaultsMainTBL
Where [TodaysDate] Between [Forms]![WorkUnitsFaultsMainFRM]![StartDateTxt] And [Forms]![WorkUnitsFaultsMainFRM]![EndDateTxt]

Hope this works out, if not post back with your error message.

Note that this will display the percent of the total for each FaultCategory. In your union portion, it should always read 100%. If you want to see percent of total work units in each shift, some slight changes will accomplish that.

Let me know how it works,
ALex



A wise man once said
"The only thing normal about database guys is their tables".
 
Alex,

I am getting the following error:

The SELECT statment includes a reserved word or argument name that is misspelled or missing, or the punctuation is incorrect.
 
Oops, I left a couple of commas in there (immediately prior to each FROM clause) [blush]. Try this:

Code:
Select FaultCategory, 
sum(IIf(FirstShift = Yes, 1, 0)) as [First Shift],
formatpercent(sum(IIf(FirstShift = Yes, 1, 0))/(select sum(1) from WorkUnitsFaultsMainTBL where FirstShift = Yes)) as [PCT Shift 1],
sum(IIf(SecondShift = Yes, 1, 0)) as [Second Shift],
formatpercent(sum(IIf(SecondShift = Yes, 1, 0))/(select sum(1) from WorkUnitsFaultsMainTBL where SecondShift = Yes)) as [PCT Shift 2],
Count(*) As [Totals],
formatpercent(count(*)/(select sum(1) from WorkUnitsFaultsMainTBL)) as [PCT Total]
From WorkUnitsFaultsMainTBL
Where [TodaysDate] Between [Forms]![WorkUnitsFaultsMainFRM]![StartDateTxt] And [Forms]![WorkUnitsFaultsMainFRM]![EndDateTxt]
Group By FaultCategory

UNION Select 'TOTAL', 
sum(IIf(FirstShift = Yes, 1, 0)) as [First Shift],
formatpercent(sum(IIf(FirstShift = Yes, 1, 0))/(select sum(1) from WorkUnitsFaultsMainTBL where FirstShift = Yes)) as [PCT Shift 1],
sum(IIf(SecondShift = Yes, 1, 0)) as [Second Shift],
formatpercent(sum(IIf(SecondShift = Yes, 1, 0))/(select sum(1) from WorkUnitsFaultsMainTBL where SecondShift = Yes)) as [PCT Shift 2],
Count(*) As [Totals],
formatpercent(count(*)/(select sum(1) from WorkUnitsFaultsMainTBL)) as [PCT Total]
From WorkUnitsFaultsMainTBL
Where [TodaysDate] Between [Forms]![WorkUnitsFaultsMainFRM]![StartDateTxt] And [Forms]![WorkUnitsFaultsMainFRM]![EndDateTxt]

Hope it works this time ;-)

Alex

A wise man once said
"The only thing normal about database guys is their tables".
 
Alex,

How can I add the Distinct Work Unit back in the SQL?

Also, the last SQL works but I am not sure what I am seeing with the Percentages. I really only need to see the Percentage of First Shift Trucks that had no Faults. Percentage of Second Shift Trucks that had no Faults and totals of both shifts and pct with no faults. I hope I am not becoming to big of a pain. I am trying to learn from you also. You have been a great help. Thanks again!
 
Not sure I understand what you mean by distinct work unit.

As far as the percentages, I think it is more work than it is worth to exclude the percentage calculations for Fault Categories where you don't want to see them. That kind of stuff would normally be done on your front end. If you are set on not seeing all of the percentages, you might be better served to display the calculations in a report or on a form, rather than within the query.

This is the way that the query is laid out from left to right:

FaultCategory, Count for FirstShift, percentage of first shift total, count for SecondShift, percentage of SecondShift total, total(combined shifts), percentage of combined total.

If you must exclude all the other calculations on a query level (again, not recommended), this might do the trick (it will place a zero everywhere that a percent shows unless the faultcategory is 'NoFaults', in which case it will show the calculated percentage).

Code:
Select FaultCategory, 
sum(IIf(FirstShift = Yes, 1, 0)) as [First Shift],
IIf(FaultCategory = "NoFaults",
formatpercent(sum(IIf(FirstShift = Yes, 1, 0))/(select sum(1) from WorkUnitsFaultsMainTBL where FirstShift = Yes))
, 0) as [PCT Shift 1],
sum(IIf(SecondShift = Yes, 1, 0)) as [Second Shift],
IIf(FaultCategory = "NoFaults",
formatpercent(sum(IIf(SecondShift = Yes, 1, 0))/(select sum(1) from WorkUnitsFaultsMainTBL where SecondShift = Yes))
, 0) as [PCT Shift 2],
Count(*) As [Totals],
IIf(FaultCategory = "NoFaults",
formatpercent(count(*)/(select sum(1) from WorkUnitsFaultsMainTBL)) 
, 0) as [PCT Total]
From WorkUnitsFaultsMainTBL
Where [TodaysDate] Between [Forms]![WorkUnitsFaultsMainFRM]![StartDateTxt] And [Forms]![WorkUnitsFaultsMainFRM]![EndDateTxt]
Group By FaultCategory

UNION Select 'TOTAL', 
sum(IIf(FirstShift = Yes, 1, 0)) as [First Shift],
'0',
sum(IIf(SecondShift = Yes, 1, 0)) as [Second Shift],
'0',
Count(*) As [Totals],
'0'
From WorkUnitsFaultsMainTBL
Where [TodaysDate] Between [Forms]![WorkUnitsFaultsMainFRM]![StartDateTxt] And [Forms]![WorkUnitsFaultsMainFRM]![EndDateTxt]

Hope it helps,

Alex

A wise man once said
"The only thing normal about database guys is their tables".
 
Thanks Alex,

Concerning the Distinct Work Units, Please see code in my original posting.

Lets say we have a total of 15 Mechanical Faults. Well those 15 Mechanical Faults may be for only 10 Work Units where some Work Units had multiple Faults. For my totals in Work Units I need the total to be of only Distinct Work Units and not a Work Unit for each Fault. As I said above, some Work Units will have multiple Faults but I only want to count that Work Unit once.
 
I do not know SQL so thoroughly.

I started using it at an old job to check web reporting applications vs. back end database. This got me interested, so I used some online tutorials to learn a bit more. ( is a good one for the basics)

Then I got a job where basically all that I do is work with SQL Server and other database applications, and from there I have learned a lot on the job, as well as from the awesome people on this site.

As you get into it more you will find that it is not too difficult (well sometimes it is, but that tends to be the most fun, and the most valuable learning experience).

Good Luck,

Alex

PS no, not too big of a pain :)



A wise man once said
"The only thing normal about database guys is their tables".
 
Thanks Alex,

I will check out the website. Have you any advice concerning the earlier post about Distinct Work Units? My first posting has that in the code.
 
Understood. Try this?

Code:
Select FaultCategory, 
sum(IIf(FirstShift = Yes, 1, 0)) as [First Shift],
formatpercent(sum(IIf(FirstShift = Yes, 1, 0))/(select sum(1) from WorkUnitsFaultsMainTBL where FirstShift = Yes)) as [PCT Shift 1],
sum(IIf(SecondShift = Yes, 1, 0)) as [Second Shift],
formatpercent(sum(IIf(SecondShift = Yes, 1, 0))/(select sum(1) from WorkUnitsFaultsMainTBL where SecondShift = Yes)) as [PCT Shift 2],
Count(*) As [Totals],
formatpercent(count(*)/(select sum(1) from WorkUnitsFaultsMainTBL)) as [PCT Total]
From WorkUnitsFaultsMainTBL
Where [TodaysDate] Between [Forms]![WorkUnitsFaultsMainFRM]![StartDateTxt] And [Forms]![WorkUnitsFaultsMainFRM]![EndDateTxt]
Group By FaultCategory

UNION Select 'TOTAL', 
sum(IIf(a.FirstShift = Yes, 1, 0)) as [First Shift],
formatpercent(sum(IIf(a.FirstShift = Yes, 1, 0))/(select sum(1) from WorkUnitsFaultsMainTBL where FirstShift = Yes)) as [PCT Shift 1],
sum(IIf(a.SecondShift = Yes, 1, 0)) as [Second Shift],
formatpercent(sum(IIf(a.SecondShift = Yes, 1, 0))/(select sum(1) from WorkUnitsFaultsMainTBL where SecondShift = Yes)) as [PCT Shift 2],
Count(a.*) As [Totals],
formatpercent(count(*)/(select sum(1) from WorkUnitsFaultsMainTBL)) as [PCT Total]
From 
[b](select distinct [WorkUnit], [FirstShift], [SecondShift] from WorkUnitsFaultsMainTBL
Where [TodaysDate] Between [Forms]![WorkUnitsFaultsMainFRM]![StartDateTxt] And [Forms]![WorkUnitsFaultsMainFRM]![EndDateTxt]) a[/b]

Notice in second piece, every time a column name was referenced, I prefaced it with 'a.'. This is because when querying a derived table (which in this case is the subquery in bold), it is a good practice to give it an alias (a in this case) and specify where to look for the fields by using derived table name/alias in front of your columns.

Hope this will help,

ALex

A wise man once said
"The only thing normal about database guys is their tables".
 
Alex,

While I am testing that could you show me how to add the Distinct Work Unit criteria in this code that you submitted earlier?

Code:
Select FaultCategory, 
sum(IIf(FirstShift = Yes, 1, 0)) as [First Shift],
sum(IIf(SecondShift = Yes, 1, 0)) as [Second Shift],
Count(*) As [Totals]
From WorkUnitsFaultsMainTBL
Where [TodaysDate] Between [Forms]![WorkUnitsFaultsMainFRM]![StartDateTxt] And [Forms]![WorkUnitsFaultsMainFRM]![EndDateTxt]
Group By FaultCategory

UNION Select 'TOTAL', 
sum(IIf(FirstShift = Yes, 1, 0)) as [First Shift],
sum(IIf(SecondShift = Yes, 1, 0)) as [Second Shift],
Count(*) As [Totals]
From WorkUnitsFaultsMainTBL
Where [TodaysDate] Between [Forms]![WorkUnitsFaultsMainFRM]![StartDateTxt] And [Forms]![WorkUnitsFaultsMainFRM]![EndDateTxt]
 
Alex,

I get the following error:

Syntax error in query expression 'Count(a*)'.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top