M
Mark
Hi All,
I’m trying to do a simple query counting the number of distinct
“nest_to_cont†grouped by ship_zone and datepart(“Hâ€, dtimecre).
Not sure why I can’t get this to work. I can create another query using the
distinct on “nest_to_cont†then build a query on it, but I know there is a
better way.
Right now I am counting all “Nest_to_Cont†no matter if it is a duplicate
number. With the tbMain data below I expect the report to show the results
of:
Ship_zone, DTIMECRE, Nest_to_cont
802 8 1
803 8 1
What I’m getting is:
Ship_zone, DTIMECRE, Nest_to_cont
802 8 3
803 8 2
How do I count this field as distinct and not count the duplicates?
Data:
tbMain
SHIP_ZONE DTIMECRE NEST_TO_CONT
802 1/23/2009 8:22:15 AM 000312820802
802 1/23/2009 8:22:32 AM 000312820802
802 1/23/2009 8:56:20 AM 000312820802
803 1/23/2009 8:10:29 AM 000312839802
803 1/23/2009 8:10:58 AM 000312839802
Query I have now that is getting the wrong results.
SELECT tbMain.SHIP_ZONE, DatePart("h",[DTIMECRE]) AS [Work Hour],
Count(tbMain.NEST_TO_CONT) AS CountOfNEST_TO_CONT
FROM tbMain
GROUP BY tbMain.SHIP_ZONE, DatePart("h",[DTIMECRE]);
Query Results (not based on data above)
Query
SHIP_ZONE Work Hour CountOfNEST_TO_CONT
802 8 218
802 9 17
803 8 121
I’m trying to do a simple query counting the number of distinct
“nest_to_cont†grouped by ship_zone and datepart(“Hâ€, dtimecre).
Not sure why I can’t get this to work. I can create another query using the
distinct on “nest_to_cont†then build a query on it, but I know there is a
better way.
Right now I am counting all “Nest_to_Cont†no matter if it is a duplicate
number. With the tbMain data below I expect the report to show the results
of:
Ship_zone, DTIMECRE, Nest_to_cont
802 8 1
803 8 1
What I’m getting is:
Ship_zone, DTIMECRE, Nest_to_cont
802 8 3
803 8 2
How do I count this field as distinct and not count the duplicates?
Data:
tbMain
SHIP_ZONE DTIMECRE NEST_TO_CONT
802 1/23/2009 8:22:15 AM 000312820802
802 1/23/2009 8:22:32 AM 000312820802
802 1/23/2009 8:56:20 AM 000312820802
803 1/23/2009 8:10:29 AM 000312839802
803 1/23/2009 8:10:58 AM 000312839802
Query I have now that is getting the wrong results.
SELECT tbMain.SHIP_ZONE, DatePart("h",[DTIMECRE]) AS [Work Hour],
Count(tbMain.NEST_TO_CONT) AS CountOfNEST_TO_CONT
FROM tbMain
GROUP BY tbMain.SHIP_ZONE, DatePart("h",[DTIMECRE]);
Query Results (not based on data above)
Query
SHIP_ZONE Work Hour CountOfNEST_TO_CONT
802 8 218
802 9 17
803 8 121