syntax error for Update Query

[Mark Carlyle via AccessMonster.com]

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

[Salad]

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?

[Salad]

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?

[Mark Carlyle via AccessMonster.com]

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

[Salad]

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?

[Mark Carlyle via AccessMonster.com]

[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

[Salad]

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?

[Mark Carlyle via AccessMonster.com]

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