how to display two count values in a field. (1 Viewer)

[Siegfried]

Dear experts,

Calling once more on your expertise.

I have a form that lists all employees (FrmEmployees.EmployeeID) with their job title (JobTitleID).
I want to display on the form in a separate field the total employee count of the hired employees and, if so applicable any temp seperately .
Example: we have 44 permanently employed staff, and presently 2 hired temps. So the count would be (44/2). When the temp contract(s) ended then the count would show (44).

Can anyone guide on how to approch this? VBA, concantenate two query counts?

Thanks for your help.

Best regards,

Siegfried

 

[isladogs]

Something like this

="(" & DCount("*","tblEmployees") & "/" & DCount("*","tblEmployees", "JobTitleID = 'Temp'") & ")"

Substitute table name & field names as appropriate

 

[arnelgp]

Something like :


="(" & dcount("*","employee_table","[employment status field]='permanent' and isnull([separation from company date field])") & iif(dount("*", "employee_table","[employment status field]='temp' and IsNull([separation from company date field]")=0, "", "/" & dount("*", "employee_table","[employment status field]='temp' and IsNull([separation from company date field]")) & ")"

 

[Siegfried]

Thanks colin but I'm getting a value 44/44, being both total count of employees...(?)

=DCount("*";"TblEmployees") & "/" & DCount("*";"TblEmployees";"JobTitleID - '12'")

12 being the temp job title.

I'm want to achieve total count 44 and when we have a interim worker (temp) to show that additional as / count.
Our present staff count is 44 permanent and 2 interim workers, hence (44/2).
If the interim workers end their temporary contract the count will show (44) not (44/).

kind regards,

Siegfried

 

[Siegfried]

Thanks Arnelgp,

What do you mean with [separation from company date field]?

Kind regards,
Siegfried

 

[isladogs]

Thanks colin but I'm getting a value 44/44, being both total count of employees...(?)

=DCount("*";"TblEmployees") & "/" & DCount("*";"TblEmployees";"JobTitleID - '12'")

12 being the temp job title.

I'm want to achieve total count 44 and when we have a interim worker (temp) to show that additional as / count.
Our present staff count is 44 permanent and 2 interim workers, hence (44/2).
If the interim workers end their temporary contract the count will show (44) not (44/).

kind regards,

Siegfried

I've corrected an error in my post. Should be equals sign after jobtitleID
As it's a number field, remove the single quotes around '12'

 

[Siegfried]

Thanks Colin.
This helped out.

=DCount("*";"tblEmployees";"functionFK<>12") & "/" & DCount("*";"tblEmployees";"functionFK=12")

 

[arnelgp]

Dont you record the date when an employee leaves permanently or you just delete their record. Thats what the field means. Only those employee currently active will be included.

 

[isladogs]

Thanks Colin.
This helped out.

=DCount("*";"tblEmployees";"functionFK<>12") & "/" & DCount("*";"tblEmployees";"functionFK=12")

Glad it worked out for you