Distinct Work Units in Totals

[netrusher]

The first section of code below is giving me fault totals and a total of Distinct Work Units.

The second section of code below is giving me the same fault totals by shift but the Work Units totals are all totals and not Distinct Work Units like the first bit of code. Could someone tell me where I am going wrong? This is code that I have received help on from earlier posts.

Select SystemGroup, Count(*) As [Mechanical Totals]
From WorkUnitsFaultsMainTBL
WHERE (((WorkUnitsFaultsMainTBL.FaultCategory)<>"No Faults") AND (WorkUnitsFaultsMainTBL.FaultCategory)<>"Cosmetic") And  ([TodaysDate] Between [Forms]![WorkUnitsFaultsMainFRM]![StartDateTxt] And [Forms]![WorkUnitsFaultsMainFRM]![EndDateTxt]And WorkUnitsFaultsMainTBL.BuildID In ("E010","C809","F001","C810","F187","A910","M173","M174"))
Group By SystemGroup

UNION ALL Select 'Total Work Units', count([WorkUnit]) from (select distinct [WorkUnit]
From WorkUnitsFaultsMainTBL
WHERE (((WorkUnitsFaultsMainTBL.FaultCategory)<>"No Faults") AND (WorkUnitsFaultsMainTBL.FaultCategory)<>"Cosmetic") And  ([TodaysDate] Between [Forms]![WorkUnitsFaultsMainFRM]![StartDateTxt] And [Forms]![WorkUnitsFaultsMainFRM]![EndDateTxt])And WorkUnitsFaultsMainTBL.BuildID In ("E010","C809","F001","C810","F187","A910","M173","M174"));

Select SystemGroup,
sum(IIf(FirstShift = Yes, 1, 0)) as [First Shift],
sum(IIf(SecondShift = Yes, 1, 0)) as [Second Shift],
Count(*) As [Totals]
From WorkUnitsFaultsMainTBL
WHERE (((WorkUnitsFaultsMainTBL.FaultCategory)<>"No Faults") AND (WorkUnitsFaultsMainTBL.FaultCategory)<>"Cosmetic") And  ([TodaysDate] Between [Forms]![WorkUnitsFaultsMainFRM]![StartDateTxt] And [Forms]![WorkUnitsFaultsMainFRM]![EndDateTxt] And WorkUnitsFaultsMainTBL.BuildID In ("E010","C809","F001","C810","F187","A910","M173","M174"))
Group By SystemGroup

UNION ALL Select 'Total Work Units',
sum(IIf(FirstShift = Yes, 1, 0)) as [First Shift],
sum(IIf(SecondShift = Yes, 1, 0)) as [Second Shift],
Count(*) As [Totals]
From
(select distinct [WorkUnit], [FirstShift], [SecondShift] from WorkUnitsFaultsMainTBL
Where [TodaysDate] Between [Forms]![WorkUnitsFaultsMainFRM]![StartDateTxt] And [Forms]![WorkUnitsFaultsMainFRM]![EndDateTxt]And WorkUnitsFaultsMainTBL.BuildID In ("E010","C809","F001","C810","F187","A910","M173","M174"));

 

[lespaul]

exactly how are these queries wrong?

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

 

[netrusher]

Leslie,

