Multiple Employees for same Job Code Question

[Dave]

On my form I have it where you enter a Employee and the date that they
worked on the job.
Control source for Employee is EmployeeID Control source for date is
Work Date

If there are more than (2) Employees for the job then I need a count of the
numbers of total employees for the job.
Or else a Text Box to be visible if the criteria is met.
The form has multiple records.

Thanks,

Dave

 

[John Vinson]

On my form I have it where you enter a Employee and the date that they
worked on the job.
Control source for Employee is EmployeeID Control source for date is
Work Date

If there are more than (2) Employees for the job then I need a count of the
numbers of total employees for the job.
Or else a Text Box to be visible if the criteria is met.
The form has multiple records.

Thanks,

Dave

You should be able to use

=DCount("*", "[your-table-name]", "[JobCode] = " & [JobCode])

assuming that you have a field named JobCode in the table.

What's the Recordsource for the form? I presume you have three tables
- Employees, Jobs, and JobAssignments?


John W. Vinson[MVP]
(no longer chatting for now)

 

[Dave Elliott]

How then can I make this code not count the same employee twice for the same
record?
But still total the amount of different employees for the total records?
Example; If I have Bill and Bob who worked on the same Job, i.e. record,
then it would count them as (2)
If I had Bill, Bob and John for the same record, count would be (3)
I need the same formula to work for all the records on this form..
Scroll to next record and then there are (4) employees for that record or
job.
Each job or record is done on a different day
Monday, Bill and Bob
Tuesday, Bill, Bob and John
Total would be (3) Bill and Bob would not be counted twice
Only need the count when there are more than (2) people on the same record
or job.

=DCount("*", "[Hours]", "[EmployeeID] = " & [EmployeeID])

On my form I have it where you enter a Employee and the date that they
worked on the job.
Control source for Employee is EmployeeID Control source for date
is
Work Date

If there are more than (2) Employees for the job then I need a count of
the
numbers of total employees for the job.
Or else a Text Box to be visible if the criteria is met.
The form has multiple records.

Thanks,

Dave

You should be able to use

=DCount("*", "[your-table-name]", "[JobCode] = " & [JobCode])

assuming that you have a field named JobCode in the table.

What's the Recordsource for the form? I presume you have three tables
- Employees, Jobs, and JobAssignments?


John W. Vinson[MVP]
(no longer chatting for now)

 

[John Vinson]

How then can I make this code not count the same employee twice for the same
record?
But still total the amount of different employees for the total records?
Example; If I have Bill and Bob who worked on the same Job, i.e. record,
then it would count them as (2)
If I had Bill, Bob and John for the same record, count would be (3)
I need the same formula to work for all the records on this form..
Scroll to next record and then there are (4) employees for that record or
job.
Each job or record is done on a different day
Monday, Bill and Bob
Tuesday, Bill, Bob and John
Total would be (3) Bill and Bob would not be counted twice
Only need the count when there are more than (2) people on the same record
or job.

Well, there should certainly NEVER be more than one person in the same
record. How are your tables structured? Where do you record that Bill,
Bob and John worked on the same job?

John W. Vinson[MVP]
(no longer chatting for now)