Access2000: Update query

  • Thread starter Thread starter Arvi Laanemets
  • Start date Start date
A

Arvi Laanemets

Hi

I have a table p.e. MyTable with columns ID, F01, F02, ..., F12

I also have a view/query p.e. MyQuery, also with columns ID, F01, F02, ...,
F12,
where MyQuery.ID is read from MyTable (i.e. both table and query have same
set of ID-values), and rest of fields are calculated based on data from
other tables in database.

Now I need to update MyTable with values for columns F01 - F12 from MyQuery
for all ID's. Somehow I don't get it working. What will be right syntax?


Thanks in advance!
Arvi Laanemets
 
Normally, you use something like

UPDATE MyTable INNER JOIN MyQuery
ON MyTable.ID = MyQuery.ID
SET MyTable.F01 = [MyQuery].[F01]
, MyTable.F02 = [MyQuery].[F02]
, MyTable.F03 = [MyQuery].[F03]

That will work in most cases, however if MyQuery uses sum, avg, min, max, etc
functions this will fail with an error about you must use an updateable query.

I suggest you post the SQL of "MyQuery" so that it can be determined if you
can use the query directly in an update query. If not, perhaps we can suggest
alternatives. You might tell us how many records (tens, thousands, millions)
are involved as that can affect the recommended solution.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
What did you try and what were your results? I expect this isn't possibly
without replacing your query with a table that has a primary key of the ID
field.
 
Hi


John Spencer said:
Normally, you use something like

UPDATE MyTable INNER JOIN MyQuery
ON MyTable.ID = MyQuery.ID
SET MyTable.F01 = [MyQuery].[F01]
, MyTable.F02 = [MyQuery].[F02]
, MyTable.F03 = [MyQuery].[F03]

That will work in most cases, however if MyQuery uses sum, avg, min, max,
etc functions this will fail with an error about you must use an
updateable query.
Click to expand...

It explains it all! MyQuery is an aggregate query

As it will be too much work to edit all queries to look more simple, I'll
copy real stuff here

I have tables

tblKoristaja: TabN (Primary, Text), ..., H01, H02, ..., H012, ... . It
contains a list of people/employees, and there are 12 columns (H01-H12)
where current estimated working hours for 12 months are stored - those same
columns I want to update.
tblTegevus: TegevusID (Primary, Autonumeric), ..., M01, M02, ..., M012. It
contains a list of possible actions for people, and how many times in month
those actions are performed for 12 months.
tblPindTegevus: PindTegevusID (Primary, Autonumeric), PindID (Long Integer),
TegevusID (Long Integer), Tunnid, Minutid. It links actions (TegevusID) with
Objects (PindID), and stores estimated time - hours (Tunnid) and minutes
(Minutid) - reserved for those actions.
tblPindTegevusKoristaja: ID (Primary, Autonumeric), PindID (Long Integer),
TegevusID (Long Integer), TabN (Text), Alates (Date). It links people (Tabn)
with actions on objects (PindID, TegevusID), starting from certain date
(Alates) - i.e. the table contains the history of attached actions for
people. (When some action remains unattached from some time moment, there
will be according record in tblPindTegevusKoristaja with this date, where
TabN=Null)

Now I need to run a procedure which recalculates all H01-H12 in tblKoristaja
with currently estimated monthly hours.

Those hours I can calculate with query qCurrKoristajaTunnid (this is MyQuery
in my earlier post):
SELECT tblKoristaja.TabN, Sum(qCurrKoristajaTegevused.Tunnid*tblTegevus.M01)
AS H01, Sum(qCurrKoristajaTegevused.Tunnid*tblTegevus.M02) AS H02,
Sum(qCurrKoristajaTegevused.Tunnid*tblTegevus.M03) AS H03,
Sum(qCurrKoristajaTegevused.Tunnid*tblTegevus.M04) AS H04,
Sum(qCurrKoristajaTegevused.Tunnid*tblTegevus.M05) AS H05,
Sum(qCurrKoristajaTegevused.Tunnid*tblTegevus.M06) AS H06,
Sum(qCurrKoristajaTegevused.Tunnid*tblTegevus.M07) AS H07,
Sum(qCurrKoristajaTegevused.Tunnid*tblTegevus.M08) AS H08,
Sum(qCurrKoristajaTegevused.Tunnid*tblTegevus.M09) AS H09,
Sum(qCurrKoristajaTegevused.Tunnid*tblTegevus.M10) AS H10,
Sum(qCurrKoristajaTegevused.Tunnid*tblTegevus.M11) AS H11,
Sum(qCurrKoristajaTegevused.Tunnid*tblTegevus.M12) AS H12
FROM (tblKoristaja LEFT JOIN qCurrKoristajaTegevused ON tblKoristaja.TabN =
qCurrKoristajaTegevused.TabN) LEFT JOIN tblTegevus ON
qCurrKoristajaTegevused.TegevusID = tblTegevus.TegevusID
GROUP BY tblKoristaja.TabN;

