Updateable, Unique List

  • Thread starter Thread starter Marie
  • Start date Start date
M

Marie

Access97

I have a table containing addresses with a separate field for State. Is there a
way to create a query that returns an unique list of the states in that table
and still be updateable? I tried setting the unique values property to Yes but
that gave me a recordset that was not updateable.

Thanks!

Marie
 
What are you trying to _accomplish_? Perhaps there is another way to do it.

The trouble is that in such a query, Access may not be able to determine a
record to update. That is, if there are multiple records with the state of
Alabama, which one should be updated?

Larry Linson
Microsoft Access MVP
 
Marie said:
Access97

I have a table containing addresses with a separate field for State. Is there a
way to create a query that returns an unique list of the states in that table
and still be updateable? I tried setting the unique values property to Yes but
that gave me a recordset that was not updateable.

Thanks!

Marie
Click to expand...

No.
What are you trying to do? Perhaps you have 15 records where State="CA" and
you wish to see a single entry in a list which says "CA" and you want to
change it to "California" so that all 15 records are updated?
If that's the sort of thing you want to do, you could first change this
query to an append query to create a new table of states. Then you could
have fields of OldName and NewName to batch update them.
But perhaps you are doing something completely different.

Fletcher
 
Access97

I have a table containing addresses with a separate field for State. Is there a
way to create a query that returns an unique list of the states in that table
and still be updateable? I tried setting the unique values property to Yes but
that gave me a recordset that was not updateable.

Thanks!

Marie
Click to expand...

No, not even in principle. If your table has 60 records for "CT" in
the State field, and you do a unique values property, you'll get one
CT in the resulting dataset; if you edit it, which of the 60 records
do you want to change?

Could you explain what you're trying to *accomplish*?
 
To the experts

I find this newsgroup very useful and I refer to it often - sometimes just to see what problems are coming up (as there might be one that I'm experiencing). I find it amazing that you 'guys & gals' can take the sometimes limited question and respond with a dead-on accurate answer

Because I'm currently in the process of normalizing my tables, I interpreted Marie's question differently. It is posted in the 'New User' group, so I (probably wrongly) assumed that she had gone down a wrong path and was stuck trying to get a solution the hard way. I suspect that I will come up against a similar problem of wishing to update a list and also wanting to know what is currently in the list

Correct me if I'm wrong, please. If the "addresses" table is split so that there is a new table for "States" it would create a 'list' that could be updated (with a change, addition, or deletion). My question is: If the user form has a combo box for the 'States' and currently there are only 10 states in the table, how do you make the combo box allow the addition of another state?

Before sending this post, I looked up "combo box" in (Access 2003) help: "If the LimitToList property is set to No, when you enter an entry that isn't in the list, if the combo box is bound, the entry is stored in the underlying field, but it isn't added to the list. To add new entries to the list, use the OnNotInList property and the NotInList event.

I can find and set the LimitToList property to No and I can find the event "OnNotInList". How do I build the event procedure to all additions? A sample would be nice

Thanks in advance

----- Marie wrote: ----

Access9

I have a table containing addresses with a separate field for State. Is there
way to create a query that returns an unique list of the states in that tabl
and still be updateable? I tried setting the unique values property to Yes bu
that gave me a recordset that was not updateable

Thanks

Mari
 
You could avoid the problem altogether, assuming your are storing the Zip.
$30-40 gets you a comprehensive set of 5-digit codes. Make two tables - Zips
with City, and one for the states. Then leave just the zip in your address
table.

(You could create such tables from your existing data, but having a
reference set contributes to data integrity.)

Clive
 
I have a table containing addresses with a separate field for
State. Is there a way to create a query that returns an unique
list of the states in that table and still be updateable? I tried
setting the unique values property to Yes but that gave me a
recordset that was not updateable.
Click to expand...

Why would you want that?

Of course it can't be updatable, because each row in the resultset
represents one or more records.

If you want to regularize data that has been entered inconsistently,
you have two choices:

1. manual: sort the data by the state field and go through and cut
and paste the corrected values. For a couple of thousand records
this may be enough, especially if the number of bad values is small.

2. lookup table: take your unique query and turn it into a MakeTable
to create a temp table with the unique values. After you've run it,
edit the table to add a new column for the corrected value. It would
look something like this:

State NewState
AK
AL
IL
Ill IL
OH
Ohio OH

The first column lists the unique values from the original data
table, and the second column you'll fill out only the values you
want to change.

Then put an index on the first column (it should be unique), and
create an update query that joins the first column to the State
column in your source data table. In the criteria row of the second
column, put in Not Null, and have the update value be the second
column (e.g., [NewState]).

To keep your data regular, you also have two choices:

1. create a State lookup table and use a combo box in your forms to
choose from the State combo box.

2. create a combo box on the unique values already in the State
field. However, if you're going to turn on LimitToList, this makes
little sense, as it's only really useful for fields where you're
likely to be adding values but don't want to maintain a lookup table
(City might be a candidate for this kind of lookup).
 
Back
Top