Count Date

MS Access

[jefferson]

i'm using ms access, and i need to take the count of part of a date. all dates are in the format:
"mm-dd-yyyy hh:nn" I need to count how many records there are from just 1 day. What is the SQL statement to do so.

[arran]

I need to count how many records there are from just 1 day.

Can you please clarify this? Do you mean you want to count all records for a particular day?

Also, what code have you got so far?

arran.

[jefferson]

I would like to count all records for a certain day.

Sample Record:
Time in:"10-05-2005 15:39"
ED MD:"Name"

I need the total records for the day, then the total records where [ed md] is not null

SELECT Timein, Count(timein), Count([ED MD]) AS [Peg Visits]FROM Accounts

when this statement is run, it just gives 1 for each count, because it count the timein field with the Date, and the Time. I just want it to use the date to count with.

[aspdaddy]

In Access (only)

SELECT DateValue(timein), Count(DateValue(timein)) AS [Peg Visits]
FROM Accounts
GROUP BY DateValue(timein)

[jefferson]

That works great, just one thing left out. Check out this statement. The ED Visits are the total for the day. The Peg Visits should be the total of the EDMD for the day. (where the EDMD field is not null)
This statement just returns the total for the day, whether or not there is something in the field or not.

Can't use a where edmd is not null statement, because that won't total the ED Visits for the day.

SELECT DateValue(timein) AS [Date], Count(DateValue(timein)) AS [ED Visits], Count(EDMD) AS [Peg Visits]
FROM acctinfo
GROUP BY DateValue(timein);

Example records:

TimeIn¦ EDMD ¦

10-05-2005 15:30 ¦Jones¦
10-05-2005 13:28 ¦(null)¦
01-01-2005 11:00 ¦Smith ¦

Results should be:

Date¦ED Visits¦Peg Visits¦

10-05-2005 ¦2 ¦1 ¦
01-01-2005 ¦1 ¦1 ¦