Update Query to populate newly added field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Added a new combo box field to form with an AfterUpdate action to fill in
three other fields based on the new field's entry. With this in place, I now
need to update this new field for 6000+ existing records based on each
record's three auto populated fields...I have spent hours searching this
forum with regard to update queries to find my answer (as I always find my
answers here), but I'm not finding it this time around..
 
Added a new combo box field to form with an AfterUpdate action to fill in
three other fields based on the new field's entry. With this in place, I now
need to update this new field for 6000+ existing records based on each
record's three auto populated fields...I have spent hours searching this
forum with regard to update queries to find my answer (as I always find my
answers here), but I'm not finding it this time around..
Click to expand...

Hm. Sounds like this field may not be properly normalized! If you can
populate three other fields given the value of this combo box, or vice
versa, then you have mutually dependent fields within the record,
violating third normal form. I'm guessing that your main table should
have only the unique ID field, and that the three autopopulated fields
should NOT be stored in your table at all.

Since I have no idea how the tables involved are related, or how you
"auto populate", I can't be certain, but try this. Assuming you have a
four field table that you're using for the RowSource for the combo,
you should be able to create a Query joining your big table to this
table, joining *on the three autopopulated fields*, joining FieldA to
FieldA, FieldB to FieldB, FieldC to FieldC. You may need to create a
unique Index on the combination of the three fields for this to be
updateable, but you should then be able to update MainTable.ID to
NewTable.ID.

If this isn't making sense, please post the relevant fieldnames and a
description of you your tables are related.

John W. Vinson[MVP]
 
Hi, vabulous.

If the field that you're selecting in the combo box determines the values of
three other fields, you don't need those fields in the first place--they are
redundant. Store them in another table, once, with their primary key the
value of your combo box field, and use a query to get those values when you
need them.

MyTable
---------------
MyComboBoxField Primary Key
Field1
Field2
Field3

Much like the following:

Orders
-----------
OrderNumber
OrderDate
CustomerNumber
....

Customers
-------------
CustomerNumber
CustomerName
Address
....

You don't store the customer name and address in the Orders table, only the
CustomerNumber, the foreign key.

If you had such a table created, you could then join it to your existing
table on each of the three fields, and Updating the value of your new field
in the existing table to the value in the new table.

Hope that helps.
Sprinks
 
TableA is Inquiries. TableB houses the various inquiry categories, currently
130 each category always containing a Topic and Category and sometimes a
subcategory, that may be assigned to an individual inquiry record. Inquiry
Form previously housed a combo box for each catagory level field, allowing
selection from each combo box to fill each category level field. I changed
the autonumber primary key for TableB into an updateable field 'Category#'
(in order to add categories and keep them together) with an AfterUpdate
action in the form to fill in the three catagory levels based on the selected
Category# from the field's combobox (combobox to allow the user to see all
levels at a glance in order to select the correct Category#)

Again, it's working fine for new records, but I am unable to update the
existing 6000+ records...
 
Back
Top