how do I create a query that will sum the records of the past week in ms access 2003

how do I create a query that will sum the records of the past week in ms access 2003
Dov_BAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
you may need to use Date()  instead of Date

from sunday through thursday, use this

select sum([FieldName])
from tableName
where [dateField] between Dateadd("d", -7 - Weekday(Date(), 2),date()) and dateadd("d",-3-weekday(date(),2),Date())
0
 
Rey Obrero (Capricorn1)Commented:
by past week, what does it include, last week monday to Sunday?
0
 
Aaron TomoskyConnect With a Mentor SD-WAN SimplifiedCommented:
Select sum(sumcolumn) from tablename
Where datecolum between('2011-03-01','2011-03-07')
group by sumcolumn
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Dov_BAuthor Commented:
I sum from sunday through thursday
how do I write the sql in msa access so its not hardcoded and does not need to be rewritten each week
0
 
Rey Obrero (Capricorn1)Commented:
for last monday and last friday dates as past week

select sum([FieldName])
from tableName
where [dateField] between Dateadd("d", - 6 - Weekday(Date, 2),date) and dateadd("d",-2-weekday(date,2),Date)
0
 
Rey Obrero (Capricorn1)Commented:
from sunday through thursday, use this

select sum([FieldName])
from tableName
where [dateField] between Dateadd("d", -7 - Weekday(Date, 2),date) and dateadd("d",-3-weekday(date,2),Date)
0
 
Dov_BAuthor Commented:
Thanks you have beeen a great help!
when I run the query it asks me to enter parameter value
how can I get it to run without asking me anything?
0
 
Dov_BAuthor Commented:
actualy I added the brackets and it stopped asking me for a parameter but did not return any records for this week unless I changed my system date to the 15 th
0
 
Gustav BrockConnect With a Mentor CIOCommented:
This should do. Find first and last date of this week, subtract one week for both, and subtract further two from the last date (Saturday) to obtain Thursday:

select
  sum([FieldName])
from
  tableName
where
  [dateField] between
    DateAdd("d", 1 - WeekDay(Date(), 1) - 7, Date())
    And
    DateAdd("d", 7 - WeekDay(Date(), 1) - 7 - 2, Date());

/gustav
0
 
Rey Obrero (Capricorn1)Commented:
Dov_B,

upload a copy of the db
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.