AlDeb


Below is the SQL I have for a Query. The results of my query are below the code. Everything works great except for the WorkUnit totals.

I am getting the total Records for the date range and I really need the Unique workUnits for the date range. For this particular daterange I

have a total of 197 WorkUnits but in reality there really are only 89 Unique WorkUnits.

In the example below on one date range I had 0 No Faults and on the other date range I had 7 No Faults.

Can anyone tell me how to add to this query to get only

Unique WorkUnits

SELECT 'No Faults' AS [Fault Type], Sum(IIf([FaultCategory]='No Faults',1,0)) AS [NoFault Totals],

Count (WorkUnitsFaultsMainTBL.WorkUnit) AS [Total Work Units],

FormatPercent(Sum(IIf([FaultCategory]='No Faults',1,0))/Count([WorkUnit]),2) AS [Pct NoFaults]

FROM WorkUnitsFaultsMainTBL

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

And ((WorkUnitsFaultsMainTBL.TodaysDate) Between Forms!Queries_ReportsFRM!StartDateTxt And Forms!Queries_ReportsFRM!EndDateTxt));

Fault Type--------NoFault Totals----------Total Work Units

No Faults-----------------0--------------------------197

Fault Type--------NoFault Totals----------Total Work Units

No Faults-----------------7--------------------------197





Re: Obtain Unique Work Units via SQL

Adamus Turner


SELECT DISTINCT Count (WorkUnitsFaultsMainTBL.WorkUnit) AS [Total Work Units],

Adamus







Re: Obtain Unique Work Units via SQL

AlDeb

Adamus,

How and where does that fit into my current code







Re: Obtain Unique Work Units via SQL

Adamus Turner

SELECT DISTINCT Count (WorkUnitsFaultsMainTBL.WorkUnit) AS [Total Work Units],
'No Faults' AS [Fault Type], Sum(IIf([FaultCategory]='No Faults',1,0)) AS [NoFault Totals],

FormatPercent(Sum(IIf([FaultCategory]='No Faults',1,0))/Count([WorkUnit]),2) AS [Pct NoFaults]

FROM WorkUnitsFaultsMainTBL

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

And ((WorkUnitsFaultsMainTBL.TodaysDate) Between Forms!Queries_ReportsFRM!StartDateTxt And Forms!Queries_ReportsFRM!EndDateTxt));

Adamus






Re: Obtain Unique Work Units via SQL

AlDeb

Adamus,

I am still getting 197 for total WorkUnits.

Code Snippet
SELECT DISTINCT Count (WorkUnitsFaultsMainTBL.WorkUnit) AS [Total Work Units],
'No Faults' AS [Fault Type], Sum(IIf([FaultCategory]='No Faults',1,0)) AS [NoFault Totals],
FormatPercent(Sum(IIf([FaultCategory]='No Faults',1,0))/Count([WorkUnit]),2) AS [Pct NoFaults]
FROM WorkUnitsFaultsMainTBL
WHERE (((WorkUnitsFaultsMainTBL.BuildID) In ("E010","C809","F001","C810","F187","A910","M173","M174"))
And ((WorkUnitsFaultsMainTBL.TodaysDate) Between Forms!Queries_ReportsFRM!StartDateTxt And Forms!Queries_ReportsFRM!EndDateTxt));

What about in line four in the code above where I am dividing with /Count([WorkUnit]),2) AS [Pct NoFaults]

Could this be causing the WorkUnits field to be total records and not distinct WorkUnits






Re: Obtain Unique Work Units via SQL

Adamus Turner

Just Add:

GROUP BY WorkUnitsFaultsMainTBL.WorkUnit

HAVING COUNT(WorkUnitsFaultsMainTBL.WorkUnit) = 1

Adamus






Re: Obtain Unique Work Units via SQL

AlDeb

Now I am getting a total of 1 WorkUnit.

Anything else you can think of I appreciate your help.

Code Snippet

