Date Query

[dazoloko via AccessMonster.com]

Dear All

I sent in this post a little while ago :-

I have a field which contains a due date for an inspection. This date can be
between now and a year from now.

Where I want to get to is to create a report that basically looks at Todays
date and counts week by week the number of inspections due in each week.

And got this response :- (from John Spencer)

SELECT Format([DueDate],"yyyy-ww") as WeekID
, Count([DueDate]) as CountDue
FROM [Some table]
GROUP BY Format([DueDate],"yyyy-ww")

Or you could use
SELECT DateDiff("ww",Date(),[DueDate]) as WeeksinFuture
, Count([DueDate]) as CountDue
FROM [Some table]
GROUP BY DateDiff("ww",Date(),[DueDate])

It does what I wanted it to do but now I want to take it a step further if
possible. I have tried but find myself at a loss.

I want to be able to alter the query so it shows the only the 52 weeks from
week beginning the following Monday including the weeks where there are no
due dates as a 0.

Hope this makes sense.

Cheers

D

 

[MGFoster]

Dear All

I sent in this post a little while ago :-

I have a field which contains a due date for an inspection. This date can be
between now and a year from now.

Where I want to get to is to create a report that basically looks at Todays
date and counts week by week the number of inspections due in each week.

And got this response :- (from John Spencer)

SELECT Format([DueDate],"yyyy-ww") as WeekID
, Count([DueDate]) as CountDue
FROM [Some table]
GROUP BY Format([DueDate],"yyyy-ww")

Or you could use
SELECT DateDiff("ww",Date(),[DueDate]) as WeeksinFuture
, Count([DueDate]) as CountDue
FROM [Some table]
GROUP BY DateDiff("ww",Date(),[DueDate])

It does what I wanted it to do but now I want to take it a step further if
possible. I have tried but find myself at a loss.

I want to be able to alter the query so it shows the only the 52 weeks from
week beginning the following Monday including the weeks where there are no
due dates as a 0.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You'll require a date table that holds the week dates and the week
numbers. Then use that table with your work table to get the zero due
dates.

Weeks table:
week_nbr - the week number
week_date - the week begin or end date, which ever you want

The query:

PARAMETERS [Start date] Date, [End date] Date;
SELECT W.week_nbr as WeeksinFuture
, Nz(Count(T.[DueDate]),0) as CountDue
FROM [Some table] AS T, Weeks AS W
WHERE W.week_date BETWEEN [Start date] And [End date]
GROUP BY W.week_nbr

If you don't want to put in the [End date] you can use this WHERE
clause:

PARAMETERS [Start date] Date;
.....
WHERE W.week_date BETWEEN [Start date] And DateAdd("ww", 51, [Start
date])

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBShM9sIechKqOuFEgEQLaAACffIFWanfnhkVX1ka3v0szANyRM6QAoPlp
U0hO/NMDf1TVXZpckgxt1ROM
=uNQA
-----END PGP SIGNATURE-----