Convert this Oracle Query to Access Query ?

  • Thread starter Thread starter Luqman
  • Start date Start date
L

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
 
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
Click to expand...

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]
 
Dear John,

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

Best Regards,

Luqman

John Vinson said:
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
Click to expand...

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]
Click to expand...
 
Back
Top