Generate a table with a list of numbers between the given range

[Hannah_0987]

How do I build program that can generate a table that lists out the numbers within the range I have entered in a form.

The user inserts two numbers in a form. For example, the field StartValue is 1 and EndValue is 20.
In another table, I would like numbers from StartValue to EndValue to be listed out. For example, 1,2,3,4...20. Each number should be listed out row by row, so in this case when I've entered 1 and 20, I'll get 20 rows of numbers.
For reference, the function is similar to the Series function in Excel as the picture below, but its implemented in Access.

Any help would be appreciated, thank you.

 

[plog]

First--why? We are always leery of people with low post counts asking unique questions without context. What is the end goal, what will doing this task help with?

With that said, you could write VBA to run a bunch of INSERT statements. You could build a base table with a ton of numbers and then use it as the basis of a MAKE TABLE query and use the input values as criteria.

Again though, there might be a better way to achieve the ultimate aim than this intermediary step.

 

[MajP]

As @plog said this would be pretty simple to do with a loop calling an insert query for the rows, but may be problematic with columns.

You could do 255 Fields Maximum. So you could make a dummy table with 255 fields, but that is your limit. However the problem would be that you would need to likely make a numeric table and date table. You could build these tables dynamically but that would be more code intensive.
But again what is it you really want to do? There may be a better idea.

 

[Pat Hartman]

I agree with plog, requests like this are always suspect because generating "empty" records is not recommended. One case where I have done this was with a receiving system. The user wanted to enter a description and then a quantity and have x records generated. So "white sheets", "10 cases". The individual rows were necessary so that the pickers could be instructed to issue box 1 before box 2 to keep inventory from getting stale when they were shipping it out.

The simplest way to do something like this it to create what we call a tally table.

Tally table sample

There is a method of generating x records using what is frequently called a "tally table". This table has rows with values from 1 to x with x being the maximum number of items you will need to generate. This particular example is from a receiving/shipping application I built for a client. The...

www.access-programmers.co.uk

 

[arnelgp]

here is a demo, open demo_series form and click Generate Series button.
open the form in design view and see the VBA (Click event) behind the Generate series button.

 

[Uncle Gizmo]

I answered a similar question a while ago.

I posted it on my website here:-

Create Many Records - Nifty Access

Recordset Loop - Create Records Becoming an MS Access Power User 💪Once you start converting queries into SQL statements – in other words, a string version of the query – you can take that string and manipulate it, extract things from it, and do all sorts of things with it! One of the best stepping

www.niftyaccess.com

A sample database demonstrating the process is available from the Website for free just use the coupon code:- "BuyMeACoffee"