homepage Welcome to WebmasterWorld Guest from 54.197.189.108
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
Count Date
MS Access
jefferson




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

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




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

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




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

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




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

In Access (only)

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

jefferson




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

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 ¦

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved