Update Query - 14329

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

0
tomrector
4/9/2007 7:54:10 PM
access.queries 6343 articles. 1 followers. Follow

5 Replies
1124 Views

Similar Articles

[PageSpeed] 56

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

<tomrector@svsatscott.com> wrote in message 
news:1176148450.373787.81930@d57g2000hsg.googlegroups.com...
> 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
> 


0
John
4/9/2007 8:27:43 PM
On 9 Apr 2007 12:54:10 -0700, tomrector@svsatscott.com 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]
0
John
4/9/2007 8:40:54 PM
On Apr 9, 3:27 pm, "John Spencer" <spen...@chpdm.edu> wrote:
> 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
> .
>
> <tomrec...@svsatscott.com> wrote in message
>
> news:1176148450.373787.81930@d57g2000hsg.googlegroups.com...
>
>
>
> > 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

0
tomrector
4/9/2007 8:50:20 PM
On Apr 9, 3:40 pm, John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com>
wrote:
> On 9 Apr 2007 12:54:10 -0700, tomrec...@svsatscott.com 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








0
tomrector
4/18/2007 8:17:04 PM
On 18 Apr 2007 13:17:04 -0700, tomrector@svsatscott.com wrote:

>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]
0
John
4/20/2007 7:48:27 PM
Reply:

Similar Artilces:

Upgrading to newest money money updates... errors..
I've been using Money 2005 for a month or so... I decided to switch which computer I use it at at home. Just yesterday I think was the last update that asked me to back up my file. Now, I've installed the program on a new computer we got and everytime it tries to download the latest money update it crashes when it goes to install it. It says I dont have access to the file or that it may be 'read only' or not enough disk space (needs 100 megs). I can use the file perfect on the orginal computer, it's not marked read only, and there's over 40GB of space available....

Make Table Query
I have a Macro and at the end of the Macro I have a Make Table Query that I would like to automatically save as todays date in the current database. Does anybody have an idea on how I can do this? On Wed, 06 Jun 2007 11:40:24 -0700, dswiders@gmail.com wrote: >I have a Macro and at the end of the Macro I have a Make Table Query >that I would like to automatically save as todays date in the current >database. Does anybody have an idea on how I can do this? A date is just a field value. Saving today's date as a Table is almost certainly VERY bad design. Why do an expensive, awk...

Is Version Updated after sp-1 is updated on Money 2007
After you get the automatic software downloaded is it necessary to click onto the sp-1 to install? -- Deb No it is automatic. If I recall, after the SP1 is downloaded, you are prompted to close Money and restart it. After you restart Help > About should give you a version number ending in .1024. -- Regards Bob Peel, Microsoft MVP - Money For UK tips & fixes see http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny. I do not respond to any emails that I have not specifically asked for. "Rebelleheart" <Rebelleheart@discussions.microsoft.com> wrote in messa...

Corstabquery or normal query
Hello, Maybe I have a langguage problem in expressing my difficulties, but here is what I wanted Normal report based on the normal query: Year 2005 No. Position Name 1, Elder Mr. A 2. clerck Mr. B 3. Deacon Mr. C Year 2006 1, Elder Mr. D 2. clerck Mr. E 3. Deacon Mr. F Year 2007...same way downward I wanted it that the year is Sideway" No. Position Name Year 2005 Year 2006 Year 2007 1, Elder Mr. A Mr. D ....... 2. clerck ...

Sum of a UNION Query
Hello, I want to sum the values together that are returned from the following two queries joined with a UNION from within the same query (instead of creating a second query that will SUM the two values that are produced from this one). I tried "wrapping" this code within a SELECT SUM, but it returns with "this is not allowing in subqueries". SELECT Count(*) AS SubCount FROM [TABLE1] WHERE (([TABLE1].USER)="JOHNSMITH") UNION ALL SELECT Count(*) AS SubCount FROM [TABLE2] WHERE (([TABLE2].USER)="EJONES"); The results are: SubCount -------------- 380 45...

Online Updates
When trying to complete online update, the computer states the Money has encountered an error and must shut down. Have upgraded to 1415. Help! BTW: I'm using Money Plus Deluxe and XP "Cheyne14" wrote: > When trying to complete online update, the computer states the Money has > encountered an error and must shut down. Have upgraded to 1415. Help! ...

