Update Query

[tomrector]

Access 2003 XP SP2

I am having a problem with an update query.

Table is in a one-to-one relationship, referentail integrity and
cascading data are checked.
(The fileds I want to update are not in both tables)

Table name= payForward
Has 15 fields ie: ID, Name, MemID, Oct , Nov, Dec, etc
Oct-Sep fields are yes/no type

I want to "select" a field (Oct-Sep) via a query parameter and
repalce "yes" with "no".
Here is my query:

UPDATE payForward SET [Enter month]=No

The messages I get is
'operation must use an updateable query'

What can I do ?


Any direction will be appreciated !
Thanks

 

[John Spencer]

You can't do it that way. Parameters are only allowed for criteria not for
names of objects (fields and tables) or for operators (=, +, -, >, etc.).

A very messy way to do this might be

UPDATE PayForward
SET Oct = IIF([Enter Month] = "Oct", False,[Oct])
, Nov = IIF([Enter Month] = "Nov", False, [Nov])
, ...
, Sep= IIF([Enter Month] = "Sep", False, [Sep])

A better way would be to use VBA code to build and execute the query. How
are your VBA skills?

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[John W. Vinson]

Access 2003 XP SP2

I am having a problem with an update query.

Table is in a one-to-one relationship, referentail integrity and
cascading data are checked.
(The fileds I want to update are not in both tables)

Orange flag right there... storing the same data in two tables is almost
surely A Bad Idea. One to one relationships are quite rare; if you're not
doing Subclassing or Table Driven Field Level Security then you probably don't
want to structure your tables in this way.

Table name= payForward
Has 15 fields ie: ID, Name, MemID, Oct , Nov, Dec, etc
Oct-Sep fields are yes/no type

And a big red flag there. Storing data in fieldnames *is incorrect design*.
You're "committing spreadsheet upon a database", a misdemeanor punishable by
being required to read about Normalization:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

The links in Jeff's list include "Database Design 101", which may prove
helpful.

Just for one (of many) concerns - is Oct October 2006? 2007? 1985? Do you
create a new database every year...!?

I want to "select" a field (Oct-Sep) via a query parameter and
repalce "yes" with "no".
Here is my query:

UPDATE payForward SET [Enter month]=No

The messages I get is
'operation must use an updateable query'

What can I do ?

