Welcome to WebmasterWorld Guest from 54.163.35.238

Forum Moderators: open

Message Too Old, No Replies

Count Date

MS Access

     

jefferson

3:26 pm on Oct 10, 2005 (gmt 0)

10+ Year Member



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

3:32 pm on Oct 10, 2005 (gmt 0)

10+ Year Member



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

3:51 pm on Oct 10, 2005 (gmt 0)

10+ Year Member



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

6:34 pm on Oct 10, 2005 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



In Access (only)

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

jefferson

7:12 pm on Oct 10, 2005 (gmt 0)

10+ Year Member



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 ¦

 

Featured Threads

Hot Threads This Week

Hot Threads This Month