Does an Update query do this?

[Zoomiest]

I have a proprietary database of customers that I need to specify
which time zone they are all in.

BACKGROUND:
I have a Company table, with their address in it, and I have an
"extra_fields" where I have created a field "time zone."

The way the database works is, if I enter a time zone, it adds a row
in the "extra_fields" table, which contains the following fields:

extra_field_ID (an index for the table)
data_item_ID (linking field, links to the ID of the record its
associated with - in this case the company ID)
field_name (whatever the extra field's user-defined name is - this one
will say time zone)
value (whatever the value of the extra field is).

QUESTION:
I would like to query the company database, and for all the states and
provinces in a particular timezone, I would like it to create a record
in the extra_fields table, and have Access write in the correct time
zone, linking it to the company record...

Is this an append query? Update query? How do I do this?
Is this non-trivial?

 

[Michel Walsh]

It seems to be an insert query, like:



INSERT INTO extra_fields(data_item_ID, field_name, [value] )
SELECT data_item_id, "Newfoundland", -3,.5
FROM companies
WHERE companyState = "NF"




BUT, it could be better to make a zones table defining the time_zones just
once (not as many time there is a client in the said zone, as you planned to
do it), AND use a join between the company table and the time_zones table
(rather than storing it in a table like 'extra_fields' ).


Also not that some states/provinces can have more than one time zone. So,
checking the 'state' (province) is hardly a good test to find the right time
zone. Postal code could be much better.





Hoping it may help,
Vanderghast, Access MVP