Simple Week over Week Query

[Guest]

I need to run a simple Week over Week query for only one field (i.e. Total
Revenue) and I need the query, without input from the user, to automatically
return the data for the current week and then the previous week.

 

[Jeff Boyce]

That will depend on your data structure.

If you have a table that records amounts, along with dates, you'll first
have to determine in which week each amount falls, then sum the amount by
week. This approach would give you greater flexibility in reporting.

Another structure might be to record Week#, and WeeklyTotal. In this case
you'd need to select by Week# and use Week#(today) and Week#(today)-1.

Good luck

Jeff Boyce
<Access MVP>

 

[Guest]

Jeff, the first part of your response is what I need. The structure of my
data is as follows:

Customer
Total Revenue
Guarantee Date

Say this week is 27; I want all Total Revenue data from week 27 and then to
also display Week 26 as well in the following format:

Customer 27 26
Customer 1 $1000 $950
Customer 2 $900 $900

where all of the $ figures are from the Total Revenue field. Does this help?

 

[Jeff Boyce]

Take a look at Access/HELP on DatePart() function to find "week".

Jeff Boyce
<Access MVP>

 

[Guest]

I did but Help mentions nothing of Week. It has Month (mmm) or Quarter (q)
but it states nothing of week. When I run:

TravelYear: DatePart('yyyy',[Travel Date])

it works fine but if I try:

TravelYear: DatePart('w',[Travel Date])
or
TravelYear: DatePart('ww',[Travel Date])

it gives me an "Invalid Procedure Call" error message.

 

[Jeff Boyce]

'ww' works for me ...

In the Immediate window, I got:

?DatePart("ww",Date())
29

Are you using double-quotes?

Jeff Boyce
<Access MVP>