Re: Update Query

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

On Apr 9, 3:40 pm, John W. Vinson <jvinson@xxxxxxxxxxxxxxxxxxxxxxxxxx>
wrote:
On 9 Apr 2007 12:54:10 -0700, tomrec...@xxxxxxxxxxxxxx wrote:

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








.

Relevant Pages

  • Re: Use a Qry as a filter in a form
    ... "John W. Vinson" wrote: ... Then you'll need to record somewhere in the table when the customers "come". ... record the exact time, Now), and run an Append query appending to the other ...
    (microsoft.public.access.forms)
  • Re: Simple Query
    ... John, ... My table is called MN&C Direct All (customers) and the fields are ... Is it just a question of substituting these into the query? ... have only dead accounts (i.e. ...
    (microsoft.public.access.queries)
  • Re: Re: Need some input on an Event Procedure for a search
    ... could try it with just needed a slight alteration to the SQL query. ... specific Features which some Customers would be looking for. ... The Customers table includes contact details, ... and the specific command for a mailout which is the event ...
    (microsoft.public.access.gettingstarted)
  • Re: Sum of numbers
    ... "Evi" wrote: ... You say the union query 'only shows fields from the first table'. ... Do you mean that you want a multicolumn report with all customers' names ...
    (microsoft.public.access.reports)
  • RE: Store Added Value List Items
    ... You could even union that query with the actual list from the form ... Each user in this database needs to have access to all the same customers. ... is praticle to create a seperate one field table to store values for a lookup ...
    (microsoft.public.access.formscoding)