populate table with repeat then increment then repeat (1 Viewer)

[Pavl]

Hello
Beginner with Access 2010 - making additions to one field in a JunctionTable.
Need to extend a field by adding some new numbers but have found it easier to Export to Excel - make changes and replace in accdb.
I have found an Excel formula that does what I want but starts from the number "1".
=INT((ROW()-1)/9)+1
Which starts with number "1" and repeats this 8 times then increments by one and repeats 8 times.
But I want to start with a number of choice (7270 and go to 9028).
Have seen some VBA versions but none give me control of the start number.
I am then able to update the second empty field on the JunctionTable from a query. (this is a fixed number).
Happy to do this with VBA but dont know the formula / syntax.
This only needs to be done once.
Thank you.

 

[ButtonMoon]

I would use a "numbers" table for this kind of thing (single column table with all the integers from 0 to some big N).

INSERT INTO your_table (col, ...)
SELECT num, ...
FROM numbers
WHERE num>=7270 AND num<=9028;

Join this with whatever your other query is to populate the other column(s). No need to insert duplicate rows; your table should not allow that anyway.

 

[Pat Hartman]

Can you tell us the business case you are trying to emulate.

 

[Pavl]

Thank you "ButtonMoon" and "Pat Hartman".
I will try the numbers idea - if I understand correctly.
My junction table has three fields - autonum for ID + PersonID + crewID
The current highest crewID number is 7269 and each crew has between 1 and ten personID numbers - these have been manually entered and functions correctly as a junction table.
I have found 1759 new "crews" each of which I will allow to have a maximum of 8 persons.
But the actual identity of each person is yet to be established so I will be using my [personID] for "unknown" to populate all the new records in field.personID - hence the simple "fill with a fixed number" part.
But first I need to add the new crewID numbers to allow my edit / update forms to function with the combo boxes that choose by flight date, aircraft, crew and so-forth.
And it was just getting that repeat/increment thing that was stopping me.
Thanks again.

 

[Pavl]

Having looked carefully at the "numbers" idea I am unable to find the repeat/increment components.
I understand the idea of grabbing an existing set of numbers then inserting into another table. Still have to get the sequence I need before that is possible.
But I am adding new records - and so therefore must be adding rows. The "duplicate" element is only within [field.crewID] - the principle ID field remains as autonum and therefore never duplicated.

 

[ButtonMoon]

Thank you "ButtonMoon" and "Pat Hartman".
I will try the numbers idea - if I understand correctly.
My junction table has three fields - autonum for ID + PersonID + crewID
The current highest crewID number is 7269 and each crew has between 1 and ten personID numbers - these have been manually entered and functions correctly as a junction table.
I have found 1759 new "crews" each of which I will allow to have a maximum of 8 persons.
But the actual identity of each person is yet to be established so I will be using my [personID] for "unknown" to populate all the new records in field.personID - hence the simple "fill with a fixed number" part.
But first I need to add the new crewID numbers to allow my edit / update forms to function with the combo boxes that choose by flight date, aircraft, crew and so-forth.
And it was just getting that repeat/increment thing that was stopping me.
Thanks again.

It's hard to do design in an online forum without the opportunity to analyse the real life scenario properly but your description does suggest some design issues.

I'm not clear what the benefit is of duplicating "dummy" rows for people whose data is not yet known. Why not just insert the rows when the personID is known? Don't make the mistake of creating database designs around a UI. Create the "right" database design and then create whatever you need in the UI to support it.

It appears that your table allows the same person to be allocated multiple times to the same crew. Perhaps that's not what you intended so it might make more sense to make (crewID, personID) the key. Maybe the autonumber is redundant but it's difficult to tell based only on what you've said here.

Hope this helps.

 

[Pat Hartman]

I agree with Button. There is no savings in creating this type of "dummy" record. Are you going to delete the unused ones if a crew has fewer than 8 members? What about if it has more? They still have to "touch" each dummy record to get it to point to the correct member.

 

[Cronk]

Pat, Button

I'll put a counter view.

While I agree with you in general, I once had a case where the user specification was that the sub form was to show all four maximum possible sub form entries and wanted the sub form to display the four even if all four were empty.

I settled on creating the four dummy entries when the Parent entity was created and set to sub form's AllowAdditions to no. I could have based the sub form on a temp table and copied the "real" records to/from the junction table to the temp table but with budget restrictions, I opted for empty records.

Pavl

You could create an unbound form with text boxes for the start/finish Crew numbers ((7270 and go to 9028) with a button which would execute the following query

Insert into tblCrewPerson ( CrewID, PersonID )
select tblCrew.CrewID,tblYourPersonID.PersonID
from tblCrew, tbiYourPersonID
where Crew.CrewID between StartCrewID and FinishCrewID

The table tblYourPersonID should have your PersonID repeated 8 times.

As there is no specified join between the two tables (Cartesion join), in tblCrewPerson, there will be 8 entries for each CrewID.

 

[Pavl]

Thanks for the suggestions and I do wish to learn more about Access but have limited time and perhaps I have been a bit too concise in my description of this project.
The purpose of the accdb is to display a collection of historical information. This is within a finite time frame and uses a finite set of original records. But for part of the time frame there is a lack of some information but remnants of other bits.

So - yes - it is likely that the same [person] will appear in a number of different [crews] and the chosen quantity of 8 is an educated guess - but once the skeleton is in place it will have to be as you say - altering each record as needed. But that is only a case of adding a record with two four digit numbers {person + crew}. I get your points about leaving well alone until I have the information - which would be the "correct" way - but that information is very unlikely to ever appear. (Originals probably destroyed).
In the meantime I need a way to display a "crew" which is populated with "unknown" rather than the present situation of a proven aircraft flight but without any sort of display for crew names.

Also did not tell you that I am the sole user / editor and always will be. The potential other "viewers" will be given copies as runtime versions (split FE/BE) with every possible restriction so that it functions solely as a method of information display with limited report printing / "saving as" PDF functions.

The thing now functions for search and display with correct information grouping - all of which has been manually entered via the Excel route. What I am doing now is the gap-filling to avoid another viewer seeing blanks.

Ultimately it is a glorified electronic history book.

 

[Pavl]

I have been given a VBA suggestion and have tried it with success - this is what I have done:
Created a module and used this code - courtesy of "RJD from Gulf South USA"

Public Sub CreateRecords()
Dim db As DAO.Database, rst As DAO.Recordset
Dim InsertNumber As Long, Sequence As Integer
Set db = CurrentDb
Set rst = db.OpenRecordset("JunctionTable")
Let InsertNumber = 7270
Let Sequence = 1
Do While InsertNumber < 9029
Do While Sequence < 9
rst.AddNew
rst!NumberField = InsertNumber
rst.Update
Sequence = Sequence + 1
Loop
Sequence = 1
InsertNumber = InsertNumber + 1
Loop
End Sub

Checked the table in datasheet view first - then ran module - visually checked outcome.

Then created query to select the new [crew_ID] rows only -
Changed to an update and put in my chosen 5 digit number for the unknown person - ran query and checked results on table.

Compact repair to reload my forms and checked the edit form now displays eight crew records for every flight.

All now working as required. Solved. Thank you for the help and advice.