Count in an Update Query

[Max Yaffe]

Dear Group,
I'm trying to do an SQL query which updates a column with the results
of a sum. If feel like it should be simple but I can't get the syntax
right. Here's the problem:

tableParts contains PartNo, IsAssembly (Key=PartNo)
tableBOM contains PartNo, SubPartNo (Key=PartNo+SubPartNo)

This select query returns Part, NewIsAssembly where NewIsAssembly = 0
if there are no subparts in a part or -1 if there are one or more
subparts in a part:

SELECT tblParts.Part, IIf(Count([tblBOM].[SubPart])>0,-1,0) AS
NewIsAssembly
FROM tblParts LEFT JOIN tblBOM ON tblParts.Part = tblBOM.Part
GROUP BY tblParts.Part;

And I can turn that into a make table query and update the value of
IsAssembly with the new table's NewIsAssembly. But is there a simple
update query which can calculate NewIsAssembly and sets IsAssembly to
the calculated value without going through a temporary table?

Converting the query above to an update query removes the calculation.

Your help is appreciated.

Max

 

[John Spencer]

In Access you either do what you are doing or use one of the VBA aggregate
functions to get the result you want

DCOUNT("NewIsAssembly","NameOfQuery","Part =""1234""")

I know of no other way to get around updating tables when you need to use
any aggregate function in the update query (other than in the where clause).