f



Update table using data from another table - MS Access

I believe the following a valid SQL statement, but MS Access returns
with an error "Operation must be an updateable query". 

Any suggestions would be greatly appreciated!

UPDATE FERCPTILoad AS FL 
SET FL.[Load Dist Factor] = FL.[MW]/(
    select FA.[Load MW] 
    from FERCPTIArea AS FA
    where FA.[Area Num] = FL.[Area Num of Bus]);



*** Sent via Developersdex http://www.developersdex.com ***
0
blong (1)
6/9/2006 10:57:22 PM
comp.databases.ms-access 42670 articles. 0 followers. Post Follow

1 Replies
769 Views

Similar Articles

[PageSpeed] 22

Bo Long wrote:
> I believe the following a valid SQL statement, but MS Access returns
> with an error "Operation must be an updateable query".
>
> Any suggestions would be greatly appreciated!
>
> UPDATE FERCPTILoad AS FL
> SET FL.[Load Dist Factor] = FL.[MW]/(
>     select FA.[Load MW]
>     from FERCPTIArea AS FA
>     where FA.[Area Num] = FL.[Area Num of Bus]);
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***

Why not try using DLOOKUP instead of the subselect?  My guess is that
since the subquery could conceivably return more than one record, the
update query won't work.

0
pietlinden (2926)
6/10/2006 2:54:17 AM
Reply:

Similar Artilces:

Access Transferring Data from one table (form) to another table (form)
I have a form that stores client information called "Client Database". Some of these people I want to remove them from the client form and move them onto our vip form by clicking a button on the client form. How can I do this? On 23/01/2015 18:16:07, wrote: > I have a form that stores client information called "Client Database". > Some of these people I want to remove them from the client form and move > them onto our vip form by clicking a button on the client form. How can I > do this? > Information is not stored on forms, it is stored in table...

Import data (not tables) from one Access database to another?
We've created a new database and need to import the data from our old database. Both are MS Access databases and each have multiple tables. The table structure of each table is completely different from each other, but the actual fields of the data we need to move exist in both tables. From what I see in the MS Access help I can only find how to import a complete table from another Access database. How can I import the data from table 1 of one file into various tables in the second file? e.g. Move the like data from the tables in FileOne to the tables in FileTwo. (notice that the field ...

SQL for Quering Access Tables using a Table/Field List Table
I have a Table called TableField that has the table names, fields and Data type of all the tables and fields in the database. They are set up as follows: Table: TableField TableName FieldName Type Table1 LName Text Table1 FName Text Table1 Address1 Text Table1 Address2 Text Table1 State Text Table1 Zip Text Table2 Color Text Table2 Descrip Text Table2 Size Text Table2 Temp Text . . Table65 ... Not having much Access experience, If I were looking for a specific value "e.g., Orange" located in one o...

How to import/map excel table data to access table data?????
Hi. How do I import while mapping an excel table to an access table please??? I've searched around and all I can find is a software product or code that does the same thing as the access wizard..... If I have to write vb code, where is a sample??? Or do I import the excel in to a new spreadsheet and then write some kind of querey to move the data from that table to my current production table.. How do I do this? Seems to me, access would be able to do this??? Thanks, ANY infor would be appreciated.! Fred "Fred" <fgarvin2000NOSPAM@yahoo.com> wrote in message news:&...

Update Access Table with Excel Table
I have a table that is being utilized in an Access database. There is a number of updates that need to be made on a monthly basis. The updates are sent in Excel format. Is there a way that I can upload the information from the Excel sheet into the Access table? I tried importing it and it won't let me do that because the it can't override the information in the table. I tried linking the database to an excel spreadsheet, but that slows the database down significantly and removes the ability of multiple people accessing the database at the same time. Preferably I would like to be a...

Updating data in a VFP 6 table using OLE from an Access app
I am interfacing with an app originally built with Visual FoxPro 6. I am building extensions using Microsoft Access. Unfortunately, the client must continue to use the existing app as is, and I am not allowed to simply convert it to Access. But I know Access and want to build the extensions in Access. For all but one of the VFP tables I can connect via ODBC so easy. One table, however, fails when trying to set an ODBC connection. I get an error: "Invalid field definition Deleted() in definition of index or relation". From my research some suggested such is due...

SQL7 Table Not Updatable as Linked Table In Access
I have an Access database that links to a set of tables in a SQL7 database and performs updates. This works fine as long as there is an "Identity" column in the SQL7 table. But we have some databases out there at a few of our customer sites where the tables do not have an identity column. It works there as well as long as the tables are linked manually an the user indicates the correct column as the primary key column. But we provide a "refresh" function that cycles through the tables and reconnects all of them with code. In that case, the tables all become not updatable. Is there a way in the code to specify the key column? Zack Sessions ...

