Add Field in a table with a form... Possible? (1 Viewer)

jajambo · Apr 28, 2016

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 · Apr 28, 2016

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 · Apr 28, 2016

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 · Apr 28, 2016

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 · Apr 28, 2016

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 · Apr 28, 2016

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 · Apr 28, 2016

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 · Apr 28, 2016

Minty said:
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 · Apr 28, 2016

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 · Apr 28, 2016

Thanks to all!

Add Field in a table with a form... Possible? (1 Viewer)

jajambo

New member

Minty

AWF VIP

arnelgp

..forever waiting... waiting for jellybean!

Isskint

Slowly Developing

jajambo

New member

arnelgp

..forever waiting... waiting for jellybean!

Minty

AWF VIP

jajambo

New member

Minty

AWF VIP

jajambo

New member

Similar threads

Users who are viewing this thread