help with SUM statement

[DIOS]

I cant seem to think today. My database looks like so:

tblMain with MainID and fldNum fields
tblSub with SubID, MainID, and fldName

initially I fill in the tblSub with data. Then for each MainID I get the
number of records in tblSub
and insert that number into fldNum in tblMain for the same MainID. I do this
because it is faster
thatn displaying the fldNum on the fly and it is not a critical number.

Once in a while the user can run a utility to do an on-the-fly check to see
that the data in fldNum
is actually the correct record count. I can run a statement to get the
actual count like so:

SELECT tblMain.MainID, COUNT(tblSub.MainID) AS [NewNum]
FROM tblMain INNER JOIN tblSub ON tblMain.MainID= tblSub.MainID
WHERE tblMain.fldNum > 0
GROUP BY tblMain.MainID
ORDER BY tblMain.MainID ASC;

But am having a hard time constructing an UPDATE statement to update the
fldNum
fields with the actual count. I tried

UPDATE tblMain
SET tblMain.fldNum = COUNT(tblSub.MainID)
WHERE tblFiles.MainID= tblMain.MainID
GROUP BY tblMain.MainID;

but it did not work. Any help is appreciated.

AGP

 

[Ken Snell]

Your update query cannot get the value of COUNT(tblSub.MainID) nor does it
know what the value of tblFiles.MainID is -- because neither of these tables
(tblSub and tblFiles) are part of the update query.

Although it may or may not be updateable, try joining these tables into the
query, similar to what you show in the first SQL query.

 

[DIOS]

That seems to work. I forgot about generating a temp table.
Thanx for all the help.

AGP