access equivalent of oracle query?

[Richard Bond]

The following query works in ORACLE but when run in ACCESS 2000 returns
"Operation must use an updateable query"

update tab x
set expshare = (select a.exp / b.exp
from tab a, tab b
where b.product = 1
and a.time = b.time
and a.Outlet = b.Outlet)

The table is below

http://www.jburden.com/query.htm (in case the formatting below doesn't work
out)

Product Time Outlet Exp ExpShare
1 1 1 500 1
2 1 1 250 0.5
3 1 1 250 0.5
1 2 1 400 1
2 2 1 200 0.5
3 2 1 200 0.5
1 3 1 400 1
2 3 1 250 0.625
3 3 1 150 0.375
1 4 1 400 1
2 4 1 300 0.75
3 4 1 100 0.25
1 1 2 50 1
2 1 2 25 0.5
3 1 2 25 0.5
1 2 2 40 1
2 2 2 20 0.5
3 2 2 20 0.5
1 3 2 40 1
2 3 2 25 0.625
3 3 2 15 0.375
1 4 2 40 1
2 4 2 30 0.75
3 4 2 10 0.25


I have given an example of what the numeric results should be in the
ExpShare column. (Product 1 is always the denominator, "within Time and
Outlet")

the select statement below returns the ExpShare column in a recordset in
access - its just I can't get it back into the original table without
creating a temporary table.

(select a.exp / b.exp
from tab a, tab b
where b.product = 1
and a.time = b.time
and a.Outlet = b.Outlet)

It would really help if someone could do this all in one move.

regards,

Richard

 

[John Viescas]

Given the sample data, the following should produce the same result:

UPDATE tab
SET expshare = 1
WHERE product = 1

Your subquery is joining the table to itself on time and outlet for product
= 1. As long as each combination of time and outlet are unique for this
product, you're simply joining each row to itself, and of course dividing
exp by exp will always yield 1 (unless exp is 0 - which will get you an
error). If the combination of time and outlet are NOT unique, you're
attempting an invalid update because the subquery returns multiple rows.
What is it you're really trying to do?

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

 

[Brian Camire]

I think the subquery in your UPDATE statement will return more than one
record. I would have thought you would need additional conditions in the
WHERE clause like...

AND
a.product = x.product
AND
a.time = x.time
AND
a.outlet = b.outlet

So, if your existing query works in Oracle, I wonder if it's by accident...

In any case, in Access, you might try a query whose SQL looks something like
this:

UPDATE
tab AS a,
tab AS b
SET
a.expshare = a.exp/b.exp
WHERE
b.product=1
AND
a.time=b.time
AND
a.outlet=b.outlet

 

[Richard Bond]

Thanks Brian,

That's perfect - I didn't realise you could put two tables after the update
statement.

Rich

 

[Brian Camire]

Glad to hear it worked.

If it still matters, I noticed that the last part of the change I suggested
to your original WHERE clause should have been

AND
a.outlet = x.outlet

instead of

AND
a.outlet = b.outlet