,where qCurrKoristajaTegevused gives the list of actions on different
objects for every employee, and calculates reserved time fior every action
in hours:
SELECT tblPindTegevusKoristaja.TabN, qLastPindTegevusKoristaja.PindID,
qLastPindTegevusKoristaja.TegevusID,
[tblPindTegevus].[Tunnid]+[tblPindTegevus].[Minutid]/60 AS Tunnid
FROM (tblPindTegevusKoristaja RIGHT JOIN qLastPindTegevusKoristaja ON
(tblPindTegevusKoristaja.PindID = qLastPindTegevusKoristaja.PindID) AND
(tblPindTegevusKoristaja.TegevusID = qLastPindTegevusKoristaja.TegevusID))
LEFT JOIN tblPindTegevus ON (tblPindTegevusKoristaja.TegevusID =
tblPindTegevus.TegevusID) AND (tblPindTegevusKoristaja.PindID =
tblPindTegevus.PindID)
WHERE (((tblPindTegevusKoristaja.TabN) Is Not Null) AND
((tblPindTegevusKoristaja.Alates)=[qLastPindTegevusKoristaja].[Last]));

, where qLastPindTegevusKoristaja gives the last time for every action on
every object, some employee was attached to it, i.e. current people attached
with every action on every object:
SELECT tblPindTegevusKoristaja.PindID, tblPindTegevusKoristaja.TegevusID,
Max(tblPindTegevusKoristaja.Alates) AS [Last]
FROM tblPindTegevusKoristaja
GROUP BY tblPindTegevusKoristaja.PindID, tblPindTegevusKoristaja.TegevusID;

Of course I can process the table tblKoristaja row-by-row, and replace
estimated hours with ones read from query, but I was searching for a way to
do it with with one go. It looks like there is no easy way to do it. ?!?

Thanks anyway!
Arvi Laanemets
 
I would probably change the table structure to normalize it. In the words of
fellow MVP Jeff Boyce "you have committed spreadsheet". The month records
should each create a new record in a related table.

Having said that, I would expect you can't run an update query on a join
that isn't a primary and foreign key relationship. You could probably append
from your query to a blank table with the appropriate primary key fields that
allow you to join to your tabel tblPindTegevusKoristaja (I think).

--
Duane Hookom
Microsoft Access MVP


Arvi Laanemets said:
Hi


John Spencer said:
Normally, you use something like

UPDATE MyTable INNER JOIN MyQuery
ON MyTable.ID = MyQuery.ID
SET MyTable.F01 = [MyQuery].[F01]
, MyTable.F02 = [MyQuery].[F02]
, MyTable.F03 = [MyQuery].[F03]

That will work in most cases, however if MyQuery uses sum, avg, min, max,
etc functions this will fail with an error about you must use an
updateable query.
Click to expand...

It explains it all! MyQuery is an aggregate query

As it will be too much work to edit all queries to look more simple, I'll
copy real stuff here

I have tables

tblKoristaja: TabN (Primary, Text), ..., H01, H02, ..., H012, ... . It
contains a list of people/employees, and there are 12 columns (H01-H12)
where current estimated working hours for 12 months are stored - those same
columns I want to update.
tblTegevus: TegevusID (Primary, Autonumeric), ..., M01, M02, ..., M012. It
contains a list of possible actions for people, and how many times in month
those actions are performed for 12 months.
tblPindTegevus: PindTegevusID (Primary, Autonumeric), PindID (Long Integer),
TegevusID (Long Integer), Tunnid, Minutid. It links actions (TegevusID) with
Objects (PindID), and stores estimated time - hours (Tunnid) and minutes
(Minutid) - reserved for those actions.
tblPindTegevusKoristaja: ID (Primary, Autonumeric), PindID (Long Integer),
TegevusID (Long Integer), TabN (Text), Alates (Date). It links people (Tabn)
with actions on objects (PindID, TegevusID), starting from certain date
(Alates) - i.e. the table contains the history of attached actions for
people. (When some action remains unattached from some time moment, there
will be according record in tblPindTegevusKoristaja with this date, where
TabN=Null)

