473,289 Members | 1,875 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,289 software developers and data experts.

Updateable, Unique List

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
5 1959
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
"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
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
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
"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: kevin parks | last post by:
hi. I've been banging my head against this one a while and have asked around, and i am throwing this one out there in the hopes that some one can shed some light on what has turned out to be a...
8
by: Tom wilson | last post by:
This is driving me nuts. I'm trying to update an Excel spreadsheet using ADO.Net and Oledb in VB.Net. The connection is open, the adapter is connected and the dataset is loaded. Here's the code...
22
by: Claudio Jolowicz | last post by:
Is it possible to store unique objects in an STL container? Suppose an object of class C is unique: class C { public: C() {} ~C() {} private:
1
by: Edward | last post by:
I've recently migrated the back end of one of my client's applications from Access to SQL Server. One of the forms is based on an Access query thus: SELECT dbo_tblDistributionDetail.*,...
5
by: Jack | last post by:
TblProduct ProductID ProductName Selected (Y/N) TblOrder OrderID CustomerID TblOrderDetail
3
by: CSN | last post by:
Just wondering, is updateable views slated for a future version of Postgresql? In addition to using rules that is. CSN __________________________________ Do you Yahoo!?
7
by: Stephen Poley | last post by:
I have the following situation: - a table of employees, keyed on employee-id; - a table of training sessions, keyed on session-id; - a requirement to log who was present at which session, plus...
9
by: Brian Tkatch | last post by:
I'm looking for a simple way to unique an array of strings. I came up with this. Does it make sense? Am i missing anything? (Testing seems to show it to work.) Public Function Unique(ByVal...
4
by: othellomy | last post by:
How do I make a view non updateable? I want to create a view so that I will not be able to update, insert or delete the view so that base table is not affected (except dropping the view itself)....
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.