Help with query: DateDiff and counting

  • Thread starter Thread starter Emma
  • Start date Start date
E

Emma

Hi. I am new to Access and getting along okay, but cannot
handle queries for showing data in a subsequent
report/graph.

I want to compare DateOpened with DateClosed to see how
long lead times are in weeks. My query says:

#ofWeeks: DateDiff("d",[DateOpened],[DateClosed])/7.

Works fine, no problems:

JobID DateOpened DateClosed #Weeks
1 01-Feb-04 01-Mar-04 4.14285714285714
2 02-Feb-04 04-Feb-04 0.285714285714286
3 15-Mar-04 01-Apr-04 2.42857142857143
etc...

What is doing me in is how to group the number of records
by the number of weeks it takes to close jobs:

Time to close #ofJobs
1 week or less 1
2 weeks 2
3 weeks 10
etc...
Avg time to close 4.3 weeks
Still Open 5 (ClosedDate is null)

Besides not being able to figure out how to write all this
into the query grid, how do you handle the fact that some
jobs may take months to close!??!

Can someone help me?
 
Subtract one day, perform integer division, and then add 1 on again:

Weeks: 1 + (DateDiff("d", [DateOpened], [DateClosed]-1) \ 7)
 
Alan, you are truly amazing! I could not have thought of
this in my wildest dreams. You're my hero - thank you SO
much!
Emma
-----Original Message-----
Subtract one day, perform integer division, and then add 1 on again:

Weeks: 1 + (DateDiff("d", [DateOpened], [DateClosed]-1) \ 7)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi. I am new to Access and getting along okay, but cannot
handle queries for showing data in a subsequent
report/graph.

I want to compare DateOpened with DateClosed to see how
long lead times are in weeks. My query says:

#ofWeeks: DateDiff("d",[DateOpened],[DateClosed])/7.

Works fine, no problems:

JobID DateOpened DateClosed #Weeks
1 01-Feb-04 01-Mar-04 4.14285714285714
2 02-Feb-04 04-Feb-04 0.285714285714286
3 15-Mar-04 01-Apr-04 2.42857142857143
etc...

What is doing me in is how to group the number of records
by the number of weeks it takes to close jobs:

Time to close #ofJobs
1 week or less 1
2 weeks 2
3 weeks 10
etc...
Avg time to close 4.3 weeks
Still Open 5 (ClosedDate is null)

Besides not being able to figure out how to write all this
into the query grid, how do you handle the fact that some
jobs may take months to close!??!

Can someone help me?
Click to expand...


.
Click to expand...
 
Back
Top