Create New fields

[n7kon]

I have three tables called Noun, Modifier, and Attributes. I have created a new table using the first two fields as Noun and Modifier and planted a default value in them. Now I need to create more fields in the new table. The headers for those fields would be the matching attributes from the attribute table.
Example
Noun = Motor, Noun ID = 1
Modifier = Electric , Modifier ID = 2
Now I want to collect the Attributes that match the nounid and modifierid from the attributes table and use those to create field headers in my motors table.
In the Attributes Table it may have several items that match the nounid and modifierid combo
Example would be Volts, Amps, RPM, and Frame
I have made a record set of those but cannot figure out how to add the fields to the existing table. I have to keep it generic because module will be used to create another table with a new noun modifier combo, that have different attributes. Any help would be appreciated.

Thanks Keith

 

[n7kon]

Ok here is where I am at now. This does not work because I am using the variable for the table name in the alter table statement. I need this to be generic so I can use different combinations of noun/modifier.

Public Sub Add_fields(NounID, ModID, strNoun)
Dim strAtt As String
Dim strAttID As String

Set DB = CurrentDb()
Set rsAtt = DB.OpenRecordset("attributes")
rsAtt.MoveLast
With rsAtt
.MoveFirst
Do Until .EOF

If !NounID = NounID And !ModID = ModID Then
strAtt = !Label

DB.Execute "ALTER TABLE strNoun ADD COLUMN stratt text(30)"

End If

.MoveNext
Loop
End With

 

[VilaRestal]

I think no-one's posting replies because it seems like a bad idea:

you want a column in a table for each row in another table?

Perhaps you have a good reason but it suggests a bad design. Altering tables at runtime is normally not a good idea. Apart from trying to save time building tables in the table designer - and designing the code is probably more work anyway.

I (and I'm sure everyone) would advise you to think carefully about whether these new columns would be better done as rows in a table, which could then be pivoted if need be.

 

[n7kon]

I would like to be able to build them all one at a time but I am working with a master file of over 2 million records and need to break each noun modifier combo out. The file holds about 139 thousand items with many records for each item. I have 1400 nouns and 3800 modifiers to go with those nouns and 20k attributes that go with all the noun modifier combos.
I need to automate the break down.


Thanks Keith

 

[n7kon]

I found my answer from another person that uses access same way I do. We analyze data from much larger data files to answer questions for our customers. We never build front ends or reports. This job is to take all the data from the larger AS400 data file and bring it down into separate tables for their folks or there suppliers to work on in Excel.

Thanks anyway
Keith

 

[jdraw]

I am glad that you have found an answer.

I have read and reread your post and have no idea what exactly you are trying to accomplish. Sometimes, just a clear statement in English of what you are trying to accomplish, will help communicate your problem/opportunity to the readers.

Getting the proper table design and structures can solve many issues as VilaRestal suggested.

Good luck with your project.

 

[n7kon]

Thanks JDraw for the comment. I am sure I should have asked a better question as I read my first post. What I am really doing is pulling all of one type of item and all its records from one file. A motor could be electric, pneumatic, hydraulic etc… and each one of those combinations will have a set of attributes like RPM, HP, Volts, and Torque etc…. In the master file they are all in one file with a column that codes them as noun, modifier, or attribute. I need to pull them out of that file and put them in a table so they can go out to folks to improve the information. The motor file will go to the supplier that sells motors and the Valve table will go to the supplier that sell them the valves and so on until all combinations are separated
So my table would look like

Noun Modifier RPM VOLTS FRAME PHASE
MOTOR ELECTRIC 1800 460 182T 3

The headers are what I need to use to make the table on the fly.

Thanks Keith