Create customizable ranges?

  • Thread starter Thread starter park.walter
  • Start date Start date
P

park.walter

Good morning.

I am using Access 2003. I have a query that calculates the difference
between a clinic visit date to a sample collection date (Diff). I
would like to sort the "Diff" results into a variety of customizable
ranges (say 0-10, 11-20, 21-30, etc.) and then enter some sort of text
into a field at the end of the query.

I have done this in the past with IIF statements which work well but
the ranges need to be determined once and aren't flexible (the formula
needs to be manually altered). I am hoping it is possible to create a
simple table to house the range information and somehow link this
information to the query?

Is it possible to do this?

wdp
 
Expr2: Partition(DateDiff("d",[clinic visit date], [sample collection
date]),1,10000,10)
 
Expr2: Partition(DateDiff("d",[clinic visit date], [sample collection
date]),1,10000,10)
Click to expand...

Karl-

I am not familiar with what you suggested.

I did figure out a solution by doing the following:
In a new table I have 3 fields:
"Diff Range" = entire field is populated with the same text "Diff
Range"
"DiffRangeMIN" = numeric field.
"DiffRangeMAX" = numeric field.

In the table I entered 7 different ranges.

I then added a field to the existing query (the one that calculated
the "Diff") and called it "Diff Range".

In a second query I then linked the table to the first query by the
Diff Range field and used two different fields to calculate
DiffRangeMIN > Diff and DiffRangeMAX > DIFF.

This provided me a method to have as many Diff Ranges as I want, as
many overlapping as I want or whatever.

Maybe it was a bit tedious but it works....:)

wdp
 
Back
Top