Convert this Oracle Query to Access Query ?

[Luqman]

Can someone please specify how to create similar query in Access 2003.

Update myTable1 A
Set Rate=
(Select sum(Amount)/sum(Quantity) from myTable2 where itemcode=A.itemcode
and myDate<=A.myDate)

Best Regards,

Luqman

 

[John Vinson]

Can someone please specify how to create similar query in Access 2003.

Update myTable1 A
Set Rate=
(Select sum(Amount)/sum(Quantity) from myTable2 where itemcode=A.itemcode
and myDate<=A.myDate)

Best Regards,

Luqman

Access (Jet rather) prohibits any query containing any Totals
operation from being updateable, even when (as in this case) it
logically should be.

You can use the DSum() VBA function to get around this:

UPDATE myTable1 A
SET Rate = DSum("[Amount]", "[myTable2]", "[itemcode] = " &
[A].[itemcode] & " AND [myDate] < #" & [A.myDate] & "#")
/ DSum("[Quantity]", "[myTable2]", "[itemcode] = " & [A].[itemcode] &
" AND [myDate] < #" & [A.myDate] & "#")

Note the use of # as a date/time delimiter.

John W. Vinson[MVP]

 

[Luqman]

Dear John,

Thanks v. much for your assistance, I really appreciate.

Best Regards,

Luqman

Can someone please specify how to create similar query in Access 2003.

Update myTable1 A
Set Rate=
(Select sum(Amount)/sum(Quantity) from myTable2 where itemcode=A.itemcode
and myDate<=A.myDate)

Best Regards,

Luqman

Access (Jet rather) prohibits any query containing any Totals
operation from being updateable, even when (as in this case) it
logically should be.

You can use the DSum() VBA function to get around this:

UPDATE myTable1 A
SET Rate = DSum("[Amount]", "[myTable2]", "[itemcode] = " &
[A].[itemcode] & " AND [myDate] < #" & [A.myDate] & "#")
/ DSum("[Quantity]", "[myTable2]", "[itemcode] = " & [A].[itemcode] &
" AND [myDate] < #" & [A.myDate] & "#")

Note the use of # as a date/time delimiter.

John W. Vinson[MVP]