CODEWITHSUNDEEP
ms-access
user681413
user681413

Reputation: 49

Add a number of records based on text box number

I have a form to raise records on property work, one option is to select if it is a single property record or multi which is handled a little different.

If multi is selected a textbox appears on the form to input the number of plots.

what I need is a way to add a number of records to a table equal to the number of plots indicated and a sequential number input for them along with the Autonumber ID created when the main record is added.

Forms!Raise

record added on a button push from an insert query. If its category = "multi" record a number of plots has been input.

need to create in a separate table to main

<Main Record ID>  <Plot number>
       1        ¦        1
       1        ¦        2
       1        ¦        3
       1        ¦        4
       2        ¦        1
       2        ¦        2
       2        ¦        3

Im assuming the best way is another insert query that pulls the main record ID and adds a number of records based on the plot numbers, The total plot numbers is also stored on the main record so instead of referencing the form perhaps reference the recorded plot number total.

So Id need to call the correct record (2 ways I can think to handle that from where matching form or looking for main records with cat = multi ID not found in Plot table ID)

Then create number records as shown, with plot numbers input up to total records created. I just have no idea how I can make multiple records with a sequential number based on a total number.

Ive done some searching with little luck and no idea where to start with this can anyone help?

Upvotes: 0

Views: 409

Answers (2)

user681413
user681413

Reputation: 49

Have a possible answer, using a table which just has a list of numbers going beyond the max I could get from an input plot number use the following query 

    SELECT Newbuilds.ID, Constant_numbers.number
from
Newbuilds
INNER JOIN
Constant_numbers
ON Constant_numbers.number <= Newbuilds.plots
;

This produces a list of the id with a sequential number that I can then insert into the plots table, it doesn't feel like the best way of getting the result I want but it works.

with the help of oliver the complete solution wanted is as below, for others that stumble on this 

    Insert Into Plot_status ([ID], [Plot])
Select Newbuilds.ID, Constant_numbers.number
From Newbuilds, Constant_numbers
Where Constant_numbers.number <= Newbuilds.Plots
And Newbuilds.category = "Multi"
And Not Exists (SELECT Plot_Status.ID
FROM Plot_Status
where Plot_status.ID = Newbuilds.ID);

Upvotes: 0

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112299

Let's assume that you know the maximum total number of records that you ever want to insert. Create a table only containing the numbers 1, 2, 3 ... at least up to this maximum number.

Table Numbers
Number
------
   1
   2
   3
   and so on until your all times max number

Now let's insert 5 records with main ID = 3

INSERT INTO plots ([Main Record ID], [Plot number])
SELECT 3, Number
FROM Numbers
WHERE Number <= 5

This inserts the following records

<Main Record ID>  <Plot number>
       3        ¦        1
       3        ¦        2
       3        ¦        3
       3        ¦        4
       3        ¦        5

Upvotes: 1

Related Questions