0

I'm an SQL newbie using VB6/Access 2000 and am trying to get a query which puts the sum of values from a table into another table. VB6 does the job, but it's so slow. I searched and tried in Access many times, just got lost with keywords IN, ON, (INNER) JOIN, each time getting a different error.

The core code should be as follows:

update t1
set t1.value = sum(t2.value)
where
val(t2.code)>89
and
t2.date=t1.date
  • t1.date is a date, no duplicates
  • t2.code is a variable string like '0081', '090'
  • values are single precision

After further searching i found a similar question here ( http://goo.gl/uqlw0U ) and tried that:

UPDATE t1
SET t1.value = 
    (
SELECT 
    SUM(t2.value) 
FROM spese
    WHERE
     t1.date=t2.date
AND
    val(t2.code)>89
    )

but Access just says "updatable query needed" -- what does that mean?

Andrea
  • 1
  • 4
  • Thanks for editing, I'd written the example code exactly like that but it turned inline when i posted. – Andrea Nov 15 '15 at 11:08
  • In the absence of a "procedural" stored procedure facility I suspect you're screwed since what you want probably can't be expressed as SQL set-based operations. So you can do this in client code iterating over the updated table with resutls from an aggregate query or you might do this with a JOIN and a make-table query that builds a new table (then DROP the old table, rename the new table via ADOX, create any required indexes). – Bob77 Nov 16 '15 at 17:45

1 Answers1

0

Try this:

UPDATE t1
 SET t1.value = SUM(t2.value)
FROM t1, t2
WHERE
 val(t2.code)>89
AND
 t2.date=t1.date
Vlado
  • 753
  • 5
  • 15