SELECT DISTINCT Count (WorkUnitsFaultsMainTBL.WorkUnit) AS [Total Work Units],
'No Faults' AS [Fault Type], Sum(IIf([FaultCategory]='No Faults',1,0)) AS [NoFault Totals],
FormatPercent(Sum(IIf([FaultCategory]='No Faults',1,0))/Count([WorkUnit]),2) AS [Pct NoFaults]
FROM WorkUnitsFaultsMainTBL
WHERE (((WorkUnitsFaultsMainTBL.BuildID) In ("E010","C809","F001","C810","F187","A910","M173","M174"))
And ((WorkUnitsFaultsMainTBL.TodaysDate) Between Forms!Queries_ReportsFRM!StartDateTxt And Forms!Queries_ReportsFRM!EndDateTxt))
GROUP BY WorkUnitsFaultsMainTBL.WorkUnit
HAVING COUNT(WorkUnitsFaultsMainTBL.WorkUnit) = 1;






Re: Obtain Unique Work Units via SQL

Adamus Turner

giggling sorry for the laughter, I didn't read the query with my thinking cap on...just remove DISTINCT and the HAVING clause and you should have it. Leave the GROUP BY there.

Adamus






Re: Obtain Unique Work Units via SQL

AlDeb

Adamus,

Now I am getting 415 rows of data instead of one row and totals.

Code Snippet

SELECT Count (WorkUnitsFaultsMainTBL.WorkUnit) AS [Total Work Units],
'No Faults' AS [Fault Type], Sum(IIf([FaultCategory]='No Faults',1,0)) AS [NoFault Totals],
FormatPercent(Sum(IIf([FaultCategory]='No Faults',1,0))/Count([WorkUnit]),2) AS [Pct NoFaults]
FROM WorkUnitsFaultsMainTBL
WHERE (((WorkUnitsFaultsMainTBL.BuildID) In ("E010","C809","F001","C810","F187","A910","M173","M174"))
And ((WorkUnitsFaultsMainTBL.TodaysDate) Between Forms!Queries_ReportsFRM!StartDateTxt And Forms!Queries_ReportsFRM!EndDateTxt))
GROUP BY WorkUnitsFaultsMainTBL.WorkUnit;






Re: Obtain Unique Work Units via SQL

Adamus Turner

Are you kidding You should be getting less than originally.

I'm confused.

There are plenty of ways to accomplish this.

My first thought would be to simply use DISTINCT

SELECT DISTINCT WorkUnitsFaultsMainTBL.WorkUnit, Count (WorkUnitsFaultsMainTBL.WorkUnit) AS [Total Work Units],
'No Faults' AS [Fault Type], Sum(IIf([FaultCategory]='No Faults',1,0)) AS [NoFault Totals],
FormatPercent(Sum(IIf([FaultCategory]='No Faults',1,0))/Count([WorkUnit]),2) AS [Pct NoFaults]
FROM WorkUnitsFaultsMainTBL
WHERE (((WorkUnitsFaultsMainTBL.BuildID) In ("E010","C809","F001","C810","F187","A910","M173","M174"))
And ((WorkUnitsFaultsMainTBL.TodaysDate) Between Forms!Queries_ReportsFRM!StartDateTxt And Forms!Queries_ReportsFRM!EndDateTxt));

Just don't display the first field.

Adamus






Re: Obtain Unique Work Units via SQL

AlDeb

Adamus,

It seems to be totaling up the number of records for each WorkUnit instead of giving me a total Number of

Distinct Workunits.






Re: Obtain Unique Work Units via SQL

Adamus Turner

Is the intent of your query to return 1 row or many

Adamus






Re: Obtain Unique Work Units via SQL

AlDeb

The intent of my query is to show as seen above in an earlier posting. I am showing

it again below. It would be either 0 NoFault Totals or Whatever # NoFault totals.

Fault Type--------NoFault Totals----------Total Work Units

No Faults-----------------0--------------------------197

Fault Type--------NoFault Totals----------Total Work Units

No Faults-----------------7--------------------------197






Re: Obtain Unique Work Units via SQL

Adamus Turner

AlDeb wrote:

Adamus,

It seems to be totaling up the number of records for each WorkUnit instead of giving me a total Number of

Distinct Workunits.

The DISTINCT keyword will filter the WorkUnits to only distinct values. It removes the duplicates. If you are getting 197, then there are 197 distinct WorkUnits.

I'm not sure how else to answer.

Adamus






Re: Obtain Unique Work Units via SQL

AlDeb

Adamus,

Thanks for your help but there are only 89 Distinct Work Units for this date range and a total of 197 Records because some of the WorkUnits have multiple entry's due to faults. I hope someone out there can help me solve this. I appreciate your help.