Updating Two Tables

[Tee See]

Currently have a table which has a field "category". Each category might
apply to many records. The category is abbreviated as in A/90 or A/45. I
would like to utilize an expanded explanation of the abbreviation to be
used as headings in a product catalogue.
e.g. A/90 = Aluminum 90 Degree Ells
Should I just add a new field to the existing table or make a new table of
only 'unique" versions of category plus the expanded text? The existing
table has 2900 records and so each record would require input to that field
while there are only 100 unique categories which would need input. If the
answer is a new table, how would I update both tables simultaneousy.

Hope this is clear. Regards

 

[Pat Hartman\(MVP\)]

Create the second table with the category code and the category name fields.
Set the category code to be the primary key. A quick way to make the code
part of the category table is with a make table query. Create a query that
selects the category code from you table. Press the sigma button on the
toolbar to turn this into a totals query. Run it. you should see a list of
unique categories. If any empty values show up at the top of the list, put
the query back into design view and add criteria:
Is Not Null
Run it again. If you still have an empty row, change the criteria to:
Is Not Null and <> ""

Once the empty values are removed, change the query type to make table. You
will be asked to supply a name for the new table. Now when you run the
query, it will make your new category table. Open the new table in design
view and add another column to hold the name. Save the table, open it and
enter the data.

Now whenever you want to see the description rather than the code, use a
query that joins the two tables (use a left join or you may be missing some
data from the main table). Select whatever you want from the main table and
select the name column from the category table.

On your forms, use a combo so you can select by the name field but store the
code field. The wizard will build the combo for you. Use the category
table as the combo's RowSource.