SUM records

[Steve]

I have a table, ITEMS which has a list of ITEMS and three number fields D1,
D2, D3. I have a second table, USE which has an ITEM field, DATE field, and
QTY field. I want to find the cords in the USE table where the ITEMS match
and sum the qty field field for a particular date range and insert it into
D1 of the ITEMS table.

So, I have:

Update ITEMS inner join USE on ITEMS.ITEM = USE.ITEM SET ITEMS.D1 =
SUM([USE].[QTY]);

However, I get an error that says you tried to execute a query that does not
include the specified expression D1 as part of an aggregate function.

Can anyone help me out?

thanks

 

[Steve]

Sorry, realized I didn't finish my statement in the last post.
I actually have

Update ITEMS inner join USE on ITEMS.ITEM = USE.ITEM SET ITEMS.D1 =
SUM([USE].[QTY]) WHERE USE.DATE>=#" & NOW() & "# and USE.DATE <= #" &
NOW()+30 & "#;"

I then repeat to update field D2 where the date is >=Now+30 and <=now+60 and
then repeat to update the D3 field for +60 and +90 then.

 

[John Smith]

I make the assumption that you are aware that items that can be derived should
not be stored, and have a good reason to breach normalisation.

On that basis, an aggregate query must have a GROUP BY clause listing all
columns mentioned which are not within an aggregate function, so you require:

"Update ITEMS inner join USE on ITEMS.ITEM = USE.ITEM SET ITEMS.D1 =
SUM([USE].[QTY]) WHERE USE.DATE BETWEEN #" & NOW() & "# AND #" & NOW()+30 & "#
GROUP BY ITEMS.D1"

Incidentally, DATE is not a good name to use for a column as it is an Access
reserved word. Doing so tends to result in strange errors when Access suddenly
gets confused as to whether it is referring to your column or the in-built date
function.

One final warning, are you sure that you want to use Now? What about items
where the use.date is earlier in the day than when the query is run? Do not
forget that if you do not put in a time you get midnight! You might want
Format$(Now, 'd/mmm/yyyy') and Format$(Now + 30, 'd/mmm/yyyy') to remove the
time element.

--
HTH
John

Sorry, realized I didn't finish my statement in the last post.
I actually have
Update ITEMS inner join USE on ITEMS.ITEM = USE.ITEM SET ITEMS.D1 =
SUM([USE].[QTY]) WHERE USE.DATE>=#" & NOW() & "# and USE.DATE <= #" &
NOW()+30 & "#;"
I then repeat to update field D2 where the date is >=Now+30 and <=now+60 and
then repeat to update the D3 field for +60 and +90 then.

I have a table, ITEMS which has a list of ITEMS and three number fields

D1, D2, D3. I have a second table, USE which has an ITEM field, DATE field,
and QTY field. I want to find the cords in the USE table where the ITEMS match

and sum the qty field field for a particular date range and insert it into
D1 of the ITEMS table.
So, I have:
Update ITEMS inner join USE on ITEMS.ITEM = USE.ITEM SET ITEMS.D1 =
SUM([USE].[QTY]);
However, I get an error that says you tried to execute a query that does

not include the specified expression D1 as part of an aggregate function.