Normalize your tables, for starters; you CANNOT pass a fieldname as a
parameter (and would not need to if you weren't storing data in fieldnames).
You will need to build your Query's SQL string in VBA code if you insist on
using this incorrect design.

John W. Vinson [MVP]

 

[tomrector]

You can't do it that way. Parameters are only allowed for criteria not for
names of objects (fields and tables) or for operators (=, +, -, >, etc.).

A very messy way to do this might be

UPDATE PayForward
SET Oct = IIF([Enter Month] = "Oct", False,[Oct])
, Nov = IIF([Enter Month] = "Nov", False, [Nov])
, ...
, Sep= IIF([Enter Month] = "Sep", False, [Sep])

A better way would be to use VBA code to build and execute the query. How
are your VBA skills?

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Access 2003 XP SP2

I am having a problem with an update query.

Table is in a one-to-one relationship, referentail integrity and
cascading data are checked.
(The fileds I want to update are not in both tables)

Table name= payForward
Has 15 fields ie: ID, Name, MemID, Oct , Nov, Dec, etc
Oct-Sep fields are yes/no type

I want to "select" a field (Oct-Sep) via a query parameter and
repalce "yes" with "no".
Here is my query:

UPDATE payForward SET [Enter month]=No

The messages I get is
'operation must use an updateable query'

What can I do ?

Any direction will be appreciated !
Thanks- Hide quoted text -

- Show quoted text -

Thanks for the quick response,, your query works great, I think it
will be sufficient.
My VBA skills are limited, but I am good at copy and paste

Thanks much for the help,
Tom Rector

 

[tomrector]

Access 2003 XP SP2

I am having a problem with an update query.

Table is in a one-to-one relationship, referentail integrity and
cascading data are checked.
(The fileds I want to update are not in both tables)

Orange flag right there... storing the same data in two tables is almost
surely A Bad Idea. One to one relationships are quite rare; if you're not
doing Subclassing or Table Driven Field Level Security then you probably don't
want to structure your tables in this way.

Table name= payForward
Has 15 fields ie: ID, Name, MemID, Oct , Nov, Dec, etc
Oct-Sep fields are yes/no type

And a big red flag there. Storing data in fieldnames *is incorrect design*.
You're "committing spreadsheet upon a database", a misdemeanor punishable by
being required to read about Normalization:

Jeff Conrad's resources page:http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:http://www.mvps.org/access/resources/index.html

The links in Jeff's list include "Database Design 101", which may prove
helpful.

Just for one (of many) concerns - is Oct October 2006? 2007? 1985? Do you
create a new database every year...!?

I want to "select" a field (Oct-Sep) via a query parameter and
repalce "yes" with "no".
Here is my query:

UPDATE payForward SET [Enter month]=No

The messages I get is
'operation must use an updateable query'

What can I do ?

Normalize your tables, for starters; you CANNOT pass a fieldname as a
parameter (and would not need to if you weren't storing data in fieldnames).
You will need to build your Query's SQL string in VBA code if you insist on
using this incorrect design.

John W. Vinson [MVP]

John,
Don't understand what you mean exactly "storing data in a
fieldname". Maybe i didn't cover it correctly, but I am interested in
doing it the best way.
I'll try again

My database is for managing a monthly rental parking lot for RV's
I have two tables, Customer and Payforward (linked on MemID)
Customers has many fields with MemID as the unique index field
PayForward has 15 fields including MemID unique also and indexed
Other fields in PayForward are Name, (System ID) and Oct through Sep
Oct -Sep are yes/no data type fields, these fields are not in
Customers.
I want to match the related records in PayForward with the records in
Customers (done, one to one)
I want update the Oct or Nov or Dec or Jan fields (yes/no) in the
records in PayFrward with query.
I want to select the month to update as parameter so I don't have to
have 12 queries.
At the end I will run a report on PayForward showing name, Oct, Nov,
Dec, etc with checks (or yes no)
Now what I didn't mention is that this query would runs on criteria
from Customers records in Customer where
user has CreditCard is not null and "billthiscycle" field' was
checked.
It seem to be working fine with John Spencers' recommendation. While
I only have about 400 records,
there is no hesitation or delay, update query run in less than 2-3
seconds.

Thanks for any input or advice, and the response..

Tom Rector

 

[John W. Vinson]

John,
Don't understand what you mean exactly "storing data in a
fieldname". Maybe i didn't cover it correctly, but I am interested in
doing it the best way.
I'll try again

My database is for managing a monthly rental parking lot for RV's
I have two tables, Customer and Payforward (linked on MemID)
Customers has many fields with MemID as the unique index field
PayForward has 15 fields including MemID unique also and indexed
Other fields in PayForward are Name, (System ID) and Oct through Sep
Oct -Sep are yes/no data type fields, these fields are not in
Customers.

That's exactly what I mean by "storing data in fieldnames".

You are storing data - a month, Oct, Sep, etc. - in fieldnames. Months ARE
DATA which should be stored in a date field.

If each Customer can pay for more than one month, you need *ANOTHER TABLE*
with one record for each month paid. "Fields are expensive, records are
cheap"!

I want to match the related records in PayForward with the records in
Customers (done, one to one)

Should be one to many.

I want update the Oct or Nov or Dec or Jan fields (yes/no) in the
records in PayFrward with query.

You can't, not without constructing a SQL query in VBA code. On the other hand
it's trivially easy to add a new record for September 2007 or January 2008 to
a table of payments.


John W. Vinson [MVP]