New Table from other table

[ferrisbb]

I am kind of new to Access, an old dbase programmer. I need to create a table(b) using the data from another table(a). The first table(a) has 2 fields #1-name, and #2-number. If the data in field #1 is "Smith" and the data in field #2 is "5", I need table(b) to have fields #1 - Smith1, #2 - Smith2, #3 - Smith3, #4 - Smith4 and #5 - Smith5.
So basically the first table(a) has the 1 - field name and 2 - how many fields with that name need to be created. I need to use the name+number in the field of the second file i.e. Smith3. Of course table(a) has many records each with different names and numbers of fields.

The idea here is that I can go into a form, edit the number associated with the field name and generate a new table with the required number of fields for each name. I will then have another form to input the data into the new table. I am sure this second form will be my next posted question.

I can do this in dbase but have been struggling badly in Access. Any help would be appreciated.

 

[ObjectRequired]

Give this a shot ( quick and dirty, not tested). You'll need to define FieldName as somthing meaningful.

Public Function fMakeTable (SourceTable as String, DestTable as String)

Dim dbCurrent As Database
Dim rstSource As Recordset
Dim I as Integer
Dim FieldName


‘Create Destination Table
DoCmd.RunSQL “SELECT ‘’ AS " & FieldName & " INTO “ & DestTable & “;”

‘Open SourceTable as Recordset
Set rstSource = dbCurrent.OpenRecordset (SourceTable, dbOpenTable)

‘Initialize For-Next loop to the number in the second field of rstSource
For I = 1 to rstSource.Fields (1)

‘Quick and dirty SQL append- you may want to open it as another recordset to increase performance
‘Note that rstSource.Fields(0) is the name, and I is the loop variable
Docmd.RunSQL “INSERT INTO “ & DestTable &” ( FieldName ) SELECT “ & rstSource.Fields(0) & I

Next I

‘Clean Up
rstSource = Nothing
dbCurrent = Nothing

End Function

 

[Darrylles]

Hi,

I tried answering this (after successfully testing it), but the connection was lost and I got fed up.

So attempt 2, but just a summary....

Create a simple query (called QueryA) selecting the pk, and the 2 fields that need concatenating.

In a column (where the field name goes) type: Expr: [fieldname1]&" "&[fieldname2]. This will hold the concatenated fields 1 and 2.

Run the query.

This should show 3 columns displaying the concatenated columns in column 3 as required.

Create another query, add the table with the field you wish to be updated (show all fields). Add the above query also.
Drag the pk field from one to the other to relate them. (Important).

Convert this into an Update query (Menu->View->Update).

In the query column that needs to be updated (your concatenated field), you now have an 'Update To' field. In there type: [Querya].[expr]

Save this query as MyUpdateQuery.

Run it.

Regards,

Darrylle (Phew).

"Never argue with an idiot, he'll bring you down to his level - then beat you with experience." AND "A pointer to a solution is better than the solution - you'll remember the solution via a pointer". darrylles@totalise.co.uk