update query

[Robert Blackwell]

I need to run an an update statement like this on an existing dbase query. I
know this script would work but AFAIK access doesn't use plain sql
statements like these and everything is wysiwyg

UPDATE _qryInactiveCustomers SET StatusID='2' WHERE StatusID='1';

how do I go about doing this?

 

[Joseph Meehan]

I need to run an an update statement like this on an existing dbase
query. I know this script would work but AFAIK access doesn't use
plain sql statements like these and everything is wysiwyg

UPDATE _qryInactiveCustomers SET StatusID='2' WHERE StatusID='1';

how do I go about doing this?

Set Criteria of StatusID equal to 1

Set UpDateTo equal to 2

Run UpDate Query.

 

[Robert Blackwell]

I had already tried that and got this message.

"Operation must use an updateable query."

 

[Joseph Meehan]

I had already tried that and got this message.

"Operation must use an updateable query."

That is a different problem. If the error message is correct, you can't
update that query, even manually due to the relationships.

You should be able to update using that method if you base the query on
a table or a query joined to the table is such a way as to allow updates to
that field.

 

[Robert Blackwell]

Okay...how can I get around this? basically, the originally query is a list
of customers whos most recent order was before 12/31/2001.

I need to change all their statuses to inactive so they do not continue to
get mailings form us. Like you said, I can't even change it manually when I
run the query and see all the records, I can't edit anything.

Perhaps dumping a list of customer id's and then doin some type of query to
change all the status's of those id's to inactive? How would I even do this.

 

[Joseph Meehan]

Okay...how can I get around this? basically, the originally query is
a list of customers whos most recent order was before 12/31/2001.

I need to change all their statuses to inactive so they do not
continue to get mailings form us. Like you said, I can't even change
it manually when I run the query and see all the records, I can't
edit anything.

Perhaps dumping a list of customer id's and then doin some type of
query to change all the status's of those id's to inactive? How would
I even do this.

It's difficult from here. I can't see the data or relationships

Let's start with a question. What is the query(s) and or table(s) you
are looking at based on? View the query in question in design view and look
at the top. There should be one or more boxes with the names of queries or
tables. They should also be connected by lines. Do the lines have arrows
and where do the point.

 

[Robert Blackwell]

http://www.wowcentral.com/random/relationship.gif

 

[Joseph Meehan]

http://www.wowcentral.com/random/relationship.gif

A quick look seems to indicate that "StatusID" that you are trying to
change, is linked to "ConStatusID" as part of the query. Again quick look
would indicate to me that you will not be able to change Status ID as long
as it being used to link to "ConSatusID" and if you do make changes to
"StatusID" the links to the "ContactData" table will be broken, unless you
have arranged for cascading changes to that table.

I might also suggest that StatusID looks like it is a keyfeld and allows
no duplicates, so you need to consider that as well.

 

[Robert Blackwell]

Btw, I removed the status table and just used the constatus id field from
contact data table, and it's still uneditable.

 

[Robert Blackwell]

What I ended up doing, is copying the cells with the contact Id, copying
them to a new table, then running a new query to match existing contacts to
those ID's and then update the account of those ids to the appropriate
status. A little clumsy I guess but it worked.