Somewhat dumb form question

[jrock1203]

Apologies at what is likely extremely simple - and I just can't seem to a. remember and b. figure out by luck.



I've got a handful of tables (previous posts refers to this, I've rectified the issues there) and a handful of forms.

Each form updates its respective table.

There is, however, one table that is essentially a repository of batch numbers. I'd like my forms to update both the repository table and their respective table with the batch number(s) only.

How can I get my text box entry field to update more than one table? Open to other solutions as well - is my question clear?


Thanks!

J

[orange]

I recommend you show us your latest tables/relationships and an overview of the issue in context.
Looking through previous posts to find info is not something many will do. Better to have the info and the context in the thread.

[jrock1203]

I recommend you show us your latest tables/relationships and an overview of the issue in context.
Looking through previous posts to find info is not something many will do. Better to have the info and the context in the thread.

See pic - All tables linked to the Batch Repository table by a PK of Batch Number. If a better layout makes sense, I'm all ears. Ignore the blended batch and legacy batch in the repository table - I've removed them.

Initial idea that just hit me is to add a subform for the other table and let the field update the other






Thanks!

J

[Micron]

This

Each form updates its respective table.

and the fact that Batch Number is a PK in every field suggests you have way more overhead than you should. You probably should have one table with all the fields, plus one more field that contains the table name of each current table as another attribute of your entity (batch?). Nor would I base a form on a table in most cases. I also have to wonder; if you need to add an attribute 3 years from now, what are you going to do? Redesign every table and form to accommodate it? That's a sure sign of improper design.

[jrock1203]

This and the fact that Batch Number is a PK in every field suggests you have way more overhead than you should. You probably should have one table with all the fields, plus one more field that contains the table name of each current table as another attribute of your entity (batch?). Nor would I base a form on a table in most cases. I also have to wonder; if you need to add an attribute 3 years from now, what are you going to do? Redesign every table and form to accommodate it? That's a sure sign of improper design.

Very interesting point. So if I take every field that exists in these tables and combined into one table, I would then use forms to update the pertinent data for each section?

In other words, all of the fields in each table currently would go into a single, new table. Then, I would have entry forms that populate only the fields in the current tables? Even though the form may say 'Alcolyzer' it would only update the fields I include on the form, yes? And thus, each Batch Number would then have all of the data across all forms attributed to it and the entire mess I've created resolves?


Thanks

J

[orange]

In addition to the points micron has raised, Access does not like embedded spaces or special characters in field names.
Use alphabetics and "_" underscore only to avoid frustrating syntax errors.

Here is a link to info on Database Planning and Design. I strongly recommend the tutorials from RogersAccessLibrary.
Work through 1 or 2 to get some experience with design.
Good luck.

[Micron]

I would always suggest the use of a form, based on a query first. If you cannot update table(s) based on the query, a form based on it would be useless. To me, forms come after queries where more than one table is involved - unless through experience it's a no-brainer. As for the remainder of your comments, I'm not familiar with your other posts and cannot see all of the fields anyway, so it's a hunch. All the fields can go into the same table regardless, but that may not be the best design either. It would only be a better approach if ALL the fields in your tables were the same in all aspects (not just name, but data type and all other possible restrictions) AND were applicable to one entity. I don't know what that entity is, but venture to say the name you'd be looking for would describe (name the entity) in a way that applies to Alcolyzer and Diacetyl. However, looking again at the other table names, I'm thinking there is no such commonality among all tables, as I don't foresee any commonality between Packaging and Alcolyzer even though the table fields seem repetitive.

I'm thinking you need a primer on normalization (and maybe other things as well...might as well give you all my customary links for newbies)

Normalization is paramount. Diagramming maybe not so much for some people.

Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.ca/...on-part-i.html
and/or
http://holowczak.com/database-normalization/

Entity-Relationship Diagramming: Part I, II, III and IV
http://rogersaccessblog.blogspot.ca/...ng-part-i.html

How do I Create an Application in Microsoft Access?
http://rogersaccessblog.blogspot.ca/...cation-in.html

Important for success:
One source about how to name things - http://access.mvps.org/access/general/gen0012.htm
What not to use in names - http://allenbrowne.com/AppIssueBadWord.html
About Auto Numbers
- http://www.utteraccess.com/wiki/Autonumbers
- http://access.mvps.org/access/general/gen0025.htm

The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
About calculated table fields - http://allenbrowne.com/casu-14.html

[jrock1203]

I would always suggest the use of a form, based on a query first. If you cannot update table(s) based on the query, a form based on it would be useless. To me, forms come after queries where more than one table is involved - unless through experience it's a no-brainer. As for the remainder of your comments, I'm not familiar with your other posts and cannot see all of the fields anyway, so it's a hunch. All the fields can go into the same table regardless, but that may not be the best design either. It would only be a better approach if ALL the fields in your tables were the same in all aspects (not just name, but data type and all other possible restrictions) AND were applicable to one entity. I don't know what that entity is, but venture to say the name you'd be looking for would describe (name the entity) in a way that applies to Alcolyzer and Diacetyl. However, looking again at the other table names, I'm thinking there is no such commonality among all tables, as I don't foresee any commonality between Packaging and Alcolyzer even though the table fields seem repetitive.

I'm thinking you need a primer on normalization (and maybe other things as well...might as well give you all my customary links for newbies)

Normalization is paramount. Diagramming maybe not so much for some people.

Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.ca/...on-part-i.html
and/or
http://holowczak.com/database-normalization/

Entity-Relationship Diagramming: Part I, II, III and IV
http://rogersaccessblog.blogspot.ca/...ng-part-i.html

How do I Create an Application in Microsoft Access?
http://rogersaccessblog.blogspot.ca/...cation-in.html

Important for success:
One source about how to name things - http://access.mvps.org/access/general/gen0012.htm
What not to use in names - http://allenbrowne.com/AppIssueBadWord.html
About Auto Numbers
- http://www.utteraccess.com/wiki/Autonumbers
- http://access.mvps.org/access/general/gen0025.htm

The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
About calculated table fields - http://allenbrowne.com/casu-14.html

Sincerely appreciate it everyone - I've got some reading to do. My slightly above newbie level access skill needs some expansion. I've copied the DB to start making suggested changes while I'm reading.

Will follow up once it is looking better!

Cheers


jeremy

[Micron]

BTW, could you just reply instead of Reply With Quote - unless you need to. Simply repeating a long answer has no benefit and prefaces your reply with a lot of unnecessary text. Thanks.