Weekly Parameters in a Crosstab Query

[Boxcar74]

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

[MMcCarthy]

Hi Boxcar

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

WHERE DateDiff("w", [Date], Date()-(Weekday(Date())+1)) <= 13
 

[Boxcar74]

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!

[MMcCarthy]

Try this ...

WHERE DateDiff("w", [Date], Date()-(Weekday(Date())+8)) <= 13
 

[Boxcar74]

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 !!!

[MMcCarthy]

Try keeping the same pivot at the old query.

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 [DATE]-Weekday([DATE])+1;
 

[Boxcar74]

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 !!!!!!

[MMcCarthy]

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 :)