Query to update column in original table

  • Thread starter Thread starter Mark909
  • Start date Start date
M

Mark909

Is there anyway to write a query to update details in the original table to
fill out a blank column.

For example:

If the value in "Properties" column = 4 then cells in the "Name" column will
= Camera.

If the value in "Properties" column = 6 then cells in the "Name" column will
= Batteries
 
A better solution would be to create a table with a record for each kind of
thing, and a number so you can match it to your table.

So, you create a table with records like this:
4 Camera
6 Batteries

Now create a query joining your original table to this one, and you can
output the name without having to alter your original table.
 
The best way to do this is to add a table to your database with two (or
perhaps more) columns.

Property ( a number field - primary key)
PropertyName (a text field)

Then you add this table to any query where you need the property name and join
the "original" table to the new Properties table on the two Property fields.

If you really, really, really feel that you have to update the "original"
table then you can use the Properties table to do so using an update query
that would look like the following.

UPDATE OriginalTable INNER JOIN Properties
ON OriginalTable.Properties = Properties.Property
SET OriginalTable.Name = Properties.PropertyName
WHERE OriginalTable.Name is null or OriginalTable.Name = ""

If you can only use the query design view and cannot figure out what to do
from the above, post back and ask for step-by-step instructions.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top