Stuart,
I'm assuming you want these results in a query.
In cases like this, I have a table (tblNumbers) which contains a single
field (lngNumber) with values 0 through 9.
I then create a query (qryNumbers) based on this table to generate numbers
in the range I need. For example, a query with numbers from 0 to 999 would
look like:
SELECT Hundreds.lngNumber * 100 + Tens.lngNumber * 10 + Ones.lngNumber as
lngNumber
FROM tblNumbers as Hundreds, tblNumbers as Tens, tblNumbers as Ones
Now, to anwer your question. I created a table (tbl_Roads) and entered
values for fields RoadName, Start and End (keep in mind that these are
numeric values in line with your 0 and 490). I then created the following
query:
SELECT tbl_Roads.RoadName,
IIf([tbl_Roads].[Start]<=[lngNumber]*[Interval],[lngNumber]*[Interval],[tbl_Roads].[Start])-([lngNumber]*[Interval]>[tbl_Roads].[Start])
AS Start,
IIf(([lngNumber]+1)*[Interval]<[tbl_Roads].[End],([lngNumber]+1)*[Interval],[tbl_Roads].[End]) AS [End]
FROM tbl_Roads, qryNumbers
WHERE
(((IIf([tbl_Roads].[Start]<=[lngNumber]*[Interval],[lngNumber]*[Interval],[tbl_Roads].[Start])-([lngNumber]*[Interval]>[tbl_Roads].[Start]))<[tbl_Roads].[End])
AND
((IIf(([lngNumber]+1)*[Interval]<[tbl_Roads].[End],([lngNumber]+1)*[Interval],[tbl_Roads].[End]))>[tbl_Roads].[Start]))
ORDER BY tbl_Roads.RoadName,
IIf([tbl_Roads].[Start]<=[lngNumber]*[Interval],[lngNumber]*[Interval],[tbl_Roads].[Start])-([lngNumber]*[Interval]>[tbl_Roads].[Start]);
When you run this query, it asks you for an interval (this can be any
positive number > 0). It then uses the values of the [lngNumber] field in
qryNumbers as multipliers of the [Interval] value you entered to generate
multiple records based on the Start and End values in the table for each
record. BTW, these values do not need to begin or end on a number that is
divisible by the [Interval] value.
The only thing you need to be sure of is that the highest number in
[qryNumbers] times the [Interval] is greater than the highest number in your
[End] field.
----
HTH
Dale
Stuartm said:
I would like to create a query which will allow me to group by a fixed
interval for selected table i.e. a record from table of road data has a name
of "abc" with a start of 0m and end of 490m, i would like the following
abc Raod 0-100m
abc Raod 101m -200m
.
.
abc road 401 -490m
Can antyone help me?
