New Table from Old 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.

Any help would be appreciated.

 

[JeremyNYC]

Hmm. First I'd say to reconsider your design. It sounds like a pretty good candidate for a lookup table, instead of one table for each person. Have a table with all the names, and whatever attributes of each person you want to record. Then have another table that just gets the personID (Name is a bad choice for a primary key, as it's too likely to get munged) and whatever piece of data would go in that Smith1 field in the table you were talking about creating. That way you can add as many or as few records for each person as you need, and querying will be MUCH easier, and you won't have a zillion tables floating around in your database window (I have a database I don't consider large and it's got 20,000 people recorded in it!), and you'll generally be a lot happier.

You can, of course, do what you're asking about. To do that you'd create a recordset of the name and number fields from your original table and walk through the records, executing Sql strings to create the tables and fields. But I recommend against it, strongly.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.