COUNT DISTINCT RECORDS 1

[netrusher]

I have a query that I am trying to obtain just totals in the output:

Primary: DCount("[ClockNbr]","1-StationVerifyTbl","[Locations]='Assembly 1-3Ton' and [StaPrimary]=True")
Back: DCount("[ClockNbr]","1-StationVerifyTbl","[Locations]='Assembly 1-3Ton' and [StaUp]=True")
Up: DCount("[ClockNbr]","1-StationVerifyTbl","[Locations]='Assembly 1-3Ton' and [StaBack]=True")

This works and gives me the total I need for those fields.

I also need a totals for another field that I will call people. There could be 100 records (ClockNbr) in this field but I only want to see the total number of Distinct Records. I would like the results to be shown as follows:

People Primary Up Back
48 158 125 130

I am getting these results in the Primary Up & Back but I need the count for the People Column to be count of
Distinct Records. I hope this makes sense and please help. I would like the Distinct count of ClockNbr with the same criteria as above.

 

[PHV]

Something like this (SQL code ?)
SELECT (SELECT Count(*) FROM (SELECT DISTINCT ClockNbr FROM [1-StationVerifyTbl] WHERE Locations = 'Assembly 1-3Ton') AS D) AS People
,Abs(Sum(StaPrimary)) AS Primary ,Abs(Sum(StaUp)) AS Up ,Abs(Sum(StaBack)) AS Back
FROM [1-StationVerifyTbl]
WHERE Locations = 'Assembly 1-3Ton'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[netrusher]

Can anyone tell me what the AS D means in the sql.

Below is my SQL for First Shift People. My total for Distinct Count of Clock Numbers is 95.

SELECT (SELECT Count(*) FROM (SELECT DISTINCT ClockNbr FROM [1-StationVerifyTbl] WHERE Locations = 'Assembly 1-3Ton' AND 'FirstShift'=True) AS D) AS People, Abs(Sum(StaPrimary)) AS [Primary], Abs(Sum(StaUp)) AS Up, Abs(Sum(StaBack)) AS Back
FROM [1-StationVerifyTbl]
WHERE ((([1-StationVerifyTbl].Locations)='Assembly 1-3Ton') AND (([1-StationVerifyTbl].FirstShift)=True));

Below is my SQL for Second Shift People. My total for
Distinct Count of Clock Numbers is 95. I do not think the two queries should have the same total of 95 Distinct Clock Numbers. Can anyone tell what is wrong.

SELECT (SELECT Count(*) FROM (SELECT DISTINCT ClockNbr FROM [1-StationVerifyTbl] WHERE Locations = 'Assembly 1-3Ton' AND 'SecondShift'=True) AS D) AS People, Abs(Sum(StaPrimary)) AS [Primary], Abs(Sum(StaUp)) AS Up, Abs(Sum(StaBack)) AS Back
FROM [1-StationVerifyTbl]
WHERE ((([1-StationVerifyTbl].Locations)='Assembly 1-3Ton') AND (([1-StationVerifyTbl].SecondShift)=True));

 

[PHV]

Replace this:
AND 'FirstShift'=True
with this:
AND FirstShift=True

Ditto for 'SecondShift'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[belovedcej]

The As D was just a renaming of the field. An AS was required there because otherwise the system wouldn't know what to call the column.

 

[PHV]

In fact, D is an alias for naming the inline view.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[netrusher]

Thanks,

That works. Can anyone tell me how to find what the (['" are used for and in what context. I am very confused on when or how to use these symbols.