Append Query every day at certain time automatically
Hi, So i have a query qryStats. Basicaly i want to be able to see how the stats have changed over a period so i was thinking of turning that simple query into an Append Query so that it just add the results to a table tblStats. I tested it and it works great so i can design reports and the result is exactly what i want. Problem is im going to forget to run the query each day. What are my options? Can i write code of some kind that runs the append query each day at, lets say 16:00? YES but your db would need to be running in order for that to work. You could also create...

Try that update which comes from the M$ Corp.
--pxulfresdind Content-Type: multipart/related; boundary="xepmincpbkqfdfbp"; type="multipart/alternative" --xepmincpbkqfdfbp Content-Type: multipart/alternative; boundary="zhaxriplmgq" --zhaxriplmgq Content-Type: text/plain Content-Transfer-Encoding: quoted-printable MS Partner this is the latest version of security update, the "October 2003, Cumulative Patch" update which eliminates all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three newly discovered vulnerabilities. Install now to p...

Script for Query-Based DL's
Pardon me if posted in the wrong place. We are scripting a large amount of OU structures and administration groups with delegation through dsacls. This is something that is repeated many times. One item that is asked to create is a query-based DL. Since it would need to be ran so many times and for consistency I am trying to find if we can have the script create the Q-B DL's. I have found scripts for dsacls and the Q-B DL's but I'm having a hard time finding how to script the creation of the DL's. Does anyone know how this would be done? Thank you, Michael ...

Updating Money Deluxe 2007 question
Will Microsoft keep the Money updates available permanently, as they do with updates for old operating systems like Windows 98? The reason I ask is that, at least in my experience, Money updates have never been a manually downloadable file, but rather once you install the program, Money calls out on the Internet and looks for updates. If Microsoft doesn't keep the updates online, is there any way to download the update files now for permanent keeping? I did a test. I installed Money 2007 Deluxe on my Virtual PC. As soon as I went into the program after installation, it updated ...

can you make one worksheet update another
I have two similar worksheets within the same workbook. One sheet is sorted by name and the other sheet is sorted by a date for a speicfic name. Is there a way to change the date on the sheet, sorted by name, and that will automatically find the name on the other sheet(sorted by date) and change the date to the second sheet? Jaime, The normal way is to have one table, and sort it as needed when needed. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Jaime S." <Jaime S.@discussions.microsoft.com> wrote in mess...

update the same cell into another column forming a list on opening
hi there, i have made a spreadsheet to enter data and then view the resulting statistics that are generated, i have one cell that changes in value after entering the data, and i would like to keep a record of what values this cell has been, i was after a formula that would update that single cell's value into a column forming a list each time the spreadsheet was opened or the value in the cell changed, like a field perhaps, this would fill up the column with a list of results from which to generate more stats and a graph etc. any help would be great, thank you -- thank...

Recordset Not Updateable
I have set up an access database to track technical support and training services. I'm pretty new to Access and Very new to VB programming. I have hit a wall with something and have found no solution in help or on these discussion groups. Here's what I'm attempting to do, from the user's perspective. This process works great up to the very end, then things fall apart. Let's say the user wants to look at all open support cases. The user presses a button called "All Open Cases." A very basic query runs, and the results are shown in a read-only (contin...

updating and underlying table field with a combo box selection
I want to use an ID value from a combo box on a subform to update the same ID value in the form's underlying table. My main form is: frm_Street_Joiner_Main My Subform is: frm_Street_Joiner_Sub My Subform table is: tbl_Street_Joiner My Mainform is: frm_Street_Joiner_Main The combo box on my subform is called: StreetName, with a column count of two but the bound column is the actual street name. SELECT QRY_Street_Names_Joiner_Master.Street_Names, QRY_Street_Names_Joiner_Master.StreetNameID FROM QRY_Street_Names_Joiner_Master ORDER BY QRY_Street_Names_Joiner_Master.Street_Names, QR...

