TempTable with X number of Rows

[namu]

Hello Guys,

I would like to ask some help on how to dynamically create a tempTable with "x" number of rows.

Let's say x = 24. The tempTable should populate ID's from 1 to 24. That means 24 rows. Is it possible?

Thank you,

namu

 

[jdraw]

What you are asking is part of HOW --but WHAT is the business issue that is requiring X records in a tempTable?
It isn't considered a good practice to pre-create records in "anticipation" of something.

 

[namu]

Hi,

Thank you for your interest on my question. The x number of records will represent the "number of weeks (which is a result of calculation)" and the tempTable is for the graph that i need to create after. I would like to generate a tempTable because the number of weeks will depend on the duration or period of the project. That is a project to project case.

This the structure of the tempTable.

tempTable
-ID (number of weeks)
-PlanToComplete (manual input)
-EstimatedManPower (calculated field)

 

[jdraw]

Do you plan to have multiple Projects? Would each Project be a separate Database?
Not sure I understand the need for TempTable- can you tell us more?

What exactly will you use Access for?
Here is a generic data model for Project Management.

 

[namu]

Thank you for the Diagram. We are planning to use Access for Estimation purposes(Budget, Cost, Manpower, Schedule, etc. ). We already have an existing excel file for the estimation but we try to convert it into access database.

And yes, there can be a lot of different projects. The only purpose of the tempTable is to plot the correct number of weeks on the graph x axis. The number of weeks are not the same in all projects. Some are 6 weeks, 12 weeks, etc.

 

[jdraw]

I recommend you do some research on Database design, and Normalization. Access(database) is quite different than spreadsheet. You may have to "unlearn" some Excel concepts to maximize the benefit of moving your database from Excel to Access.
Good Database videos #1,2 and 4 from this series by Dr. Daniel Soper.


Good luck.

 

[namu]

I appreciate your recommendation though there is no real problem with normalization. I just need to know if it's possible to create a tempTable with x number of rows as pre-populated data. I really don't know where to start. I start googling but i don't get much information or maybe my search keywords are wrong. Do you know how to generate such table?

Thanks,

namu

 

[jdraw]

Create the Table.
Then I'd suggest a For Next loop using the table as the source of a recordset.

Dim rs as DAO.recordset
Dim J as Integer
set rs = currentdb("tempTable")
For J = 1 to 24 '( whatever number you need )
  rs.addnew
  rs!YourfieldName = "Record " & J
  rs.update
Next J

 

[namu]

Great! thanks a lot for the help. I was able to populate the ID by editing some of the code you provided.

Dim myDB As Database
Set myDB = CurrentDb
Dim rs As Recordset
Dim J As Integer

Set rs = myDB.OpenRecordset("tempTable")
For J = 1 To 24
    rs.AddNew
    rs!ID = J
    rs.Update
Next J

End Sub

 

[jdraw]

Good stuff. Happy to help.