Update Query Syntax

[David]

Hello,

I'd like some help on the SQL Syntax or preferably, the method to use the
QBE window to do the following:

1. Use a query to select/sum data to then update data in another table.

The syntax of the SQL statement of what I have thus far is listed below.

I am trying to join on Loan Number and Quarter Ending Date.
I am trying to update Coll_Value1 with Coll_Total.

UPDATE tbl_LLMasterData_Collat_Lien SET
tbl_LLMasterData_Collat_Lien.Coll_Value1 =
[qry_LLCrossCollat_SumByLoan2]![CollTotal]
WHERE (((tbl_LLMasterData_Collat_Lien.Loan_Number) In (Select
X_Collateral_Master_LoanNumber From qry_LLCrossCollat_SumByLoan2)) AND
((tbl_LLMasterData_Collat_Lien.QuarterEndingDate) In (Select
QuarterEndingDate From qry_LLCrossCollat_SumByLoan2)));

Thanks.

 

[George Nicholson]

UPDATE tbl_LLMasterData_Collat_Lien
INNER JOIN tbl_LLMasterData_Collat_Lien
ON qry_LLCrossCollat_SumByLoan2.X_Collateral_Master_LoanNumber =
tbl_LLMasterData_Collat_Lien.Loan_Number AND
qry_LLCrossCollat_SumByLoan2.QuarterEndingDate =
tbl_LLMasterData_Collat_Lien.QuarterEndingDate
SET tbl_LLMasterData_Collat_Lien.Coll_Value1 =
[qry_LLCrossCollat_SumByLoan2]![CollTotal]

HTH

 

[David]

George,

Thanks for the reply.

However, I get an error: "Syntax Error in JOIN Operation", and the following
is highlighted "qry_LLCrossCollat_SumByLoan2" on the line "ON
qry_LLCrossCollat_SumByLoan2.X_Collateral_Master_LoanNumber =
tbl_LLMasterData_Collat_Lien.Loan_Number AND "

Any ideas?
--
David

UPDATE tbl_LLMasterData_Collat_Lien
INNER JOIN tbl_LLMasterData_Collat_Lien
ON qry_LLCrossCollat_SumByLoan2.X_Collateral_Master_LoanNumber =
tbl_LLMasterData_Collat_Lien.Loan_Number AND
qry_LLCrossCollat_SumByLoan2.QuarterEndingDate =
tbl_LLMasterData_Collat_Lien.QuarterEndingDate
SET tbl_LLMasterData_Collat_Lien.Coll_Value1 =
[qry_LLCrossCollat_SumByLoan2]![CollTotal]

HTH

Hello,

I'd like some help on the SQL Syntax or preferably, the method to use the
QBE window to do the following:

1. Use a query to select/sum data to then update data in another table.

The syntax of the SQL statement of what I have thus far is listed below.

I am trying to join on Loan Number and Quarter Ending Date.
I am trying to update Coll_Value1 with Coll_Total.

UPDATE tbl_LLMasterData_Collat_Lien SET
tbl_LLMasterData_Collat_Lien.Coll_Value1 =
[qry_LLCrossCollat_SumByLoan2]![CollTotal]
WHERE (((tbl_LLMasterData_Collat_Lien.Loan_Number) In (Select
X_Collateral_Master_LoanNumber From qry_LLCrossCollat_SumByLoan2)) AND
((tbl_LLMasterData_Collat_Lien.QuarterEndingDate) In (Select
QuarterEndingDate From qry_LLCrossCollat_SumByLoan2)));

Thanks.

 

[David]

Got the syntax working (see snippet below-I believe the parens where
needed?).

Now get the message when running: "Operation must use an updateable query".

Ideas?

UPDATE tbl_LLMasterData_Collat_Lien INNER JOIN qry_LLCrossCollat_SumByLoan1
ON (tbl_LLMasterData_Collat_Lien.QuarterEndingDate =
qry_LLCrossCollat_SumByLoan1.QuarterEndingDate) AND
(tbl_LLMasterData_Collat_Lien.Loan_Number =
qry_LLCrossCollat_SumByLoan1.X_Collateral_Master_LoanNumber) SET
tbl_LLMasterData_Collat_Lien.Coll_Value1 =
qry_LLCrossCollat_SumByLoan1!SumOfColl_Value1;
--
David

UPDATE tbl_LLMasterData_Collat_Lien
INNER JOIN tbl_LLMasterData_Collat_Lien
ON qry_LLCrossCollat_SumByLoan2.X_Collateral_Master_LoanNumber =
tbl_LLMasterData_Collat_Lien.Loan_Number AND
qry_LLCrossCollat_SumByLoan2.QuarterEndingDate =
tbl_LLMasterData_Collat_Lien.QuarterEndingDate
SET tbl_LLMasterData_Collat_Lien.Coll_Value1 =
[qry_LLCrossCollat_SumByLoan2]![CollTotal]

HTH

Hello,

I'd like some help on the SQL Syntax or preferably, the method to use the
QBE window to do the following:

1. Use a query to select/sum data to then update data in another table.

The syntax of the SQL statement of what I have thus far is listed below.

I am trying to join on Loan Number and Quarter Ending Date.
I am trying to update Coll_Value1 with Coll_Total.

UPDATE tbl_LLMasterData_Collat_Lien SET
tbl_LLMasterData_Collat_Lien.Coll_Value1 =
[qry_LLCrossCollat_SumByLoan2]![CollTotal]
WHERE (((tbl_LLMasterData_Collat_Lien.Loan_Number) In (Select
X_Collateral_Master_LoanNumber From qry_LLCrossCollat_SumByLoan2)) AND
((tbl_LLMasterData_Collat_Lien.QuarterEndingDate) In (Select
QuarterEndingDate From qry_LLCrossCollat_SumByLoan2)));

Thanks.

 

[Jamie Collins]

Now get the message when running: "Operation must use an updateable query".

Ideas?

UPDATE tbl_LLMasterData_Collat_Lien INNER JOIN qry_LLCrossCollat_SumByLoan1
ON (tbl_LLMasterData_Collat_Lien.QuarterEndingDate =
qry_LLCrossCollat_SumByLoan1.QuarterEndingDate) AND
(tbl_LLMasterData_Collat_Lien.Loan_Number =
qry_LLCrossCollat_SumByLoan1.X_Collateral_Master_LoanNumber) SET
tbl_LLMasterData_Collat_Lien.Coll_Value1 =
qry_LLCrossCollat_SumByLoan1!SumOfColl_Value1;

Based on your column names, I'm thinking this might be the problem:

ACC: Update Query Based on Totals Query Fails
http://support.microsoft.com/kb/116142

Jamie.

--

 

[David]

That's it Jamie. Thanks.

I guess that is a 'feature' of Access... not a 'bug'!