Update One Table from Another Table Using SQL
Using SQL in SAS 8.2, I would like to update the contents of specific = rows in one table using rows with matching composite key column values = from another table. In other SQL dialects, this could be accomplished = using code similar to the following: /* syntax 1 (Microsoft SQL Server) */ update table1 set table1.column1=3Dtable2.column1, table1.column2=3Dtable2.column2 from table2 where table1.key1=3Dtable2.key1 and = table1.key2=3Dtable2.key2; /* syntax 2 (Microsoft Access, MySQL) */ update table1, table2 set table1.column1=3Dtable2.column1, table1.column2=3Dtable2.column2 ...

Access populate field in table from another field in the same table
I have two fields, "Item" and "Information" in the same table. "Information" contains three items from a seperate table created by a lookup. I would like to substring out the first item and place it in the "Item" field. Is there an easy way to do this? I would like the "Item" field to populate after the contents of "Information" have been selected. Thanks in advance for any assistance. Item Information Item1 Item1 Item2 Item3 On Tue, 20 May 2008 13:40:45 -0700 (PDT), sd_eds <sd_eds@hotmail.com> wrote: With a ...

creating a table for accessing data via Access forms
I have to create an Access 2007 table which has data about different divisions(and departments inside it) of a company. company A has divisions of Sales, Engineering, Customer Support, Corporate Office. Sales division further has departments like marketing, pre-sales department, accounting. Engineering division has departments like Research and Development, Building group, Testing group. Customer Support has departments like Phone Support, Email support, Feedback. Corporate Office has departments like CEO office, Public Relations and so on. For each department of a division like Engin...

inserting table data from a Word document into an Access table
The Word 2003/2007 document(some files are in "docx" or Word 2007 and some are in "doc" Word 2003 format) has data as indicated below: ...some text on line 1 which is not needed.. ...some text on line 2 which is not needed.. ...some text on line 3 which is not needed.. Table 1 Row 1 has no columns ....some data which is not needed... Row 2/Column 1 Row 2/Column 2 ...data not needed.. ..data needed.. Row 3/Column 1 Row 3/Column 2 ...not needed.. ..data needed.. Row 4/Column 1 Row 4/Column 2 ...not needed.. ..data needed.. R...

Updating data in table depending on data in same table
Hey everyone, First of all apologies for posting this in the oracle.server section I meant to post it in here first. I'm still a relative newb so go easy please! I've inherited a system from someone that is in a bit of a mess and I need to tidy it up. It's an Oracle 10g database. The part i have to clean up basically has two tables. Table A attributes are: SOURCEID, NAME, DESCRIPTION, DEPARTMENT, LANGUAGES. Table B attributes are: SOURCEID, LANGID, ROLE, STATUS. Now the way this system is supposed to work is that a row is entered into table A and X amount of languages are added to the LANGUAGES field. This field is then split up into a row for each language into table B with the corresponding SOURCEID. e.g. Table A: 123 -- Homer -- lazy -- HR -- French;Spanish;German Table B: 123 -- French -- manager -- approved 123 -- Spanish -- admin -- pending 123 -- German -- teaboy -- proposed This system had been working fine until a new person started and instead of just adding a new language to the original row in table A she added 1000+ rows that are identical to those already in the table except for the fact the language is Japanese. So now the table looks like Table A: 123 -- Homer -- lazy -- HR -- French;Spanish;German 456 -- Homer -- lazy -- HR -- Japanese; Table B: 123 -- French -- manager -- approved 123 -- Spanish -- admin -- pending 123 -- German -- teaboy -- proposed 456 -- Japanese -- janitor -- approved I have...

How can I use data in one temporary table to modify data in another? SQL Update puzzle!
Here are the tables: CREATE TEMPORARY TABLE fee_change_dates ( fd DATE ) ENGINE = MEMORY; CREATE TEMPORARY TABLE cycle_dates ( sd DATE, ed DATE, fee DOUBLE ) ENGINE = MEMORY; cycle_dates will have a pair of start and end dates for consecutive periods (i.e. Saturday through Tuesday, for one record and Wednesday through Friday for the next, &c.) fee_change_dates will typically have only a handful of dates, dates on which fees have changed (not known a priori - have to be obtained from the DB). These dates will not be in either field in cycle_dates. There is the po...

Problems accessing a MySQL database via an Access linked table
Hello In order to perform a hetrogeneous join (I think that's what they're called) between MySQL and another database, I've created an Access database containing linked tables from each datasource. I create the linked tables without any problems, clicking on "save password" before selecting the desired tables. I can then access both tables fine from within the Access GUI. The problem starts when I then try to access this data from my ASP.NET web application - classic ASP works fine but .NET gives me... ERROR [HY000] [Microsoft][ODBC Microsoft Access Driver] ODBC--conn...

