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
1044 Views

Similar Articles

[PageSpeed] 16

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:

How do I access multiple records within a query?
I am trying to calculate the total quantity of components in a nested Bill Of Materials where the true quantity of any sub-level component is dependant on the quantity of the previous higher component. Level Item number Quantity 1 A123 1 2 A124 2 3 A125 2 3 A127 1 2 A126 1 2 A125 1 1 A128 1 2 A130 2 Take a look at: http://community.netscape.com/n/pfx/forum.aspx?tsn=1&nav=messages&a...

SUMIF help and another query
I'm wondering if someone could help with a problem I am having - not so much a problem - more I'm not as good at Excel as others here and they may be able to help. I'm treasurer of a semi-professional non-league football club. As such I have to calculate travelling expenses for each player. The tax man taxes players on home games and training sessions but doesn't tax the travelling expenses for away games for those players that take their own cars and not the bus. Currently we pay them 15p per mile plus an additional 3p per mile for every passenger - I know, not much - ...

Update Rollup 3 won't install properly
Hi, After running lots of updates on SBS 2008 server, I finally was offered the Update Rollup 3 to alleviate the problem of my Win7 box not being able to connect. However, I am getting an error when trying to install the patch. After about 40%, the install fails with Code 6BA. Also, the "New Updates are available" baloon pops up in the system tray, offering to install it again. I've tried restarting the server, but can't seem to install this update. Any suggestions would be appreciated. Geordie GeordieB wrote: > Hi, > > After running lots of u...

2005 Update
If anyone was having problems downloading from Vanguard, the latest update for Money 2005 seems to have fixed the problem, where shares are subtracted from investments. -- regards, Michael Abbaticchio MVP for Microsoft Exchange Server http://exchange.mvps.org ...

Subtracting values in a query
I have a query with multiple repeating values, each value has a set of readings assigned to it How do I subtract the max/min from the readings for each value (looking for change over time)? Example: Value Reading over time 1 2 1 3 1 0.5 2 3 2 4 2 1 3 7 3 2 3 0.3 I would like Access to automatically subtract 3 - 0.5 for value 1; 4 - 1 for value 2; and so on. Is this possible? Thanks! Replace 'z' with your [Value] and 'x' ...

sub query
Does MS Access support sub query? I wanted to create a query which has sub query like following Select MyField, (select MyField1 from mytable1 where mytable1.myfiled2 = mytable.MyField3) from mytable Do I need create 2 queries to do this or just one query with one sub query? Your information is great appreciated, Yes. Access supports subqueries like that. More info and some examples: http://allenbrowne.com/subquery-01.html -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at ...

Using saved query for different tables
I want to use the same query for many tables. I'm new to Access. I see where you can save the query and name it, but is there a way to then use it with different tables? I can't see where that can be done. This would be most helpful in Update Queries. -- Barry Barry: You could build the UPDATE statements in code in a dialogue form's module, getting the variable table names from unbound controls on the form, and then execute the statement. You could even modify the SQL property of a saved query in code and then execute it. The fact that you are contemplating thi...

checking queries between programs
Hi all I have several access programs that are extremely similar but written for a purpose at the time I now wish to combine into one access program Is there a simple way of checking queries between these programs to see if they do the same job or not Hope this makes sense, regards, Garry ...

Leading Zeros SQL query
I need to add leading zeros to every item in our RMS database that uses UPCs. Is there an SQL query that could accomplish this? Thanks, Wesley Sanders Hi wesley , I tried this query and it update my itemlookupcode with leading zero where the barcode format is 9(UPCA) but please backup your data before running this query. and before running this ..try to ask for second opinion...I might miss some files that needed to be updated... SQL--> update item set itemlookupcode='0'+ rtrim(ltrim(itemlookupcode)) from item where barcodeformat=9 9=UPCA I hope it helps :) "wessan...

