DO LOOP vs QUERY

[Steve]

I would like to sum a field in a Table2 and update a field in a Table1 with
the total where a particular field matches.

ie, Table1 has a list of ITEMS with a field called USAGE and the ITEMS field
is a primary field. Table2 has the ITEMS field and a QTY field but the ITEMS
field is duplicated for each QTY transaction. I want to add up the QTY field
for each unqiue ITEMS in Table2 and update the USAGE field in TABLE1 for the
corresponding ITEMS.

I tried to build a query in Access by pulling up the two tables and linking
the ITEMS field then updating the USAGE field in TABLE1 with
Sum([table2.qty]). I try to run it and get an error that I tried to execute
a query where USAGE is not part of the aggragate. It shows a query of UPDATE
Table1 INNER JOIN Table2 ON Tablw1.ITEMS = Table2.ITEMS SET Table1.[USAGE] =
Sum([Table2].[qty]);

I also tried hand typing it as UPDATE Table1 SET Table1.[USAGE] =
Sum([table2].[qty]) where table1.items = table2.items;

Can anyone help me with where I am going wrong?

 

[Treebeard]

I would like to sum a field in a Table2 and update a field in a Table1 with
the total where a particular field matches.

ie, Table1 has a list of ITEMS with a field called USAGE and the ITEMS field
is a primary field. Table2 has the ITEMS field and a QTY field but the ITEMS
field is duplicated for each QTY transaction. I want to add up the QTY field
for each unqiue ITEMS in Table2 and update the USAGE field in TABLE1 for the
corresponding ITEMS.

I tried to build a query in Access by pulling up the two tables and linking
the ITEMS field then updating the USAGE field in TABLE1 with
Sum([table2.qty]). I try to run it and get an error that I tried to execute
a query where USAGE is not part of the aggragate. It shows a query of UPDATE
Table1 INNER JOIN Table2 ON Tablw1.ITEMS = Table2.ITEMS SET Table1.[USAGE] =
Sum([Table2].[qty]);

I also tried hand typing it as UPDATE Table1 SET Table1.[USAGE] =
Sum([table2].[qty]) where table1.items = table2.items;

Can anyone help me with where I am going wrong?

I don't think you can do updates like that. You're not supposed to keep a
calculated value in a table field (according to Database 101). However, I
always keep calculated values in tables because Database 101 doesn't work
in the real world.


Put this function in one of your modules:

Public Function GetUsageSum(strItem As String) As Long
' assuming Item is a string, Qty is a long
Dim strSQL As String, lngQtySum As Long
Dim dbs As ADODB.Connection, rst As ADODB.Recordset

lngQtySum = 0

strSQL = " SELECT Sum([qty]) AS [qtySum] " & _
"FROM [Table2] WHERE [items ] = '" & strItem & "'"

Set dbs = CurrentProject.Connection
Set rst = New Recordset

rst.Open strSQL, dbs, adOpenStatic, adLockOptimistic

If rst.RecordCount > 0 Then
If IsNumeric(rst!qtySum) Then
lngQtySum = rst!qtySum
End If
End If
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

End Function
*******************************
Now use this for your query:

UPDATE Table1 SET Table1.[USAGE] = GetUsageSum(Table1.[USAGE] );

 

[TC]

(snip)

You're not supposed to keep a
calculated value in a table field (according to Database 101). However, I
always keep calculated values in tables because Database 101 doesn't work
in the real world.

That might come as a surprise to hundreds of thousands (millions?) of
professional software developers around the world.

TC

 

[Dirk Goldgar]

I would like to sum a field in a Table2 and update a field in a
Table1 with the total where a particular field matches.

ie, Table1 has a list of ITEMS with a field called USAGE and the
ITEMS field is a primary field. Table2 has the ITEMS field and a QTY
field but the ITEMS field is duplicated for each QTY transaction. I
want to add up the QTY field for each unqiue ITEMS in Table2 and
update the USAGE field in TABLE1 for the corresponding ITEMS.

I tried to build a query in Access by pulling up the two tables and
linking the ITEMS field then updating the USAGE field in TABLE1 with
Sum([table2.qty]). I try to run it and get an error that I tried to
execute a query where USAGE is not part of the aggragate. It shows a
query of UPDATE Table1 INNER JOIN Table2 ON Tablw1.ITEMS =
Table2.ITEMS SET Table1.[USAGE] = Sum([Table2].[qty]);

I also tried hand typing it as UPDATE Table1 SET Table1.[USAGE] =
Sum([table2].[qty]) where table1.items = table2.items;

Can anyone help me with where I am going wrong?