The Work Unit Totals I am receiving in the Second SQL Code are not the Distinct Work Unit Totals. I am getting a different Work Unit Totals from the First SQL Query then the Second. I am getting a total work unit count instead of a Disinct Work Unit Count. The First SQL is giving me what I need (distinct Work Unit Count). The second SQL is giving me (total Work Unit Count.

Thanks for your help!

 

[lespaul]

Can you show some examples of what you mean? What do the results look like that are incorrect? What SHOULD they look like?

 

[netrusher]

The Total Work Units should be 26 for both Queries. The second query with 1st Shift & 2nd Shift give me a different
totals. There are 35 Faults but on 26 Distinct Work Units. I want the second query Total Work Units to equal the first query Total Work Units.
All the totals match for both queries except for the Total Work Units. Thanks again.


SystemGroup Mechanical Totals
Back up Alarm 1
Battery Cable 1
Brake Cable 1
Cooling system 1
Cowl 4
CWT 6
Dash Display 3
Electrical 2
Engine 1
Fuel System 3
Hood 2
Hydraulic System 2
Levers 3
Mast 2
Mast-Assy-Fab 10
Mini Levers 3
OHG 2
Steering 1
Valve 2
Total Work Units 26
------------------------------------
SystemGroup First Shift Second Shift Totals
Back up Alarm 0 1 1
Battery Cable 0 1 1
Brake Cable 0 1 1
Cooling system 0 1 1
Cowl 1 3 4
CWT 4 2 6
Dash Display 1 2 3
Electrical 2 0 2
Engine 1 0 1
Fuel System 1 2 3
Hood 1 1 2
Hydraulic System 2 0 2
Levers 2 1 3
Mast 2 0 2
Mast-Assy-Fab 5 5 10
Mini Levers 3 0 3
OHG 2 0 2
Steering 0 1 1
Valve 2 0 2
Total Work Units 18 17 35

 

[lespaul]

Ok, starting with the easy stuff:

Totals:
1+1+1+1+4+6+3+2+1+3+2+2+3+2+10+3+2+1+2 = 50

Not 26 or 35...So, obviously there's more wrong than you think.

Your first query:

Select SystemGroup, Count(*) As [Mechanical Totals]
From WorkUnitsFaultsMainTBL
WHERE (((WorkUnitsFaultsMainTBL.FaultCategory)<>"No Faults") AND (WorkUnitsFaultsMainTBL.FaultCategory)<>"Cosmetic") And  ([TodaysDate] Between [Forms]![WorkUnitsFaultsMainFRM]![StartDateTxt] And [Forms]![WorkUnitsFaultsMainFRM]![EndDateTxt]And WorkUnitsFaultsMainTBL.BuildID In ("E010","C809","F001","C810","F187","A910","M173","M174"))
Group By SystemGroup

UNION ALL Select 'Total Work Units', count([WorkUnit]) from (select distinct [WorkUnit]
From WorkUnitsFaultsMainTBL
WHERE (((WorkUnitsFaultsMainTBL.FaultCategory)<>"No Faults") AND (WorkUnitsFaultsMainTBL.FaultCategory)<>"Cosmetic") And  ([TodaysDate] Between [Forms]![WorkUnitsFaultsMainFRM]![StartDateTxt] And [Forms]![WorkUnitsFaultsMainFRM]![EndDateTxt])And WorkUnitsFaultsMainTBL.BuildID In ("E010","C809","F001","C810","F187","A910","M173","M174"));

is counting different things. The first one is counting mechanical totals and the second is counting WorkUnits. What do you really want to count?







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

 

[netrusher]

I want to count both. There really are 50 Mechanical Faults but only 26 Work Units as some Work Units have multiple faults. Does that make sense? I sure appreciate your help!

 

[Golom]

Its probably because this

Select SystemGroup,
sum(IIf(FirstShift = Yes, 1, 0)) as [First Shift],
sum(IIf(SecondShift = Yes, 1, 0)) as [Second Shift],
Count(*) As [Totals]
From WorkUnitsFaultsMainTBL
WHERE (((WorkUnitsFaultsMainTBL.FaultCategory)<>"No Faults") AND (WorkUnitsFaultsMainTBL.FaultCategory)<>"Cosmetic") And  ([TodaysDate] Between [Forms]![WorkUnitsFaultsMainFRM]![StartDateTxt] And [Forms]![WorkUnitsFaultsMainFRM]![EndDateTxt] And WorkUnitsFaultsMainTBL.BuildID In ("E010","C809","F001","C810","F187","A910","M173","M174"))
Group By SystemGroup

UNION ALL

Select 'Total Work Units',
sum(IIf(FirstShift = Yes, 1, 0)) as [First Shift],
sum(IIf(SecondShift = Yes, 1, 0)) as [Second Shift],
Count(*) As [Totals]
From

(select [red]distinct[/red] [WorkUnit], [FirstShift], [SecondShift] from WorkUnitsFaultsMainTBL
Where [TodaysDate] Between [Forms]![WorkUnitsFaultsMainFRM]![StartDateTxt] And [Forms]![WorkUnitsFaultsMainFRM]![EndDateTxt] And WorkUnitsFaultsMainTBL.BuildID In ("E010","C809","F001","C810","F187","A910","M173","M174"))

isn't doing what you expect it to do.

The DISTINCT predicate returns one record for each DISTINCT combination of the fields listed in the SELECT ... NOT for just the first such field.

You may need something more like

UNION ALL 

Select 'Total Work Units',
       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]

  AND WorkUnitsFaultsMainTBL.BuildID In
       ("E010","C809","F001","C810","F187","A910","M173","M174")

which is just what you have in the first query.

 

[netrusher]

First Shift should have 15 Total Distinct Work Units
and Second Shift should have 11 Total Distinct Work Units

 

[netrusher]

Golom,

I put in the code that you sent and these are the totals
I received on Total Work Units. Still not right! Thanks for any other help you all might have.

SystemGroup First Shift Second Shift Totals
Total Work Units 35 29 64

 

[netrusher]

Miss Leslie,

Do you have any input for me?