How can I get data from Access Queries instead of Access Tables?
I can get data from Microsoft Access Tables in Labview using DSN, but can not get it from Access Queries? Does anybody know what the difference is? Thanks. ...

Database updates (using SqlDataAdapter) during runtime but Database Table Does Not Update
I apologize if this is a simple question, but I am very new to SQL Server. I feel like I am missing something easy here. I an running VB ..NET from Visual Studio 2005 and SQL Server Express. The code in question is as follows: ChosenProjectName = TextBox2.Text Dim NewProject As New DataSet OpenConn() Dim GetProjectList As String = "SELECT * FROM Approved_Projects ORDER BY Project_ID DESC" Dim GetProjectsAdapter As SqlDataAdapter = New SqlDataAdapter(GetProjectList, MainCon) Dim myDataSet As DataSet = New DataSet() Dim myDataRow As DataRow GetProjectsAdapter.Fill(myDataSet, ...

collecting data using email in a Access table
I am collecting a group's information(name, phone number and three paragraphs describing their hobby). I want to send an email to all the group recipients which allows them to enter the information(name, phone number and three paragraphs describing their hobby) and update it in a Access table stored on my machine. One way I can do this if all recipients have Outlook 2007 is using the email create facility of Access 2007 which is described at http://www.microsoft.com/downloads/details.aspx?FamilyId=618891E8-7145-44C8-9E40-82218B1E8DF7&displaylang=en This creates a ...

Using data from one Access database in another
The situation: A database is wanted. It's purpose is to show some co-workers' skills. Therefore I will need to make a database that contains names and the posts "skill1", "skill2", "skill3" etc. (YES/NO-posts) This should be easy; one table in a simple database, but... In the company we already have another database containing the co-workers work-ID-number and their names. I would like to use this existing information in the new database, then we will not need to maintain another database with names. (and no, I'm not allowed to extend the alre...

Replace an Access database table using VB6
I am trying to replace an entire table that exists in my Access database us= ing VB6. I have completely updated the table with new records and need to = send out the update to everyone using the database. The table name, etc. w= ill remain the same, it's just has all new records that are inside of it. Does anyone know how to do this? =20 Thanks! On Mon, 3 Jan 2011 08:51:02 -0800 (PST), bfryman <bfryman@gmail.com> wrote: >I am trying to replace an entire table that exists in my Access database using VB6. I have completely updated the table with new records and nee...

How to update a table with another table
Hi to All, I have in my 'schema' 2 tables: CREATE TABLE T_ITEMS ( ITEM_GROUP VARCHAR2(10 BYTE) NOT NULL, ITEM_CODE VARCHAR2(25 BYTE) NOT NULL, ITEM_DESCRIPTION VARCHAR2(40 BYTE) NOT NULL, ITEM_QTA NUMBER(16,4), ITEM_PRICE NUMBER(18,5) ) TABLESPACE DOM8DATI PCTUSED 0 PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 2M MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 BUFFER_POOL DEFAULT ) LOGGING NOCOMPRESS NOCACHE NOPARALLEL MONITORING; CREATE TABLE T_PRICES ( ITEM_GROUP VARCHAR2(10 BYTE) NOT NULL, ITEM_CODE VARCHAR2(25 BYTE) NOT NULL, ITEM_PRICE NUMBER(18,5) ) TABLESPACE DOM8DATI PCTUSED 0 PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 2M MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 BUFFER_POOL DEFAULT ) LOGGING NOCOMPRESS NOCACHE NOPARALLEL MONITORING; I need to create a script that for each record in T_ITEMS, read if exist the code (ITEM_GROUP,ITEM_CODE) in T_PRICES and update ITEM_PRICE in T_ITEMS. Can anyone help me? Thank You and Best Regards Gaetano "nick048" <nicosia.gaetano@moonsoft.it> schreef in bericht news:58c09989-2361-41e1-9db9-dee2dd6cc99c@z17g2000hsg.googlegroups.com... > Hi to All, > > I have in my 'schema' 2 tables: > > CREATE TABLE T_ITEMS > ( > ITEM_GROUP VARCHAR2(10 BYTE) NOT NULL, > ITEM_CODE VARCHAR2(25 BYTE) NOT NULL, > ITEM_DESCRIPTION VARCHAR2(40 BYTE) NOT NULL, > ITEM_QTA NUMBER(16,4), > I...

Re: Update One Table from Another Table Using SQL 131790
Kevin, Doe the thread at the following link provide what you need?: http://www.tek-tips.com/viewthread.cfm?qid=1078242&page=1 HTH, Art ------- On Tue, 24 Nov 2009 14:45:17 -0600, Kevin Myers <kmyers1@CLEARWIRE.NET> wrote: >Using SQL in SAS 8.2, I would like to update the contents of specific rows in one table using rows with matching composite key column values from another table. In other SQL dialects, this could be accomplished using code similar to the following: > >/* syntax 1 (Microsoft SQL Server) */ >update table1 >set table1.column1=table2...

TABLES TABLES TABLES
How would you best describe to a retiscent SAS student that the concept of TABLES isn't limited to SQL? This came up during a discussion on table lookup methods - and lookup tables in particular (I mean, lookup tables pre- date SQL). Even SAS data sets are referred to as tables. This made the student apoplectic. Words of wisdom most welcome!!! Thanks, Howard sasbum@AOL.COM wrote: >How would you best describe to a retiscent SAS student that the concept of >TABLES isn't limited to SQL? This came up during a discussion on table >lookup methods - and lookup tables in partic...

Cannot open same table using Access 2.0 and Access 2003 in the same time
I'm unable to to open a linked table T that is stored in an Access 2.0 MDB database file (shared/back-end,) using both Access 2.0 and Access 2003, in the same time. Here is the scenario: 1- Start Access 2.0 and open the linked table T. 2- Start Access 2003 then try to open the same linked table T. 3- You may get the error "Could not use <.mdb file name>; file already in use." It seems Access 2.0 "Locks" the table for exclusive use, but how to explain that another instance of Access 2.0 still CAN open the same table in the same time! Please note: Access 2.0 and...

Re: Update One Table from Another Table Using SQL 335552
Art - The problem with the technique in the link that you referenced is that it requires a separate update statement to be coded for every column that is to be updated. While the example that I provided only includes two columns to be updated, the real tables that I am working with include a much larger number of columns that need to be updated from the second table. Although I could write a macro to generate the necessary update statements, executing a separate update statement for each individual column would be terribly slow. Any other ideas? Thanks, Kevin M. ----- Orig...

To retrive a table from one database to and another database(same table)
hi i have emp table in one database , how to retrieve same table another database through query analizer give code ,,, Hi, use the three part notation: SELECT * FROM database.owner.Objectname (replace owner by schema for SQL2k5) HTH, Jens Suessmeyer. --- http://www.sqlserver2005.de --- Hi surya While the first database is "Current" as would result from: Use FirstDatabaseName select * from OtherDatabaseName..Emp -- -Dick Christoph "surya" <suryaitha@gmail.com> wrote in message news:1142588469.061525.268810@u72g2000cwu.googlegr...

Web resources about - Update table using data from another table - MS Access - comp.databases.ms-access

Ted Cruz holds Flint water crisis money hostage
Stage fright is a real affliction that affects us all in different ways. For Ted Cruz, on the eve of a presidential debate in his home state ...

Justin Bieber’s Ex-Girlfriend Selena Gomez ‘Likes’ An Old Video Of Them On Instagram
Justin Bieber and Selena Gomez are back in the news after Gomez “liked” an old video of the two of them on Instagram. Could Gomez be missing ...

Obama Nominates Silicon Valley Judge to Appellate Court
President Barack Obama has nominated a federal judge who has presided over patent feuds between Apple and Samsung to the nation's largest federal ...

‘Penny Dreadful’ Season 3 Trailer: How Much Darker Can This Show Get?
“Embrace your dark side,” the Penny Dreadful season three trailer exclaims. Not a bad tagline for a show about battling internal and external ...

U.S., backed by China, proposes tough North Korea sanctions at U.N.
By Louis Charbonneau and Michelle Nichols UNITED NATIONS (Reuters) - The United States presented a draft U.N. Security Council resolution on ...

Oscars: Ranking the Best Picture Nominees
Our critics break down all eight best picture nominees, and agree that one towers above the rest.

Facebook’s new ‘Canvas’ ads go fullscreen on mobile
Facebook has opened up a new option to advertisers: fullscreen advertisements. The ads are part of its "Canvas" offering, which is limited to ...

Nissan pulls the Leaf's phone app after security vulnerabilities come to light
The NissanConnect hack, which allows an individual to download and manipulate settings if they have a Leaf's VIN number, is not the most serious ...

Univ. of Missouri Fires Instructor After Student Run-Ins
The University of Missouri has fired an assistant professor who was suspended after run-ins with student journalists during protests last year ...

Kim Kardashian And Kanye West Are Not Fighting And Not Getting A Divorce
Despite everybody spreading rumors that Kanye West and Kim Kardashian’s marriage is dissolving, Gossip Cop is reporting that these rumors just ...

Resources last updated: 2/26/2016 1:21:05 AM