Grouping Data

[Guest]

Hi, Please help...

I have a query that counts the number of days between visit and closure of a
job. This gives me a vast number of results. What I am trying to do is group
them together so that its easier to report on.

I want to combine them in this way... If the days are between 0 & 6 then
Week1, if they are between 7 & 14 then Week2, if between 15 & 21 then Week3
and if greater than 21 then Week4orMore

How is this posible to do?

Thanks,
Jez

 

[Douglas J. Steele]

Presumably you mean between 0 & 6 is week1, between 7 & 13 is week2, between
14 & 20 is week3 and so on...

If so, try:

([Days] \ 7) + 1

 

[David F Cox]

not tested, group by:-

INT([days]/7) +1 ?

 

[David F Cox]

posted better, posted quicker, and probably Douglas is handsomer too. -<)

Presumably you mean between 0 & 6 is week1, between 7 & 13 is week2,
between 14 & 20 is week3 and so on...

If so, try:

([Days] \ 7) + 1

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi, Please help...

I have a query that counts the number of days between visit and closure
of a
job. This gives me a vast number of results. What I am trying to do is
group
them together so that its easier to report on.

I want to combine them in this way... If the days are between 0 & 6 then
Week1, if they are between 7 & 14 then Week2, if between 15 & 21 then
Week3
and if greater than 21 then Week4orMore

How is this posible to do?

Thanks,
Jez

 

[Guest]

Jez:

Add the following function to a standard module:

Public Function GetWeekGroup(dtmVisit As Date, dtmClosure As Date) As String

Dim intDays As Integer

intDays = DateDiff("d", dtmVisit, dtmClosure)

Select Case intDays
Case Is <= 6
GetWeekGroup = "Week 1"
Case 7 To 14
GetWeekGroup = "Week 2"
Case 15 To 21
GetWeekGroup = "Week 3"
Case Else
GetWeekGroup = "Week 4 or later"
End Select

End Function

Call the function in the query, passing the Visit date and the Closure date
into it, e.g.

WeekGroup:GetWeekGroup([VisitDate], [ClosureDate])

and group the query on the WeekGroup column.

Ken Sheridan
Stafford, England

 

[Guest]

Thanks Ken. Works perfect.

Jez:

Add the following function to a standard module:

Public Function GetWeekGroup(dtmVisit As Date, dtmClosure As Date) As String

Dim intDays As Integer

intDays = DateDiff("d", dtmVisit, dtmClosure)

Select Case intDays
Case Is <= 6
GetWeekGroup = "Week 1"
Case 7 To 14
GetWeekGroup = "Week 2"
Case 15 To 21
GetWeekGroup = "Week 3"
Case Else
GetWeekGroup = "Week 4 or later"
End Select

End Function

Call the function in the query, passing the Visit date and the Closure date
into it, e.g.

WeekGroup:GetWeekGroup([VisitDate], [ClosureDate])

and group the query on the WeekGroup column.

Ken Sheridan
Stafford, England

Hi, Please help...

I have a query that counts the number of days between visit and closure of a
job. This gives me a vast number of results. What I am trying to do is group
them together so that its easier to report on.

I want to combine them in this way... If the days are between 0 & 6 then
Week1, if they are between 7 & 14 then Week2, if between 15 & 21 then Week3
and if greater than 21 then Week4orMore

How is this posible to do?

Thanks,
Jez