Splt a length record into multiple lengths

  • Thread starter Thread starter Les
  • Start date Start date
L

Les

Hello

I have a table which contains start and end points e.g.

Start End
1.00 miles 3.00 miles

Is is possible for me to create a query to split this mileage into multiple
records in a seperate table e.g

Start End
1.00 miles 1.50 miles
1.50 miles 2.00 miles
2.00 miles 2.50 miles
2.50 miles 3.00 miles

Cheers

Les.
 
Les

Not quite enough info ...

How many segments? How long for each segment? What about "rounding"?

More info, please...

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
Sure, this seems possible. I would expect you have some specifications you
aren't telling us. In order to get 4 records from a single in SQL, you
generally need a table with 4 records, some stored value that identifies the
interval, etc.

Is .5 always the increment?
 
Les,

The way I handle this is with a utility table (tbl_Numbers). This table
contains a single field (intNumber) and 10 records (with values 0 through 9).

I then create a query (qry_Numbers) from this table that generates values
from 0 to 99 or 0 to 999, or ... The SQL looks like:

SELECT Tens.intNumber * 10 + Ones.intNumber as intNumber
FROM tbl_Numbers as Tens, tbl_Numbers as Ones

You could then use this table, along with your other table to generate a
sequence of numbers in any increment. Something like:

SELECT T.Start + (Q.intNumber * .5) as Start,
T.Start + (Q.intNumber + 1) * .5 as End
FROM yourTable as T, qry_Numbers as Q
WHERE T.Start + (Q.intNumber + 1) * .5 <= T.End
ORDER BY T.Start + (Q.intNumber * .5)

The nice thing about this method is that you can use any interval by
replacing the values of .5 with some other value (.1)
 
Create a table named CountNumber with field CountNUM containing numbers from
0 (zero) through maximum spread.
Use this query --
SELECT ((([End]-[Start])/[Enter number of parts])*[CountNUM])+[Start] AS
Bottom, ((([End]-[Start])/[Enter number of
parts])*[CountNUM])+[Start]+((([End]-[Start])/[Enter number of parts])*1) AS
[Top]
FROM CountNumber
WHERE (((((([End]-[Start])/[Enter number of
parts])*[CountNUM])+[Start]+((([End]-[Start])/[Enter number of
parts])*1))<=[End]) AND ((CountNumber.CountNUM)<=[Enter number of parts]))
ORDER BY ((([End]-[Start])/[Enter number of parts])*[CountNUM])+[Start];
 
Back
Top