First, why do this? Why store a total that any future activity in
Table2 is almost certain to invalidate? If you want to know at any time
what is the total USAGE for each ITEMS, why not just run the query

SELECT
Table1.ITEMS,
Nz(Sum(USAGE),0) AS TotalUsage
FROM
Table1 LEFT JOIN Table2
ON Table1.ITEMS = Table2.ITEMS
GROUP BY Table1.ITEMS;

?

 

[Steve]

I would like to sum a field in a Table2 and update a field in a
Table1 with the total where a particular field matches.
ie, Table1 has a list of ITEMS with a field called USAGE and the
ITEMS field is a primary field. Table2 has the ITEMS field and a QTY
field but the ITEMS field is duplicated for each QTY transaction. I
want to add up the QTY field for each unqiue ITEMS in Table2 and
update the USAGE field in TABLE1 with that total.
I tried to build a query in Access by pulling up the two tables and
linking the ITEMS field then updating the USAGE field in TABLE1 with
Sum([table2.qty]). I try to run it and get an error that I tried to
execute a query where USAGE is not part of the aggragate. It shows a
query of UPDATE Table1 INNER JOIN Table2 ON Tablw1.ITEMS =
Table2.ITEMS SET Table1.[USAGE] = Sum([Table2].[qty]);

First, why do this? Why store a total that any future activity in
Table2 is almost certain to invalidate? If you want to know at any time
what is the total USAGE for each ITEMS, why not just run the query

SELECT
Table1.ITEMS,
Nz(Sum(USAGE),0) AS TotalUsage
FROM
Table1 LEFT JOIN Table2
ON Table1.ITEMS = Table2.ITEMS
GROUP BY Table1.ITEMS;

Dirk Goldgar, MS Access MVP
(please reply to the newsgroup)

Table1 is a temp table that gets deleted and recreated as the user executes
the program functions - so, the total usage query is actually being executed
each time and the total is not stored beyond the users immediate session.
The temp table accummulates data from about 15 other dissimilar (ie, non-key
matching, non-index matching, duplicates allowed type fields and tables)
tables as the user makes selections. The users desire to see their results
at each selection to aid them with how to make further selections so the
information is most useful to them.

 

[Dirk Goldgar]

I would like to sum a field in a Table2 and update a field in a
Table1 with the total where a particular field matches.
ie, Table1 has a list of ITEMS with a field called USAGE and the
ITEMS field is a primary field. Table2 has the ITEMS field and a QTY
field but the ITEMS field is duplicated for each QTY transaction. I
want to add up the QTY field for each unqiue ITEMS in Table2 and
update the USAGE field in TABLE1 with that total.
I tried to build a query in Access by pulling up the two tables and
linking the ITEMS field then updating the USAGE field in TABLE1 with
Sum([table2.qty]). I try to run it and get an error that I tried to
execute a query where USAGE is not part of the aggragate. It shows a
query of UPDATE Table1 INNER JOIN Table2 ON Tablw1.ITEMS =
Table2.ITEMS SET Table1.[USAGE] = Sum([Table2].[qty]);

First, why do this? Why store a total that any future activity in
Table2 is almost certain to invalidate? If you want to know at any
time what is the total USAGE for each ITEMS, why not just run the
query

SELECT
Table1.ITEMS,
Nz(Sum(USAGE),0) AS TotalUsage
FROM
Table1 LEFT JOIN Table2
ON Table1.ITEMS = Table2.ITEMS
GROUP BY Table1.ITEMS;

Table1 is a temp table that gets deleted and recreated as the user
executes the program functions - so, the total usage query is
actually being executed each time and the total is not stored beyond
the users immediate session. The temp table accummulates data from
about 15 other dissimilar (ie, non-key matching, non-index matching,
duplicates allowed type fields and tables) tables as the user makes
selections. The users desire to see their results at each selection
to aid them with how to make further selections so the information is
most useful to them.

Under those circumstances, and considering you're using temp tables
already, I suppose you may as well add one more:

CREATE TABLE UsageSummary
(ITEMS Integer PRIMARY KEY, TotUsage Integer);

INSERT INTO UsageSummary (ITEMS, TotUsage)
SELECT ITEMS, Sum(Qty) FROM Table2
GROUP BY ITEMS;

UPDATE
Table1 LEFT JOIN UsageSummary
ON Table1.ITEMS = UsageSummary.ITEMS
SET Table1.USAGE = Nz(UsageSummary.TotUsage, 0);

DROP TABLE UsageSummary;

All this traffic in temp tables is probably going to bloat your
database, so you'll want to remember to compact regularly.