By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,584 Members | 3,458 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,584 IT Pros & Developers. It's quick & easy.

syntax error for Update Query

P: n/a

I have this update query that I am trying to run. I know the syntax is
messed up but do not know how to correct it.

Select 'UPDATE', Transactions,'Set [balance] =[Daily Balance] where [Id]=
[Idalias]' From "Get Daily Balances"

Transactions = name of the table I want to update
balance = name of the field i want to update

daily balance= name of the query result that I want to move to the table
(this is the result of a Dsum)

ID=primary key in the table
IDalias = the query resulting copy of the ID number

Get daily balances = the name of the query that I need to update from

Thanks in advance for the help on writng the query correctly

Mark
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200507/1
Nov 13 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
Mark Carlyle via AccessMonster.com wrote:
I have this update query that I am trying to run. I know the syntax is
messed up but do not know how to correct it.

Select 'UPDATE', Transactions,'Set [balance] =[Daily Balance] where [Id]=
[Idalias]' From "Get Daily Balances"

Transactions = name of the table I want to update
balance = name of the field i want to update

daily balance= name of the query result that I want to move to the table
(this is the result of a Dsum)

ID=primary key in the table
IDalias = the query resulting copy of the ID number

Get daily balances = the name of the query that I need to update from

Thanks in advance for the help on writng the query correctly

Mark

Can you use Access to create it? Open up the query designer
(Query/New/Design) and select/add table/query Transactions and "Get
Daily Balances". If needed, create a relationship line between the ID
fields. Now, drag the Balance field from Transactions. From the menu
select Query/Update to change it to an update query. In the Update row
for Balance, enter [Get Daily Balances]![Daily Balance]. Save and run.

You should end up with a query similar to this.

UPDATE Transactions INNER JOIN [Get Daily Balances] ON Transactions.ID =
[Get Daily Balances].IDAlias SET Transactions.Balance = [Get Daily
Balances]![Daily Balance];

Just curious. Why do people put spaces into table and query names? To
create more work for themselves?
Nov 13 '05 #2

P: n/a
Mark Carlyle via AccessMonster.com wrote:
I have this update query that I am trying to run. I know the syntax is
messed up but do not know how to correct it.

Select 'UPDATE', Transactions,'Set [balance] =[Daily Balance] where [Id]=
[Idalias]' From "Get Daily Balances"

Transactions = name of the table I want to update
balance = name of the field i want to update

daily balance= name of the query result that I want to move to the table
(this is the result of a Dsum)

ID=primary key in the table
IDalias = the query resulting copy of the ID number

Get daily balances = the name of the query that I need to update from

Thanks in advance for the help on writng the query correctly

Mark

Can you use Access to create it? Open up the query designer
(Query/New/Design) and select/add table/query Transactions and "Get
Daily Balances". If needed, create a relationship line between the ID
fields. Now, drag the Balance field from Transactions. From the menu
select Query/Update to change it to an update query. In the Update row
for Balance, enter [Get Daily Balances]![Daily Balance]. Save and run.

You should end up with a query similar to this.

UPDATE Transactions INNER JOIN [Get Daily Balances] ON Transactions.ID =
[Get Daily Balances].IDAlias SET Transactions.Balance = [Get Daily
Balances]![Daily Balance];

Just curious. Why do people put spaces into table and query names? To
create more work for themselves?
Nov 13 '05 #3

P: n/a

Ok I get an error message that says "Operation must contain an updateable
Query"

Transactions is a table... Get Daily Balances is a Query of Transactions.

Salad wrote:
I have this update query that I am trying to run. I know the syntax is
messed up but do not know how to correct it.

[quoted text clipped - 16 lines]

Mark


Can you use Access to create it? Open up the query designer
(Query/New/Design) and select/add table/query Transactions and "Get
Daily Balances". If needed, create a relationship line between the ID
fields. Now, drag the Balance field from Transactions. From the menu
select Query/Update to change it to an update query. In the Update row
for Balance, enter [Get Daily Balances]![Daily Balance]. Save and run.

You should end up with a query similar to this.

UPDATE Transactions INNER JOIN [Get Daily Balances] ON Transactions.ID =
[Get Daily Balances].IDAlias SET Transactions.Balance = [Get Daily
Balances]![Daily Balance];

Just curious. Why do people put spaces into table and query names? To
create more work for themselves?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200507/1
Nov 13 '05 #4

P: n/a
Mark Carlyle via AccessMonster.com wrote:
Ok I get an error message that says "Operation must contain an updateable
Query"
Most likely [Get Daily Balances] uses a Totals query in it or else is a
Totals query. If so, you'll need to adjust your method of updating and
not use a Total query.
Transactions is a table... Get Daily Balances is a Query of Transactions.

Salad wrote:
I have this update query that I am trying to run. I know the syntax is
messed up but do not know how to correct it.


[quoted text clipped - 16 lines]
Mark


Can you use Access to create it? Open up the query designer
(Query/New/Design) and select/add table/query Transactions and "Get
Daily Balances". If needed, create a relationship line between the ID
fields. Now, drag the Balance field from Transactions. From the menu
select Query/Update to change it to an update query. In the Update row
for Balance, enter [Get Daily Balances]![Daily Balance]. Save and run.

You should end up with a query similar to this.

UPDATE Transactions INNER JOIN [Get Daily Balances] ON Transactions.ID =
[Get Daily Balances].IDAlias SET Transactions.Balance = [Get Daily
Balances]![Daily Balance];

Just curious. Why do people put spaces into table and query names? To
create more work for themselves?


Nov 13 '05 #5

P: n/a

[Get Daily Balances] is a Dsum Query of a Totals Query.

What I need to do is update a table with the results of the Dsum Query.

Am I going about this all wrong?

Salad wrote:
Ok I get an error message that says "Operation must contain an updateable
Query"


Most likely [Get Daily Balances] uses a Totals query in it or else is a
Totals query. If so, you'll need to adjust your method of updating and
not use a Total query.
Transactions is a table... Get Daily Balances is a Query of Transactions.

[quoted text clipped - 20 lines]
Just curious. Why do people put spaces into table and query names? To
create more work for themselves?

--
Message posted via http://www.accessmonster.com
Nov 13 '05 #6

P: n/a
Mark Carlyle via AccessMonster.com wrote:
[Get Daily Balances] is a Dsum Query of a Totals Query.

What I need to do is update a table with the results of the Dsum Query.

Am I going about this all wrong?
Yes, if you are attempting to update from a Totals query.

You need to change your methodology of getting the balance.

Salad wrote:
Ok I get an error message that says "Operation must contain an updateable
Query"


Most likely [Get Daily Balances] uses a Totals query in it or else is a
Totals query. If so, you'll need to adjust your method of updating and
not use a Total query.

Transactions is a table... Get Daily Balances is a Query of Transactions.


[quoted text clipped - 20 lines]
Just curious. Why do people put spaces into table and query names? To
create more work for themselves?


Nov 13 '05 #7

P: n/a

Well, I figured out finally that there is no need to store the balance... why
not just create a query that gives the balance along with the other info I
wish to view.

Thanks a ton for your help!

Salad wrote:
[Get Daily Balances] is a Dsum Query of a Totals Query.

What I need to do is update a table with the results of the Dsum Query.

Am I going about this all wrong?


Yes, if you are attempting to update from a Totals query.

You need to change your methodology of getting the balance.
Ok I get an error message that says "Operation must contain an updateable
Query"[quoted text clipped - 9 lines]>Just curious. Why do people put spaces into table and query names? To
>create more work for themselves?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200507/1
Nov 13 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.