Now I need to run a procedure which recalculates all H01-H12 in tblKoristaja
with currently estimated monthly hours.

Those hours I can calculate with query qCurrKoristajaTunnid (this is MyQuery
in my earlier post):
SELECT tblKoristaja.TabN, Sum(qCurrKoristajaTegevused.Tunnid*tblTegevus.M01)
AS H01, Sum(qCurrKoristajaTegevused.Tunnid*tblTegevus.M02) AS H02,
Sum(qCurrKoristajaTegevused.Tunnid*tblTegevus.M03) AS H03,
Sum(qCurrKoristajaTegevused.Tunnid*tblTegevus.M04) AS H04,
Sum(qCurrKoristajaTegevused.Tunnid*tblTegevus.M05) AS H05,
Sum(qCurrKoristajaTegevused.Tunnid*tblTegevus.M06) AS H06,
Sum(qCurrKoristajaTegevused.Tunnid*tblTegevus.M07) AS H07,
Sum(qCurrKoristajaTegevused.Tunnid*tblTegevus.M08) AS H08,
Sum(qCurrKoristajaTegevused.Tunnid*tblTegevus.M09) AS H09,
Sum(qCurrKoristajaTegevused.Tunnid*tblTegevus.M10) AS H10,
Sum(qCurrKoristajaTegevused.Tunnid*tblTegevus.M11) AS H11,
Sum(qCurrKoristajaTegevused.Tunnid*tblTegevus.M12) AS H12
FROM (tblKoristaja LEFT JOIN qCurrKoristajaTegevused ON tblKoristaja.TabN =
qCurrKoristajaTegevused.TabN) LEFT JOIN tblTegevus ON
qCurrKoristajaTegevused.TegevusID = tblTegevus.TegevusID
GROUP BY tblKoristaja.TabN;

,where qCurrKoristajaTegevused gives the list of actions on different
objects for every employee, and calculates reserved time fior every action
in hours:
SELECT tblPindTegevusKoristaja.TabN, qLastPindTegevusKoristaja.PindID,
qLastPindTegevusKoristaja.TegevusID,
[tblPindTegevus].[Tunnid]+[tblPindTegevus].[Minutid]/60 AS Tunnid
FROM (tblPindTegevusKoristaja RIGHT JOIN qLastPindTegevusKoristaja ON
(tblPindTegevusKoristaja.PindID = qLastPindTegevusKoristaja.PindID) AND
(tblPindTegevusKoristaja.TegevusID = qLastPindTegevusKoristaja.TegevusID))
LEFT JOIN tblPindTegevus ON (tblPindTegevusKoristaja.TegevusID =
tblPindTegevus.TegevusID) AND (tblPindTegevusKoristaja.PindID =
tblPindTegevus.PindID)
WHERE (((tblPindTegevusKoristaja.TabN) Is Not Null) AND
((tblPindTegevusKoristaja.Alates)=[qLastPindTegevusKoristaja].[Last]));

, where qLastPindTegevusKoristaja gives the last time for every action on
every object, some employee was attached to it, i.e. current people attached
with every action on every object:
SELECT tblPindTegevusKoristaja.PindID, tblPindTegevusKoristaja.TegevusID,
Max(tblPindTegevusKoristaja.Alates) AS [Last]
FROM tblPindTegevusKoristaja
GROUP BY tblPindTegevusKoristaja.PindID, tblPindTegevusKoristaja.TegevusID;

Of course I can process the table tblKoristaja row-by-row, and replace
estimated hours with ones read from query, but I was searching for a way to
do it with with one go. It looks like there is no easy way to do it. ?!?

Thanks anyway!
Arvi Laanemets

I suggest you post the SQL of "MyQuery" so that it can be determined if
you can use the query directly in an update query. If not, perhaps we can
suggest alternatives. You might tell us how many records (tens,
thousands, millions) are involved as that can affect the recommended
solution.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Click to expand...


