Table updates (1 Viewer)

[Atthe]

Hi All,

I have a tblTools with over 2000 records and 10 fields, 2 of these fields are Machine and Tool Type. Can some explain the bestway to handle changes to the two fields so that it It will update all the records automatically?

I.e Of the 2000 records around 800 of the Machine field is 20 Ton Press , If for example this press was replaced by a 30 Ton press how can I update this without having to manually interact with the table?

Thanks for any help

 

[arnelgp]

use Update Query:

Update yourTableName Set FieldName = "30 Ton Press" Where FieldName = "20 Ton Press";

 

[Uncle Gizmo]

How to Create an Update Query with the Designer Grid:-

Designer Grid - Update Query - Nifty Access​

 

[mike60smart]

Hi All,

I have a tblTools with over 2000 records and 10 fields, 2 of these fields are Machine and Tool Type. Can some explain the bestway to handle changes to the two fields so that it It will update all the records automatically?

I.e Of the 2000 records around 800 of the Machine field is 20 Ton Press , If for example this press was replaced by a 30 Ton press how can I update this without having to manually interact with the table?

Thanks for any help

Hi
I would have thought the best way to manage this scenario would have been a table for Machines and a related table of Tools.

That way you would only have 1 record for 20 Ton Press and then a number of related Tool Types.

 

[The_Doc_Man]

It seems from your description that perhaps a bit of normalization would be in order. You appear to have a repetitive description field when you could instead have a descriptive table with a code for each description. Then when you need to change the description of something, you change the description in the table ONCE. You need to look up JOIN queries as a way to do the automated lookup of the code when displaying the results on forms or reports.

If you are not familiar with normalization, I highly recommend that you spend a little study time. In this forum, search for "Normalization." IF you search the web, use "Database Normalization" since there are other types of normalization that have nothing to do with databases.

 

[Pat Hartman]

Mike and Doc are on the right track. If you find you need to update common data in a table, the data is almost certainly in the wrong table. You probably need to add a parent table as doc suggested so you can have the common fields in the parent table. That allows you to update only a single record if you want to change a common field.