Number Generator

[Richard G]

Have a list of numbers in a table. Has a lot of gaps.
Need to fill the gaps, but only between certain numbers.
For example I have the following numbers in my table:
1,2,5,9,16,35,75(actually i have a lot more but this is an
example). What I need to do is fill in the gaps if the
difference between the first number and the next number is
less than 10 different numbers. So since 2 and 5 have
less than 10 different numbers between them, then it would
fill in 3,4 and 5 and 9 would fill in 6,7,8 and 9 and 16
would fill in 10,11,12,13,14,15 but 16 and 35 would fill
in nothing because it is more than 10 numbers apart?? HELP!
....

 

[Richard G]

-----Original Message-----

Have a list of numbers in a table. Has a lot of gaps.
Need to fill the gaps, but only between certain numbers.
For example I have the following numbers in my table:
1,2,5,9,16,35,75(actually i have a lot more but this is an
example). What I need to do is fill in the gaps if the
difference between the first number and the next number is
less than 10 different numbers. So since 2 and 5 have
less than 10 different numbers between them, then it would
fill in 3,4 and 5 and 9 would fill in 6,7,8 and 9 and 16
would fill in 10,11,12,13,14,15 but 16 and 35 would fill
in nothing because it is more than 10 numbers apart?? HELP!
...

Eeeuw. This one is tough.

Try this: a Self Join query. Let's say your current table is named
MyTable. Create a new table, Num, with a field N with values 1 through
9.

Then use:

SELECT MyTable.Field1 + N
FROM MyTable, MyTable AS MyTable_1, Num
WHERE MyTable_1.Field1 =
DMin("Field1", "MyTable", "[Field1] > " & MyTable.Field1)
AND MyTable_1.Field1 - MyTable.Field1 < 10
AND N < MyTable_1.Field1 - MyTable.Field1;

Change this to an Append query once you've verified that it returns
the right values (tweaking if need be).


.

That helps however I need to generate the numbers out of
thin air because I have 10's of thousands of numbers to do
this with and having to make a table with that many
numbers in it would defeat the purpose of doing it. Any
Ideas??

 

[John Spencer (MVP)]

Have you tried John's solution, as he stated it?

His solution would require you to have only 9 fields in the Num table. I will
admit that **_I_** have not tested his solution, but it looks as if it would
work.

-----Original Message-----

Have a list of numbers in a table. Has a lot of gaps.
Need to fill the gaps, but only between certain numbers.
For example I have the following numbers in my table:
1,2,5,9,16,35,75(actually i have a lot more but this is an
example). What I need to do is fill in the gaps if the
difference between the first number and the next number is
less than 10 different numbers. So since 2 and 5 have
less than 10 different numbers between them, then it would
fill in 3,4 and 5 and 9 would fill in 6,7,8 and 9 and 16
would fill in 10,11,12,13,14,15 but 16 and 35 would fill
in nothing because it is more than 10 numbers apart?? HELP!
...

Eeeuw. This one is tough.

Try this: a Self Join query. Let's say your current table is named
MyTable. Create a new table, Num, with a field N with values 1 through
9.

Then use:

SELECT MyTable.Field1 + N
FROM MyTable, MyTable AS MyTable_1, Num
WHERE MyTable_1.Field1 =
DMin("Field1", "MyTable", "[Field1] > " & MyTable.Field1)
AND MyTable_1.Field1 - MyTable.Field1 < 10
AND N < MyTable_1.Field1 - MyTable.Field1;

Change this to an Append query once you've verified that it returns
the right values (tweaking if need be).


.

That helps however I need to generate the numbers out of
thin air because I have 10's of thousands of numbers to do
this with and having to make a table with that many
numbers in it would defeat the purpose of doing it. Any
Ideas??

 

[John Vinson]

That helps however I need to generate the numbers out of
thin air because I have 10's of thousands of numbers to do
this with and having to make a table with that many
numbers in it would defeat the purpose of doing it. Any
Ideas??

Ummm...

You didn't try it.

Your question as posed will never need more than nine rows in the Num
table, since you only want to insert values in gaps smaller than 10.

 

[Richard G]

Misunderstood about the nine row tabel, sorry. Tried it
out and it ran for 2 hours without result-Still running.
Original table had almost 500,000 numbers in it. Broke
off a portion of the table with just under 3,000 numbers
in it to analysis (a complete list of those numbers would
be 10,000 but I have criteria) again ran for two hours
without results - Still running. Is there a way to make
it go faster. I noticed that there is no "join" in the
expression. Did I miss something?