Create a Headcount Access query based on dates

[markgoodridge]

Hi All,



I am trying to create what I thought would be an easy access query.

I'm trying to determine the number of staff working for my organisation as at today's date, based upon their assignment start date and assignment end date. So if an employee is in post as at today, it will return a 1 and if not a zero, which I would then like to total up.

The main table that I believe should be used is listed out below:

AssignmentID(PK)
StaffName
RoleTitle
CurrentFTE
AssignmentStart (date)
AssignmentEnd (date)

Ideally, I would like to know how to build a formula using design view, rather than SQL.

Any assistance would be gratefully received.

Many, many thanks in advance.

Mark

[JoeM]

Is the AssignmentEnd field always populated?

If so, then try this formula:

IIf(([AssignmentStart]<=Date()) And ([AssignmentEnd]>=Date()),1,0)

Note that you can also get your count in a query without any formulas by doing an Aggregate Count Query, using Criteria.

[ranman256]

Don't use SQL, use a query. Similar to :
Select count(staffname) from table where startDate <= date and EndDate >= date

[markgoodridge]

Hi Joe,
Yes the "assignmentend" field is a contracted end date of 31/03/20, although could be different if the employee has already left