Help with a simple query
Hi guys and gals, Can anyone help me with this query? I am having some issues with pulling in all names even though (it appears) I have the join correct. SELECT tblEmployee.EmpRptID, tblEmployee.UserName, Sum(tmpReports.NumOfSets) AS SumOfNumOfSets FROM tblEmployee LEFT JOIN tmpReports ON tblEmployee.UserName = tmpReports.UserName WHERE (((tblEmployee.IsCQATech)=True) AND ((tmpReports.CompleteDate) Between #2/8/2010# And #2/12/2010#)) GROUP BY tblEmployee.EmpRptID, tblEmployee.UserName; What am I doing wrong? You have defeated the left join by applying criteria to the right s...

Merging queries by column in access
Hi, I have a series of queries which get data aggregated by year, e.g. select year(date), min(var1), avg(var1), max(var1) from table where (ridiculously complicated set of conditions) group by year(date) select year(date), min(var2), avg(var2), max(var2) from table where (entirely different ridiculously complicated set of conditions) group by year(date) etc etc There are six queries like this so what I want is to amalgamate these columns together into one results table (and also into one query so users can just run one stored query rather than six!) as follows: year, m...

Mircosoft Money 2003 Deluxe share update
> Hi , > > I have Mircosoft Money 2003 Deluxe which I use mainly to record shares > trades in the Portfolio area. I am wondering if someone could explain me the > process to update my shares prices. i understand there is a manual way of > doing that but I would prefer having it done automatically. > > Cheers Versions that still have "online services" use Internet Update to get stock quotes for investments defined with symbols that are available online. Your version may be too old to still qualify for online services. "fab" <fab@discussio...

update shape
I want to use a consistent shape in many pages. Is there a way to create a master copy such that when I update the text on a master copy all copies are updated. I created a new stencil and new master but updating the master doesn't seem to update the copies (after i save the file). Amos You have to update the master in the local, or "Document Stencil" Every Visio drawing contains a "hidden" Document Stencil, that carries copies of masters from "external" stencil files. When you drag a new shape into a drawing, the master gets copied from the external .v...

windows update fail...... What now?
ok so I had a problem with windows update downloading but not installing.. I did go to the windows file in the C: drive and manually installed it myself. Thinking that it would fix my install problem.. I was wrong.. I next have found out that in my event logger that my IIS6 is not working properly or something. It doesn't look right and in my event logger it says my security assessment tool 4.0 error 1303 I am the administrator and logged in already so what the heck? And I also would like to say that my computer is old and it's a IBM think pad running windows xp perfessio...

401K Mutual Fund Manual Price Updates
I have a 401K that tracks NAV of the funds differently than the prices auto-downloaded by Money for the same symbol (don't ask me why???). I have turned off Auto-Update of those funds so I can manually update the fund prices myself. The problem is that no matter how many times I try to delete previous on-line price updates, they remain with the Fund. This is kind of minor, but also kind of annoying. I've learned when I add a new fund to this account to disable price updates before my next update and this works for new fund additions, but the old funds are still maintaining t...

Update a combobox on another form
This must be simple... I have a main form(A) with a subform(B) containing a combobox linked to a table. On the main form(A) I open a form(C) containing the "combobox linked" table After I add/change/delete data from this table and close the form(C) I want to refresh the combobox on the subform(B). On form(C) i have a close button with clicked event: Forms![mainForm]![subform].Form![combobox].Refresh DoCmd.Close The Forms! line gives an error that this isn't supported.??? Any ideas? -- Patrick It was simple :) Changed Forms![mainForm]![subform].Form!...

disk mgmt query
I have a desktop system with two different sized HDs with both having the identical xp media edition installed. The problem is that windows is not allowing me to manage the pecking order. Drive "0" is being read in disk mgmt as drive D and Drive "1" is being read as drive C. In the bios upon boot up, Drive 0 is in fact being read as the primary master and Drive 1 read as the primary slave. I have tried to change the letters of these drives to drive 0 = C and drive 1 = D but a window pops up saying that I can't change the name of the boot drive. "Windows c...

Query about Last Cell reference.
I have 12 sheets, which holds monthly information. Data exists in Column A through K, row 1 down to row 500. I have a formula in cell AZ1 (done to ensure no one can find it and subsequently delete it). When I depress CTRL+END it takes me to AZ500 (as expected). The question I have is that as all my data is going to be in Columns A through K and rows 1 to 500, if I reset the Last Cell to say K500 would it achieve faster saves to a LAN?. Please note that the value from AZ1 is used in column G1 – G500. All views greatly appreciated. First, you can olnly "reset" the last cell ...

adding a Carriage Return or Line Feed character to an update query
I'm building an update query that will concatenate two fields and update the first field with the combination of the two fields. However, I would like to separate the two components with a blank line, or two carriage return or line feed characters. In Visual Basic, I'd use vbcr&vbcr to do this. How can I do this in an update query? Thanks in advance, Paul Paul wrote: >I'm building an update query that will concatenate two fields and update the >first field with the combination of the two fields. However, I would like >to separate the two...

Updated appointments stay in calendar
When an appointment has been updated we have found by several clients that the appointment that was updated stays in the calendar causing a double appointment. The old appointment is not automatically removed when the new appointment has been accepted. Can anyone please let me know of a solution to this problem. thanks, Yvonne ...