Distinct count for each day

[CJ]

Hi Groupies

I know that there are a number of posts regarding Distinct Count on the net
but I just can not make mine work.

I have tblDailyBoxes that keeps track of who planted which flowers from
which box. I need to count the number of different planters each day and
show the date and the count in the result.

I have figured out the SQL to show just the total number of different
planters for the entire table with:

SELECT Count(tblDailyBoxes.lngEmpID) AS PlanterCount
FROM [SELECT DISTINCT lngEmpId FROM tblDailyBoxes]. AS TEST;

However, I can not get my head around the syntax for including the field,
dtmDate, from the same table.

Can somebody please help me out with this before I lose it completely!!

 

[John W. Vinson]

Hi Groupies

I know that there are a number of posts regarding Distinct Count on the net
but I just can not make mine work.

I have tblDailyBoxes that keeps track of who planted which flowers from
which box. I need to count the number of different planters each day and
show the date and the count in the result.

I have figured out the SQL to show just the total number of different
planters for the entire table with:

SELECT Count(tblDailyBoxes.lngEmpID) AS PlanterCount
FROM [SELECT DISTINCT lngEmpId FROM tblDailyBoxes]. AS TEST;

However, I can not get my head around the syntax for including the field,
dtmDate, from the same table.

Can somebody please help me out with this before I lose it completely!!

What's in dtmDate - just a date, or a date and time?

What trouble are you having?

If dtmDate is a pure date (e.g. #2/18/2010#) you should be able to group by
it:

SELECT dtmDate, Count(tblDailyBoxes.lngEmpID) AS PlanterCount
FROM [SELECT DISTINCT lngEmpId, dtmDate FROM tblDailyBoxes]. AS TEST
GROUP BY dtmDate;

If it contains a time component use the DateValue function:

SELECT TheDate, Count(tblDailyBoxes.lngEmpID) AS PlanterCount
FROM [SELECT DISTINCT lngEmpId, DateValue(dtmDate) AS TheDate FROM
tblDailyBoxes]. AS TEST
GROUP BY dtmDate;

 

[CJ]

I did not have dtmDate(just a date) in the Select Distinct statement.
It makes so much sense after you see it written but sometimes getting to
that point is brutally painful.
I guess it's one of those things you need to do a few times.

Thanks John!

--
Thanks for taking the time!

CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!

Hi Groupies

I know that there are a number of posts regarding Distinct Count on the
net
but I just can not make mine work.

I have tblDailyBoxes that keeps track of who planted which flowers from
which box. I need to count the number of different planters each day and
show the date and the count in the result.

I have figured out the SQL to show just the total number of different
planters for the entire table with:

SELECT Count(tblDailyBoxes.lngEmpID) AS PlanterCount
FROM [SELECT DISTINCT lngEmpId FROM tblDailyBoxes]. AS TEST;

However, I can not get my head around the syntax for including the field,
dtmDate, from the same table.

Can somebody please help me out with this before I lose it completely!!

What's in dtmDate - just a date, or a date and time?

What trouble are you having?

If dtmDate is a pure date (e.g. #2/18/2010#) you should be able to group
by
it:

SELECT dtmDate, Count(tblDailyBoxes.lngEmpID) AS PlanterCount
FROM [SELECT DISTINCT lngEmpId, dtmDate FROM tblDailyBoxes]. AS TEST
GROUP BY dtmDate;

If it contains a time component use the DateValue function:

SELECT TheDate, Count(tblDailyBoxes.lngEmpID) AS PlanterCount
FROM [SELECT DISTINCT lngEmpId, DateValue(dtmDate) AS TheDate FROM
tblDailyBoxes]. AS TEST
GROUP BY dtmDate;