By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,640 Members | 1,137 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,640 IT Pros & Developers. It's quick & easy.

Weekly Parameters in a Crosstab Query

Boxcar74
P: 42
Hi,

This is probably easy but Iím canít find the answer. Iím using Access 2003 (mdb).

Ok Here is my Issue Iím running a crosstab query to retrieve data for the Last 13 weeks from a single date field.

I can get the data using the Parameters:

WHERE ((([DATE]-Weekday([DATE])+1) Between Date()-96 And Date()-7))

This will work but on some days I get the current week.

I tried Between Week()-14 And Week()-1)) and I tried Between Weekly()-14 And Weekly()-1)).

With Both I get a ďUndefined Function ĎWeekí in ExpressionĒ Error.

So my Question is there a Week function for parameters?

If not any recommendations for the parameter?

Not a big deal for me now but Iím running this expression in 30 or 40 queries and I want it to be efficient and get it right the first time.

Thanks,
Boxcar
May 23 '07 #1
Share this Question
Share on Google+
7 Replies

MMcCarthy
Expert Mod 10K+
P: 14,534
Hi Boxcar

I'm not sure exactly what you are looking for but try something like this ...

Expand|Select|Wrap|Line Numbers
  1. WHERE DateDiff("w", [Date], Date()-(Weekday(Date())+1)) <= 13
  2.  
May 25 '07 #2

Boxcar74
P: 42
That is what I was looking for!!!!!! (well kind of)

But it is pulling this weeks (the current week) data as well.

So today is 5/29 now I get 5/27 and twelve other weeks.

But I need 5/20 and twelve weeks back.

I keep trying different expression, but none work. Iíll probably get it.

But if anyone know an easy way please post it.

Thanks!
May 29 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
Try this ...

Expand|Select|Wrap|Line Numbers
  1. WHERE DateDiff("w", [Date], Date()-(Weekday(Date())+8)) <= 13
  2.  
May 29 '07 #4

Boxcar74
P: 42
Sorry to bother you.

I feel like an Idiot +8 made sense. But now I get days going back 13 weeks.

Here is the code
TRANSFORM Sum(DailyCSTMs.COUNT) AS SumOfCOUNT
SELECT DailyCSTMs.CATEGORY, Sum(DailyCSTMs.COUNT) AS Total
FROM DailyCSTMs
WHERE (((DateDiff("ww",[Date],Date()-(Weekday(Date())+8)))<=13))
GROUP BY DailyCSTMs.CATEGORY
PIVOT DailyCSTMs.DATE;


Not sure if I mentioned this but as you can see it is a Crosstab Query.

And each record in the DB is by individual date.

It works this way I was just trying to find a better way:
TRANSFORM Sum(DailyCSTMs.COUNT) AS SumOfCOUNT
SELECT DailyCSTMs.CATEGORY, Sum(DailyCSTMs.COUNT) AS Total
FROM DailyCSTMs
WHERE ((([DATE]-Weekday([DATE])+1) Between Date()-96 And Date()-7))
GROUP BY DailyCSTMs.CATEGORY
PIVOT [DATE]-Weekday([DATE])+1;


I'm Trying to make it Idiot Proof. Because I have Many reports running this query (and similar ones) at different time of the week.

I think one i got works fine.

mmccarthy If you have any Ideas or a better soulution it is appreicated.

Thansk you this site is great !!!
May 30 '07 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
Try keeping the same pivot at the old query.
Expand|Select|Wrap|Line Numbers
  1. TRANSFORM Sum(DailyCSTMs.COUNT) AS SumOfCOUNT
  2. SELECT DailyCSTMs.CATEGORY, Sum(DailyCSTMs.COUNT) AS Total
  3. FROM DailyCSTMs
  4. WHERE (((DateDiff("ww",[Date],Date()-(Weekday(Date())+8)))<=13))
  5. GROUP BY DailyCSTMs.CATEGORY
  6. PIVOT [DATE]-Weekday([DATE])+1;
  7.  
May 30 '07 #6

Boxcar74
P: 42
For some Reason that query had 16 weeks including this week.

With some trial and error. I don't know why or how.

I got this to work:
TRANSFORM Sum(DailyCSTMs.COUNT) AS SumOfCOUNT
SELECT DailyCSTMs.CATEGORY, Sum(DailyCSTMs.COUNT) AS Total
FROM DailyCSTMs
WHERE (((DateDiff("ww",[Date],Date()-(Weekday(Date())+1)))>-1 And (DateDiff("ww",[Date],Date()-(Weekday(Date())+1)))<=12))
GROUP BY DailyCSTMs.CATEGORY
PIVOT [DATE]-Weekday([DATE])+1;


It may not be the best but is works.

Thank You Very Much !!!!!!
May 30 '07 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
For some Reason that query had 16 weeks including this week.

With some trial and error. I don't know why or how.

I got this to work:
TRANSFORM Sum(DailyCSTMs.COUNT) AS SumOfCOUNT
SELECT DailyCSTMs.CATEGORY, Sum(DailyCSTMs.COUNT) AS Total
FROM DailyCSTMs
WHERE (((DateDiff("ww",[Date],Date()-(Weekday(Date())+1)))>-1 And (DateDiff("ww",[Date],Date()-(Weekday(Date())+1)))<=12))
GROUP BY DailyCSTMs.CATEGORY
PIVOT [DATE]-Weekday([DATE])+1;


It may not be the best but is works.

Thank You Very Much !!!!!!
You're welcome. If it works don't mess with it :)
May 31 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.