.
Click to expand...
 
STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

I think you could use something like the following.

UPDATE tblKoristaja
SET Ho1 = DLOOKUP("Ho1","qCurrKoristajaTunnid","TabN=" & tblKoristaja.TabN)
, Ho2 = DLOOKUP("Ho2","qCurrKoristajaTunnid","TabN=" & tblKoristaja.TabN)

However, that would end up putting Null into any records where there is not
matching record in the query - which may be what you want

AND it makes little sense to do this. It is almost always better to use a
query to get summary values.

If you really, really need to do this, then faster option would be to use
qCurrKoristajaTunnid to create a table or populate an existing table with the
needed values. Then link that table to the update. Something like the
following after the WorkTable has been populated.

UPDATE tblKoristaja As K INNER JOIN WorkTable as W
ON K.TabN = W.TabN
SET K.H01 = [W].[H01]
, K.H02 = [W].[H02]
, K.H03 = [W].[H03]
, K.H04 = [W].[H04]
, K.H05 = [W].[H05]
, K.H06 = [W].[H06]
, K.H07 = [W].[H07]
, K.H08 = [W].[H08]
, K.H09 = [W].[H09]
, K.H10 = [W].[H10]
, K.H11 = [W].[H11]
, K.H12 = [W].[H12]

You can DELETE the records in the work table (or delete the work table) after
you have populated the data. But again, if you need this data you are
probably better off using the existing query or linking to the work table for
reports etc. If you change one record, delete a record, or add a record in
tblKoristaja you would need to rerun this procedure to have accurate data in
the fields. If you use a query to get and display the data, the data will
automatically be current when you run the query.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Arvi said:
Hi


John Spencer said:
Normally, you use something like

UPDATE MyTable INNER JOIN MyQuery
ON MyTable.ID = MyQuery.ID
SET MyTable.F01 = [MyQuery].[F01]
, MyTable.F02 = [MyQuery].[F02]
, MyTable.F03 = [MyQuery].[F03]

That will work in most cases, however if MyQuery uses sum, avg, min, max,
etc functions this will fail with an error about you must use an
updateable query.
Click to expand...

It explains it all! MyQuery is an aggregate query

As it will be too much work to edit all queries to look more simple, I'll
copy real stuff here

I have tables

tblKoristaja: TabN (Primary, Text), ..., H01, H02, ..., H012, ... . It
contains a list of people/employees, and there are 12 columns (H01-H12)
where current estimated working hours for 12 months are stored - those same
columns I want to update.
tblTegevus: TegevusID (Primary, Autonumeric), ..., M01, M02, ..., M012. It
contains a list of possible actions for people, and how many times in month
those actions are performed for 12 months.
tblPindTegevus: PindTegevusID (Primary, Autonumeric), PindID (Long Integer),
TegevusID (Long Integer), Tunnid, Minutid. It links actions (TegevusID) with
Objects (PindID), and stores estimated time - hours (Tunnid) and minutes
(Minutid) - reserved for those actions.
tblPindTegevusKoristaja: ID (Primary, Autonumeric), PindID (Long Integer),
TegevusID (Long Integer), TabN (Text), Alates (Date). It links people (Tabn)
with actions on objects (PindID, TegevusID), starting from certain date
(Alates) - i.e. the table contains the history of attached actions for
people. (When some action remains unattached from some time moment, there
will be according record in tblPindTegevusKoristaja with this date, where
TabN=Null)

Now I need to run a procedure which recalculates all H01-H12 in tblKoristaja
with currently estimated monthly hours.

