Date Query

[dazoloko via AccessMonster.com]

Dear All

Heres hoping someone can help me.

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.

Can anybody point me in the right direction. Is it possible to do with one
query or will I need a query for each week ?

Cheers

D

 

[Lou]

Dear All

Heres hoping someone can help me.

I have a field which contains a due date for an inspection. This date canbe
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.

Can anybody point me in the right direction. Is it possible to do with one
query or will I need a query for each week ?

Cheers

D

A starting point might be to find the number of inspections due each
week, numbering the weeks from 0 to 51.

SELECT CInt( ( InspectionDue - Date() ) / 7 ) as [Weeks In Future] ,
count(*) as [Total Due]
from tblMaintenceSchedule
where InspectionDue >= Date()
and InspectionDue <= Date() + 365
group by CInt( ( InspectionDue - Date() ) / 7 )

 

[MGFoster]

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

Do you have a Field that indicates an inspection took place - like a
Date field (the inspection took place on this date)? If so you can use
the DatePart() function - like this:

DatePart("ww", inspection_date)

This will give you the week of the year that the inspection took place.
You could use this in a query like this:

PARAMETERS StartDate Date, EndDate Date;
SELECT DatePart("ww", inspection_date) As TheWeek,
COUNT(*) As Inspections
FROM InspectionsTable
WHERE inspection_date BETWEEN StartDate And EndDate
GROUP BY DatePart("ww", inspection_date)

This is a parameter query that asks for a beginning date and and ending
date. It counts the number of inspections per week number between those
dates.

A week is considered a 7-day period, Sunday thru Saturday. See the VBA
Help article DatePart Function for more info on the function's
parameters.
--
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/AwUBSTwzqYechKqOuFEgEQIJuwCgqsS1Y9QgeGUdZ7qsKzn7mYPS95IAoNZG
R8MLx5m900nmZEjuauDfiAqn
=WCXU
-----END PGP SIGNATURE-----

 

[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])



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[dazoloko via AccessMonster.com]

John

I can see where you are coming from with this code, but where exactly where I
put it ?

Cheers

D

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

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Dear All

[quoted text clipped - 12 lines]

D

 

[John Spencer]

That was an entire query in SQL view.

To build the query in the design grid
--- Add the Table
--- Add DUE Date to the query TWO TIMES
--- Edit the first field to read
Format([DueDate],"yyyy-ww")
--- SELECT View: Totals from the menu
--- Change Group By to Count under the second field

Run the query.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

John

I can see where you are coming from with this code, but where exactly where I
put it ?

Cheers

D

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

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Dear All

[quoted text clipped - 12 lines]

D