Add Field in a table with a form... Possible?

[jajambo]

Dear all,

Is it possible to add a field in a table by using a form?
I am working on a tracking database, where I track Machines as records and Modifications as fields. Both records and fields will grow over time and I want to create forms to update them.
Adding a record is obviously possible. But adding a field? I have no luck finding any answers yet.
Thanks for your time.

 

[Minty]

You shouldn't need to add a field if you are storing your data correctly. You should have a table of Modifications and add a record to it.

 

[arnelgp]

definitely doing the wrong thing. wrong design.
you are adding column for modification and/or machine?
table should grow by record not by columns.

 

[Isskint]

Ditto the 2 above and also consider queries, forms & reports. You would have to keep changing the designs to account for the new fields.

if mods can be applied to multiple machines
Table1: Machines
Table 2: Modifications
Table3: MachineMods

OR if mods are purely machine spedific

Table1: Machines
Table 2: Modifications

 

[jajambo]

Thanks for the replies so far. To think about it, adding fields would NOT be a way to go.

To answer your question @arnelgp: both Machines and Modifications will grow over time.

@Isskint: Mods can be applied to multiple machines. What would Table3: MachineMods look like? I can picture Table1 and Table2, and they will have a "many-to-many" relationship.

 

[arnelgp]

suggests basic structure of tables:

tblMachine
ID (Autonumber) (pk)
MachineDesc (Text)
' other fields here

tblModification:
ID (autonumber) (pk)
Description

tblMachMod (junction table)
ID (autonumber) (pk)
MachineID (long)(id from machine table) (fk)
ModID (long)(id from modification table) (fk)
ModDate (Date)

the records of table machine will grow row-wise.
the records of table modification will grow row-wise.
the records of junction table will grow row-wise.

 

[Minty]

Table 3 is called a junction table and would simply have a UniqueID, then the MachinesID and the ModificationsID in it.
This allows you to have as many mods per machine as you require.
This is waht allows you to create your Many to Many relationship, which is not good/practical design.

 

[jajambo]

Table 3 is called a junction table and would simply have a UniqueID, then the MachinesID and the ModificationsID in it.
This allows you to have as many mods per machine as you require.
This is waht allows you to create your Many to Many relationship, which is not good/practical design.

@Minty: What would be a better design?

 

[Minty]

Sorry - the junction table IS the good design - trying to have two tables directly joined by "Many to Many" was what I meant was poor / impossible design.

Try it on paper and you'll soon see why it can't work, you'll end up needing to add a field for every modification you could have again...

 

[jajambo]

Thanks to all!