Favorites lost after update
Hello, A friend reports this problem: "Ever since we updated IE, I cannot click on my favorites. The icon next to each link is the default picture sign. If I click on the link, it attempts to print." The system is Windows XP Pro SP3. The update is to IE8. Any thought about how to pursue this further? I know I need to examine the system and will do so, but I would appreciate any prelininary thoughts or direction. Also, if I roll back the IE version with %windir%\ie8\spuninst\spuninst.exe would that bring back the missing favorites? Many thanks. Jack ...

Manipulating or comparing xs:dateTime values in xpath queries with SelectNodes
Is there a simple way of compare dates? I am trying to create a list of perishable stock in an inventory that is near or past its expiration date. I've tried telling it to compare a node's value with a DateTime string, but that doesn't work. I've assumed that it is easier to manipulate today's date instead of the date in my ExpireDate element. Thankyou in advanced for taking the time to read this and possibly helping me Echo 8 ---------------------------------------------------------------------------- -------------------------------- My Code: Public Function FromElem...

Payroll Tax update
How do I grant rights to DYNSA to do a payroll tax update? For some reason 'sa' is not a user and we have been unable to add it as a user. We are unable to update our payroll taxes. Any help with this would be greatly appreciated. Thank you. Tracey D ...

Cannot update
Platform WinXP SP3 When I try to pull down updates from MS sites a window comes up saying that these sites ate only for WINDOWS not MAC. ie. somewhere along the line my machine is being read as a MAC machine. I only have as above, no second OS etc, but for a couple of months I cannot pull down updates. Dave On Sat, 24 Apr 2010 18:38:38 +1000, "David" <dtmail@bigpond.com> wrote: > >Platform WinXP SP3 > >When I try to pull down updates from MS sites a window comes up saying that >these sites ate only for WINDOWS not MAC. ie. somewhere alon...

append query 03-03-08
i am trying to append from one table (NewItems) to another(Main), but get an error saying it can't append 975 [of 4000] records due to key violations. The target table (Main)has a composite primary key made of date and itemnumber. All the items to be added have a new date that doesn't exist in Main. The append query is left joined on NewItems ItemNumber, so that all records from NewItems table should go into Main table. What am i not seeing? Why are 975 items being rejected? thanks. doh! i was using an expression to add , so i had to use a max query instead of the Main table...