Those hours I can calculate with query qCurrKoristajaTunnid (this is MyQuery
in my earlier post):
SELECT tblKoristaja.TabN, Sum(qCurrKoristajaTegevused.Tunnid*tblTegevus.M01)
AS H01, Sum(qCurrKoristajaTegevused.Tunnid*tblTegevus.M02) AS H02,
Sum(qCurrKoristajaTegevused.Tunnid*tblTegevus.M03) AS H03,
Sum(qCurrKoristajaTegevused.Tunnid*tblTegevus.M04) AS H04,
Sum(qCurrKoristajaTegevused.Tunnid*tblTegevus.M05) AS H05,
Sum(qCurrKoristajaTegevused.Tunnid*tblTegevus.M06) AS H06,
Sum(qCurrKoristajaTegevused.Tunnid*tblTegevus.M07) AS H07,
Sum(qCurrKoristajaTegevused.Tunnid*tblTegevus.M08) AS H08,
Sum(qCurrKoristajaTegevused.Tunnid*tblTegevus.M09) AS H09,
Sum(qCurrKoristajaTegevused.Tunnid*tblTegevus.M10) AS H10,
Sum(qCurrKoristajaTegevused.Tunnid*tblTegevus.M11) AS H11,
Sum(qCurrKoristajaTegevused.Tunnid*tblTegevus.M12) AS H12
FROM (tblKoristaja LEFT JOIN qCurrKoristajaTegevused ON tblKoristaja.TabN =
qCurrKoristajaTegevused.TabN) LEFT JOIN tblTegevus ON
qCurrKoristajaTegevused.TegevusID = tblTegevus.TegevusID
GROUP BY tblKoristaja.TabN;

,where qCurrKoristajaTegevused gives the list of actions on different
objects for every employee, and calculates reserved time fior every action
in hours:
SELECT tblPindTegevusKoristaja.TabN, qLastPindTegevusKoristaja.PindID,
qLastPindTegevusKoristaja.TegevusID,
[tblPindTegevus].[Tunnid]+[tblPindTegevus].[Minutid]/60 AS Tunnid
FROM (tblPindTegevusKoristaja RIGHT JOIN qLastPindTegevusKoristaja ON
(tblPindTegevusKoristaja.PindID = qLastPindTegevusKoristaja.PindID) AND
(tblPindTegevusKoristaja.TegevusID = qLastPindTegevusKoristaja.TegevusID))
LEFT JOIN tblPindTegevus ON (tblPindTegevusKoristaja.TegevusID =
tblPindTegevus.TegevusID) AND (tblPindTegevusKoristaja.PindID =
tblPindTegevus.PindID)
WHERE (((tblPindTegevusKoristaja.TabN) Is Not Null) AND
((tblPindTegevusKoristaja.Alates)=[qLastPindTegevusKoristaja].[Last]));

, where qLastPindTegevusKoristaja gives the last time for every action on
every object, some employee was attached to it, i.e. current people attached
with every action on every object:
SELECT tblPindTegevusKoristaja.PindID, tblPindTegevusKoristaja.TegevusID,
Max(tblPindTegevusKoristaja.Alates) AS [Last]
FROM tblPindTegevusKoristaja
GROUP BY tblPindTegevusKoristaja.PindID, tblPindTegevusKoristaja.TegevusID;

Of course I can process the table tblKoristaja row-by-row, and replace
estimated hours with ones read from query, but I was searching for a way to
do it with with one go. It looks like there is no easy way to do it. ?!?

Thanks anyway!
Arvi Laanemets

I suggest you post the SQL of "MyQuery" so that it can be determined if
you can use the query directly in an update query. If not, perhaps we can
suggest alternatives. You might tell us how many records (tens,
thousands, millions) are involved as that can affect the recommended
solution.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Click to expand...
Click to expand...
 
Hi

I used a simple VBA script.

....
Set rst1 = New ADODB.Recordset
Set rst1.ActiveConnection = CurrentProject.Connection
rst1.CursorType = adOpenDynamic
rst1.LockType = adLockOptimistic
rst1.Open "tblKoristaja"

Set rst2 = New ADODB.Recordset
Set rst2.ActiveConnection = CurrentProject.Connection
rst2.CursorType = adOpenDynamic
rst2.LockType = adLockOptimistic

rst1.MoveFirst
Do While rst1.EOF = False
CurrTabn = rst1!TabN
rst2.Open "Select * FROM qCurrKoristajaTunnid WHERE TabN = '" &
CurrTabn & "'"
rst1!H01 = rst2!H01
rst1!H02 = rst2!H02
...
rst1!H12 = rst2!H12
rst2.Close
rst1.Update
rst1.MoveNext
Loop
....

There aren't enough data in tables to estimate how fast the scrip will work
in future, but with test data it was practically momentan. And the scrip
doesn't run automatically, but only when it is called by user (a button on
form is clicked), so a couple of seconds waiting time isn't an issue at all.


Arvi Laanemets
 
Back
Top