Ofer,
I respectfully disagree about your comment on DateSerial. Not being able to
handle negative numbers. It does so easily and correctly.
The advantage that DateAdd has is that it can handle many different
increments (Years, Weeks, Days, Hours, Minutes, Seconds). The advantage
that date serial has is that it can perform things with dates that require
some more complex code if you want to get the first day of the previous
month based on the current date.
For instance
?DateSerial(Year(#1/21/2007#),Month(#1/21/2007#)-2,1) returns 11/1/2006
Admiral,
Sir with all respect are you trying to get count for each day in a period
SELECT Attendance.Date, Count([Last Name]) as CountOfEntries
FROM Attendance
WHERE Attendance.Date Between #1/1/2005# and #12/31/2006#
GROUP BY Attendance.Date
OR are you trying to get a monthly total for the time frame
SELECT Format(Attendance.Date, "YYYY-MM"), Count([Last Name]) as
CountOfEntries
FROM Attendance
WHERE Attendance.Date Between #1/1/2005# and #12/31/2006#
GROUP BY Format(Attendance.Date, "YYYY-MM")
OR are you trying to get a running 90 day total - That is for each date in
the query range sum up the attendance for the prior 90 days.
SELECT attendance.Date,
(SELECT Count(A.Date)
FROM Attendance as A
WHERE A.Date Between DateAdd("d",-90,Attendance.Date)
and Attendance.Date) as 90DayCount
FROM Attendance
WHERE Attendance.Date Between #1/1/2005# and #12/31/2006#
OR are you trying to do something else.
ALL of the above queries are non-tested, AIR CODE.
Capt. J. Spencer, USAF (Retired)
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
Ofer Cohen said:
Let start with the fact that
DateSerial(Year([Date]),Month([Date])-3,Day([Date]))
Is not a good way of getting the start date
1/1/2007 will return -2 for the month
So, you should have used the DateAdd function to subtruct 3 month's from
the
date
DateAdd("m",-3,[Date])
***************************
But for your case, if you want the number of entries in the last three
month's try
Select Count(*) As CountOfEntry From attendance Where [Date] Between
DateAdd("m",-3,Date()) And Date()
Just copy this SQL and try it
***************************
Note: It's not good to use reserved words in Access (Like Date) as Fields
names, it requires you to put it in square brackets
--
Good Luck
BS"D
Admiral O. F. Doom said:
Here are the 2 SQL things that I wrote (that aren't working):
SELECT attendance.Date, Count(attendance.[Last Name]) AS [CountOfLast
Name],
DateSerial(Year([Date]),Month([Date])-3,Day([Date])) AS start,
attendance.Date AS [end],
[date]-DateSerial(Year([Date]),Month([Date])-3,Day([Date])) AS days
FROM attendance
GROUP BY attendance.Date,
DateSerial(Year([Date]),Month([Date])-3,Day([Date])), attendance.Date,
[date]-DateSerial(Year([Date]),Month([Date])-3,Day([Date]));
SELECT DSum("[CountOfLast Name]","qsel_daycount","[date]>[start] and
[date]<[end]") AS dc00, qsel_daycount.days, [dc00]/[days] AS Expr1
FROM qsel_daycount;
Thank you.
:
Post your SQL!
--
Email address is not valid.
Please reply to newsgroup only.
:
That is essentially the query I have been trying to run. No luck.
I guess what I have been trying to do is get an entry that starts
with the
date that is on that row, subtracting 90 days from that, and counting
the
records between those dates for each row. Can that even be done?
:
What is the criteria you are using?
Something like
Select Count(*) As CountOfTimeIn
From TableNAme
Where [Time In] Between DateAdd("d",-90,[DateValue]) And
[DateValue]
I'm not sure where the DateValue is comming from, but replace it
with the
real one
--
Good Luck
BS"D
:
Hmmm....
The table structure is pretty straight forward: Date, Last Name,
First
name, Last Four (of SSN), Time In and Time Out.
When people enter the lab, they sign in on a sign-in sheet. Me
and my staff
type it into the database at the end of the day or the next day.
I have one query already that counts the number of people by day.
That
query simply does a Dcount on the database by day, and groups the
days
together.
What I was trying to do is make another query that takes the
date, subtracts
90 days from it, and then counts the records between the start
date and the
end date. With no luck.
I have started writing a report to pull the data out the way I
need to, but
that requires three formulas for each day...and the guy that
wants this
report wants to go back three YEARS!!!
Is that any help?
:
It would be significantly easier if you would post your table
structure. I
suppose that when people enter the "lab", they have to log in
or something.
Do you want to identify the number of unique people entering
the lab, or
every time someone logs in? Do you want to count over the
entire 90 days, or
get a by date count?
Dale
--
Email address is not valid.
Please reply to newsgroup only.
:
I have a database that contains dates, names, and other
things that relate to
the attendance.
What I am trying to do is dynamically count the number of
people who were in
the lab for the past 90 days, and show it on a calendar. The
problem is that
I can't get the query to count the records correctly. I
either get ALL of
the records, which I seriously doubt that there were 3500+
visits the past 90
days, or I get NO records...
What I would like to do is, if the calendar is pointing to
May 1, 2007, it
would show the number of people in the lab for the 90 days
prior to that. I
can get the calendar to show that data...I just can't get the
data out of the
database right.
Any help would be appreciated.
