Update Query won't accept "Update To" data

[Guest]

I have an update query which I'm using to write data from another Access
query into tables from an external non-Access database (that I've linked via
an ODBC driver).

When I try to run the query it asks me to manually input the parameter
values for every field in the query. It doesn't seem to be accepting the
output of the other Access query as the data to be written to the external
tables.

An example of the SQL code of the Update Query is below. Can anyone see any
problems with it?

Thanks,
FJ

UPDATE ExpenseLine SET ExpenseLine.Date = [Advances - Check
Table]!ChequeDate, ExpenseLine.Name = [Advances - Check Table]!CompanyName,
ExpenseLine.Amount = [Advances - Check Table]!SumOfAdvanceAmount,
ExpenseLine.[Memo] = [Advances - Check Table]!Notes, ExpenseLine.Ref =
[Advances - Check Table]!ChequeNumber
WHERE (((ExpenseLine.Account)="Bank Chequing") AND ((ExpenseLine.Save)=0));

 

[John Spencer]

Well, if you are updating values on existing records then you need to
get the other table (or query) into this one. If you are inserting new
records then you need a different type of query.

How is Advances - Check Table related to ExpenseLine table?

UPDATE ExpenseLine INNER JOIN [Advances - Check Table]
ON xxx = xxx
SET ...
WHERE ...

As a guess that is going to fail anyway since Access won't permit the
use of aggregate queries in an update and SumOfAdvanceAmount is probably
a field from an aggregate (totals) query.

If you want to an insert query then it would be something like the
followiing

INSERT INTO ExpenseLine ([Date],[Name],Amount, [Memo], Ref)
SELECT ChequeDate, CompanyName, SumOfAdvanceAmount, Notes, ChequeNumber
FROM [Advances - Check Table]

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Guest]

Thanks for the speedy reply!

Am I correct thatI can construct the equivalent to your INSERT code by
creating an APPEND query?

Thanks,
FJ

Well, if you are updating values on existing records then you need to
get the other table (or query) into this one. If you are inserting new
records then you need a different type of query.

How is Advances - Check Table related to ExpenseLine table?

UPDATE ExpenseLine INNER JOIN [Advances - Check Table]
ON xxx = xxx
SET ...
WHERE ...

As a guess that is going to fail anyway since Access won't permit the
use of aggregate queries in an update and SumOfAdvanceAmount is probably
a field from an aggregate (totals) query.

If you want to an insert query then it would be something like the
followiing

INSERT INTO ExpenseLine ([Date],[Name],Amount, [Memo], Ref)
SELECT ChequeDate, CompanyName, SumOfAdvanceAmount, Notes, ChequeNumber
FROM [Advances - Check Table]

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I have an update query which I'm using to write data from another Access
query into tables from an external non-Access database (that I've linked via
an ODBC driver).

When I try to run the query it asks me to manually input the parameter
values for every field in the query. It doesn't seem to be accepting the
output of the other Access query as the data to be written to the external
tables.

An example of the SQL code of the Update Query is below. Can anyone see any
problems with it?

Thanks,
FJ

UPDATE ExpenseLine SET ExpenseLine.Date = [Advances - Check
Table]!ChequeDate, ExpenseLine.Name = [Advances - Check Table]!CompanyName,
ExpenseLine.Amount = [Advances - Check Table]!SumOfAdvanceAmount,
ExpenseLine.[Memo] = [Advances - Check Table]!Notes, ExpenseLine.Ref =
[Advances - Check Table]!ChequeNumber
WHERE (((ExpenseLine.Account)="Bank Chequing") AND ((ExpenseLine.Save)=0));

 

[John Spencer]

My INSERT code is an append query. So yes you can create the code by
creating an Append query using the QBE query grid. Once you have done so,
you can select View: SQL from the menu and see what the graphical user
interface tool (the query grid) has constructed as the SQL.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Thanks for the speedy reply!

Am I correct thatI can construct the equivalent to your INSERT code by
creating an APPEND query?

Thanks,
FJ

Well, if you are updating values on existing records then you need to
get the other table (or query) into this one. If you are inserting new
records then you need a different type of query.

How is Advances - Check Table related to ExpenseLine table?

UPDATE ExpenseLine INNER JOIN [Advances - Check Table]
ON xxx = xxx
SET ...
WHERE ...

As a guess that is going to fail anyway since Access won't permit the
use of aggregate queries in an update and SumOfAdvanceAmount is probably
a field from an aggregate (totals) query.

If you want to an insert query then it would be something like the
followiing

INSERT INTO ExpenseLine ([Date],[Name],Amount, [Memo], Ref)
SELECT ChequeDate, CompanyName, SumOfAdvanceAmount, Notes, ChequeNumber
FROM [Advances - Check Table]

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I have an update query which I'm using to write data from another
Access
query into tables from an external non-Access database (that I've
linked via
an ODBC driver).

When I try to run the query it asks me to manually input the parameter
values for every field in the query. It doesn't seem to be accepting
the
output of the other Access query as the data to be written to the
external
tables.

An example of the SQL code of the Update Query is below. Can anyone see
any
problems with it?

Thanks,
FJ

UPDATE ExpenseLine SET ExpenseLine.Date = [Advances - Check
Table]!ChequeDate, ExpenseLine.Name = [Advances - Check
Table]!CompanyName,
ExpenseLine.Amount = [Advances - Check Table]!SumOfAdvanceAmount,
ExpenseLine.[Memo] = [Advances - Check Table]!Notes, ExpenseLine.Ref =
[Advances - Check Table]!ChequeNumber
WHERE (((ExpenseLine.Account)="Bank Chequing") AND
((ExpenseLine.Save)=0));