TOTALS BY SHIFT AND TIME 1

[netrusher]

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.

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]);

 

[AlexCuse]

You can take your first piece of the union query, and add

where FirstShift = "yes"

,

Then add a second piece to the union query, identical to the first, and add

where SecondShift = "yes"

And that should do the trick (I think). Leave your bottom piece with the total untouched.

A couple other things I would do - add something to your query to differentiate first from second shift and make sure that there is a validation rule in place that FirstShift and SecondShift cannot both be set to 'yes'

Hope this helps,

Alex

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

 

[netrusher]

Alex,

How and where do I stick this in the SQL? I tried to put it in and can't seem to get it correct.

 

[netrusher]

Also, what do you mean add something to the query to differentiate first from second shift. I want to do that but do not know how.

 

[AlexCuse]

NetRusher-

Here's how it would work (changes in bold):

Select [b]'Shift 1',[/b] 
FaultCategory, Count(*) As [Faults/NoFaults]
From WorkUnitsFaultsMainTBL
Where [TodaysDate] Between [Forms]![WorkUnitsFaultsMainFRM]![StartDateTxt] 
And [Forms]![WorkUnitsFaultsMainFRM]![EndDateTxt]
[b]and FirstShift = "yes"[/b]
Group By FaultCategory

union all Select [b]'Shift 2', [/b]
FaultCategory, Count(*) As [Faults/NoFaults]
From WorkUnitsFaultsMainTBL
Where [TodaysDate] Between [Forms]![WorkUnitsFaultsMainFRM]![StartDateTxt] 
And [Forms]![WorkUnitsFaultsMainFRM]![EndDateTxt]
[b]and SecondShift = "yes"[/b]
Group By FaultCategory

UNION ALL Select [b]'',[/b]
'Total Work Units', count([WorkUnit]) from (select distinct [WorkUnit] from WorkUnitsFaultsMainTBL
Where [TodaysDate] Between [Forms]![WorkUnitsFaultsMainFRM]![StartDateTxt] And [Forms]![WorkUnitsFaultsMainFRM]![EndDateTxt]);

Try pasting this into a new query, and see if you get the desired results. I think you will.

NOtice I added text 'Shift 2' or 'Shift 1' to the query as an extra output field, this is what will allow you to tell which shift is which.

Another way to do this would be to use IIf statements, but that works better on a single bit field (if you just had one yes/no, and yes = first shift, no = second shift).

Hope this helps,

Alex

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

 

[AlexCuse]

I see the bold on this part:

'',

didn't really show. This is key, as it keeps pieces of your union query having same number of output fields.

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

 

[netrusher]

Thanks Alex,

I get the following error when I put the SQL in a query

The expression is typed incorrectly, or it is too complex to be evaluated. For example, a numerica expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.

Also I did not quite understand what you meant on the last post about ", not showing as bold.

Thanks for all your help. I hope you can tell me what is wrong.

 

[AlexCuse]

Did you copy exactly from what I posted? Looking up again, you may want to remove the double quotes around "yes" and "no" and replace with single quotes, as it seems you have enabled ANSI syntax.

Not sure why I noticed this when adding the 'Shift 1', 'Shift 2', and '' and not the where clause...

Can you post the SQL from the query that give you the error? That will help in finding what is wrong.

Thanks,

Alex

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

 

[netrusher]

Alex

Below is the SQL that is giving the error:

Select 'Shift 1', 
FaultCategory, Count(*) As [Faults/NoFaults]
From WorkUnitsFaultsMainTBL
Where [TodaysDate] Between [Forms]![WorkUnitsFaultsMainFRM]![StartDateTxt] 
And [Forms]![WorkUnitsFaultsMainFRM]![EndDateTxt] and FirstShift = "yes"
Group By FaultCategory

UNION ALL Select 'Shift 2', 
FaultCategory, Count(*) As [Faults/NoFaults]
From WorkUnitsFaultsMainTBL
Where [TodaysDate] Between [Forms]![WorkUnitsFaultsMainFRM]![StartDateTxt] 
And [Forms]![WorkUnitsFaultsMainFRM]![EndDateTxt] and SecondShift = "yes"
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]);

 

[AlexCuse]

What do you get if you replace the double quotes around the yes for first and secondshift with single quotes?

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

 

[netrusher]

Alex,

I get the same thing!

 

[AlexCuse]

First thing I would try is to make sure your start and end dates on the form are formatted correctly.

Is 'Today's Date' entered by the user? It it's always going to be Today's date, you might consider using simply Date(), so that there are no problems in the way it is entered.

Hang in there, I'll try and help you get this knocked out before the long weekend.

Alex



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

 

[netrusher]

My start and end date default to today's date. They are changed by clicking on a calendar that pops up. The original query works just fine with the start and end dates. I have a lot of other queries that work with the start and end dates also. The dates will vary depending on what range of time they want to view. So far I have not had any issues with the date fields and my queries. Thanks for all your help.

 

[lespaul]

don't you need to add the date delimiters #?

Select 'Shift 1',
FaultCategory, Count(*) As [Faults/NoFaults]
From WorkUnitsFaultsMainTBL
Where [TodaysDate] Between #[Forms]![WorkUnitsFaultsMainFRM]![StartDateTxt]#
And #[Forms]![WorkUnitsFaultsMainFRM]![EndDateTxt]# and FirstShift = "yes"
Group By FaultCategory

UNION ALL Select 'Shift 2',
FaultCategory, Count(*) As [Faults/NoFaults]
From WorkUnitsFaultsMainTBL
Where [TodaysDate] Between #[Forms]![WorkUnitsFaultsMainFRM]![StartDateTxt]#
And #[Forms]![WorkUnitsFaultsMainFRM]![EndDateTxt]# and SecondShift = "yes"
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]#);



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[AlexCuse]

Good eye Les.

I was thinking because ANSI syntax appeared to be enabled that these would not be necessary. I would defer to you on anything access related though.

(or guitar related :-D)

NetRusher -

Is [TodaysDate] a field in your table?
If so, what is its' data type?

Almost there...

Alex


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

 

[netrusher]

I get:

"Syntax error in Date in query expression" when I add this.

 

[netrusher]

Alex,

It is a field in my table-Date/Time General Date

StartDate and EndDate are not part of the table.

I will be leaving to travel to Ohio but I will check on
the post while there.

Thanks for all of your help.

 

[AlexCuse]

Interesting. I think you should add some debug.prints to your sub and look at the date values in particular.

Are you using MS' calendar to assign values for the two text boxes? I think something is being lost in translation at some point, you may need to format those values (to get from text/string to Date).

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

 

[lespaul]

I would defer to you on anything access related though.

Thanks, but I'm really just a query girl and I don't know anything about guitars either! I don't do much in Access at all, and I'm not always right when I guess at Access specific issues!

Have you tried running each query separately? Do they run individually? Once you get one of them to work correctly, you should be able to do the same thing to each of the others.

Leslie

 

[AlexCuse]

Lol I don't do much in Access either, most of my work is in SQL Server. Access has given me enough fits that I know a bit about it though, especially query syntax as that is where most of the fits happen

Were you aware that your handle is the name of a jazz guitarist who is the namesake of (IMO) the greatest electric guitar ever built?

NetRusher -

Just so we are Crystal Clear, the query that you posted in your initial post (what was that, a year ago now ;-) ) works properly if you run it, correct?

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