Using DCount() in query grid

[Harry Shmedlap]

I am a beginner with aggregate functions.
I have two tables, one describing parts, and one describing assemblies that use those parts:

tblParts show all the part descriptions, including fldPartNumber.

tlbWhereUsed shows zero or more fldAssemblyNumber records for each fldPartNumber.

I want a query that shows, for each record of tblParts, the number of assemblies that use that part. I specifically want to do this with an aggregate function:

DCount("*","tlbWhereUsed ","[tlbWhereUsed].[fldPartNumber]=[tblParts].[fldPartNumber]")

but I don't know where to put this function call in the query grid.
(I do not want to do this in VBA, if I don't need to).
Thanks in advance...

 

[stopher]

If fldPartNumber is of data type TEXT then use:

DCount("*","tblWhereUsed","[fldPartNumber]='" & [fldPartNumber] & "'")

Otherwise if data type is numeric then use

DCount("*","tblWhereUsed","[fldPartNumber]=" & [fldPartNumber] )

Basically you stick this in the top row of an empty column. Access will append it with a name which you can change.

hth
Chris

 

[Harry Shmedlap]

Thanks!
Follow-up questions:
1) In your expression:
DCount("*","tblWhereUsed","[fldPartNumber]='" & [fldPartNumber] & "'")

could you explain then last part: '" & [fldPartNumber] & "'"

2) Where can I read up more on the Internet about using aggregate function?

 

[stopher]

I doubt if the internet is going to add more on the subject of aggregate functions than you can read in Access help.

Supposing the value of [fldPartNumber] in your data source is the sting "12345" . Then the third arguement "[fldPartNumber]='" & [fldPartNumber] & "'" equates to

"[fldPartNumber]='12345' "

In other words we've created the required string expression by glueing the bits together using the & concatenator.

There are better explanations than mine of the "where" arguement in these forums so maybe search dsum and dcount etc.

hth
Chris