By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,224 Members | 1,103 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,224 IT Pros & Developers. It's quick & easy.

Updateable, Unique List

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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" <mh*****@earthlink.net> wrote in message
news:rS******************@newsread1.news.atl.earth link.net...
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

Nov 12 '05 #2

P: n/a
"Marie" <mh*****@earthlink.net> wrote in message
news:rS******************@newsread1.news.atl.earth link.net...
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


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
Nov 12 '05 #3

P: n/a
On Wed, 03 Mar 2004 21:11:51 GMT, "Marie" <mh*****@earthlink.net>
wrote:
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


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*?
John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public
Nov 12 '05 #4

P: n/a
CDB
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
"Marie" <mh*****@earthlink.net> wrote in message
news:rS******************@newsread1.news.atl.earth link.net...
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

Nov 12 '05 #5

P: n/a
"Marie" <mh*****@earthlink.net> wrote in
news:rS******************@newsread1.news.atl.earth link.net:
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.


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).

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.