Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 449
  • Last Modified:

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
0
Dov_B
Asked:
Dov_B
3 Solutions
 
Rey Obrero (Capricorn1)Commented:
by past week, what does it include, last week monday to Sunday?
0
 
Aaron TomoskyTechnology ConsultantCommented:
Select sum(sumcolumn) from tablename
Where datecolum between('2011-03-01','2011-03-07')
group by sumcolumn
0
 
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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
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
 
Rey Obrero (Capricorn1)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
 
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 BrockCIOCommented:
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

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now