Get a total with associative entity

[mainmast]

An employee can have more than one position and a position can belong to more than one employee.



I have three tables:
Employee
EmployeePosition
Position

How do I get a query to show that total number of positions held by a employee?

[Gicu]

You turn the query you have into a totals query by clicking on the sum (sigma) symbol on the Design ribbon then add the PositionID field form the EmployeePosition table,leave the two fields you currently have to have GroupBy in the Totals row and select Count for the PositionId. You should really review your table design; the EmployeePosition table should have an autonumber field EmployeePositionID set as the primary key, the PositionId field should be renamed PositionID_FK (and the one in the Position table PositionId_PK), ... you get the idea, easier to avoid confusion when building queries.

Cheers,

[mainmast]

Thanks Vlad,
Got it working