query grouping by fixed interval

[Stuartm]

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?

 

[MGFoster]

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?

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

You can use the Partition() function, but it has to work on some column
value. The following contains info from the Access VBA Help:

Syntax: Partition(column_name, start, stop, interval)

"number Required. Whole number that you want to evaluate against
the ranges.
"start Required. Whole number that is the start of the overall

range of numbers. The number can't be less than 0.
"stop Required. Whole number that is the end of the overall range

of numbers. The number can't be equal to or less than
start."
interval Multiple of the interval changes the output

The interval in your case should be 100 and you should start on 1, not
zero. Examples:

Partition(0, 1, 500, 100) yields : 0
Partition(50,1,500,10) yields 1:100
Partition(100,1,500,10) yields 1:100
Partition(130,1,500,10) yields 101:200
Partition(200,1,500,100) yields 101:200

HTH,
--
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/AwUBSffQc4echKqOuFEgEQIcOgCeIwUB0kf54fdqdn/lAdiG58V1l6QAoN+1
a02jrwrEh9+TmzKasGW2KjLI
=THXG
-----END PGP SIGNATURE-----

 

[Dale Fye]

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.

 

[Stuartm]

Dale,

Thanks for your help on this, works a perfectly, much appreciated.

Regards

Stu

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

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?