The query processor could not produce a query plan
SQL 2005 with SP3 Before I added the following view to the SELECT that follows it, the whole thing has always run fine. This view gets all the unique dates from a main table (tblNotices) and one of its subtables (tblNotices_AdjournmentDates) ALTER VIEW [dbo].[vwLatestAdjSaleDates] AS SELECT DISTINCT NoticeID, (SELECT TOP 1 AdjournmentDate FROM dbo.tblNotices_AdjournmentDates WHERE NoticeID = A.NoticeID ORDER BY AdjournmentDate DESC) AS DateOfSale FROM dbo.tblNotices_AdjournmentDates A UNION ALL SELECT NoticeID, DateOfSale FROM dbo.tblNotices WHERE NoticeID NOT IN (SE...

Money 2007 Deluxe Update ???
When I try to open my money 2007 deluxe file I am getting prompted for an update. When I click on OK to start the update, Money closes and the update starts and gets to approximately 17% downloading and then stops and errors out. Have tried 3 times and every time it stalls and errors out. Anyone else having this issue? Tried it again and this time it got to 99% and did the same thing. "STinGA" wrote: > When I try to open my money 2007 deluxe file I am getting prompted for an > update. When I click on OK to start the update, Money closes and the update > starts ...

How to display query criteria in my Report?
Hi, I have searched but found no answers, so I guess this is standard :P Anyhow, how do I get a certain fields query criteria written in the report? (For example, my data source is a query that selects all customers (cust_id) with annual turnover gretater than 10,000 (criteria is >10000). Now, sometimes I change the criteria so it would be nice to have it dynamically turn up on the report (rather than static text). But what's the "call function" for this? Kindly, Mikael Mikael Lindqvist wrote: >I have searched but found no answers, so I guess this is standard :P &g...

Multiple Listbox selectin and query criteria
I've read many of the posts here about using multiple list box selections to filter queries yet I still cant get it to work. I'll post my understanding then my code (which I am totally clueless about) and see which is at fault. My table is called "sales", my query is "sales summary Query", my filter form is " Filter Report", my listbox is "customerselect" and set to simple multi select I then have a hidden control named "customerselect2" with on click set to event procedure, then in the procedure I've pasted the code ...

drop down boxes query?
hi, i am attempting to create a drop down box that you can (a) search in by entering letters ie. type a and it goes to the a's and (b)can enter you own data in ie. the option you require isnt there so you can go to a blank one and enter your own data. i have seen it on operation before but am lost as to how to do it. please can anyone offer any help at all? many many thanks. Cheers. Have you tryed Data - Filter - auto filter >-----Original Message----- >hi, > >i am attempting to create a drop down box that you can (a) >search in by entering letters ie. type a an...

Query problem (MS SQL and MS Access tables)
L.S, I have a front-end application (MS Access 2000) that uses data from both MS Access tables and MS SQL Server tables. The SQL tables are linked to the front-end application via ODBC. I am trying to perform a query using the following tables: 1. dbo_430_PRProject (MS SQL Server Table) 2. dbo_430_cicmpy (MS SQL Server Table) 3. tbl_ink_bb (MS Access table) 4. tbl_ink_bb_status (MS Access table) Table …  ‘1’ contains information about our projects (Customer-ID, Project description, start date, end date etc.)  ‘2’ contains information about our business contacts (both customers and...

updating spreadsheet
Hello all, I am creating a spreadsheet that will most likely be used as a databas type file. I need to create a link to it, so that multiple people ca update it constantly. Does anyone know the basics of creating a .ln file -- Message posted from http://www.ExcelForum.com Hi a lnk file won't help you :-= This is just a Windows 'Link' pointing to the specific file. (e.g. created within the Windows Explorer). What you probably mean is within Excel the option 'Tools- Share workbook'. Have a look in the Excel help for 'shared workbooks' Note: Excel is not a datab...

Money 2006 wont update Citibank account....
Updating my Citibank account either takes for ages or just plain won't work. Any suggestions? (WindowsXP|SP2 (IE 7.0 Beta 2 installed) ...

cannot write to database query
I have created a query using a table and another query but cannot write into it. I dont get an error message at all just a bleep. I have tried doing the query using the 3 tables that I need but the same thing happens. Each query/table concerned has an ID field which is linked to each other in the relationships area. That means that you have created a query that is not updatable. Beyond that we would need to know more about your table structure and query. As a guess you may need to use a Main form and subform(s) to control the data entry in this situation. In Access Help type th...

Help with Outlook update
I'm using Windows 2000, and decided I would check to see if my Outlook 2003 needed any updates. It found Outlook Collaboration Data Objects Update as a critical update. But when I clicked to install it, I get a window that says: This machine does not have the MS Outlook Email Security Update installed. Please install the MS Outlook Email Security Update before running the CD0 1.21 Email Security Update. I've searched on the web and on the MS webiste/help for anything about this and found nothing. I don't understand what I am supposed to do...it's like it's telling me I n...

Updating Call option prices
I sold some calls on Best Buy and entered the transaction in Money 2003 with the BBYHV symbol. When Money updates prices on the Internet, it doesn't update the price of the calls. What do I need to do to update the prices? In microsoft.public.money, Steve wrote: >I sold some calls on Best Buy and entered the transaction >in Money 2003 with the BBYHV symbol. When Money updates >prices on the Internet, it doesn't update the price of >the calls. What do I need to do to update the prices? See http://groups.google.com/groups?as_q=options++dot&num=50&as_scori...