DCount

[nsteenhaut]

Hi,



I have two tables. One table called "Lawyers" and one table called "Law Firm Offices". Each Lawyer belongs to a Law Firm Office via the primary key "Law_Office_ID". I am trying to set up a query that returns the number of lawyers for each Law_Office_ID, so my query (called "No_Of_Attys_By_Office") has just 2 columns - Law_Office_ID and the field I built with the expression builder using DCount. The expression I buildt below, however, returns the total number of attorneys in the database and the number is the same for each Law_Office_ID. What am I doing wrong? Here's my expression:

Number_Of_Attorneys: DCount("[Lawyers]![Lawyer_ID]","Lawyers",[Lawyers]![Law_Office_ID]=[No_Of_Attys_By_Office]![Law_Office_ID])

[TheShabz]

There is no grouping with DCount. To get the count per law office you query:

SELECT [Law_Office_ID], Count([Law_Office_ID]) AS CountOfLawyers
FROM [Lawyers]
GROUP BY [Law_Office_ID]

[nsteenhaut]

THANKS!!! This worked like a charm!