Count in an Update Query

  • Thread starter Thread starter Max Yaffe
  • Start date Start date
M

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
 
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).
 
Back
Top