Need help with conditional format with multiple fields (Weekly field) and (ManppowerID)

[Beltramo64]

I want to create an conditional format that will do the following:

For each manpowerID ie.. person, if the sum of a field for a particular week is >= 40 (hrs) then Make the background green.

This currently works Expression is [AS1/18]>=40, but if a person is working on more than one job per week it does not work, so I want it to be the sum of the field [AS1/18], then make the background green. But I can't get it to work. Any ideas


thanks

[June7]

Use DSum() domain aggregate in the Conditional Formatting rule. Need some value that identifies the week period.

Expression Is: DSum("[AS1/18]", "tablename", "manpowerID=" & [manpowerID] & " AND [weekID]=" & [weekID]) >= 40

Or do the DSum() calc in query and reference that constructed field in the rule. If this is for a report, instead of DSum() can use an aggregate query that does the summary calc and join that query to the data with compound link on ID fields.

Advise not to use spaces nor punctuation/special characters (underscore only exception) in naming convention.

[Beltramo64]

it works if my sum adds up to 40 or more on one job, however if the man works on 2 or 3 customers jobs within the week, hence 3 separate inputs for the same person it does not work. maybe I need to add in the conditional format the customerID? What would that formula look like thanks in advance,

[isladogs]

Why not base your form or report on an aggregate query?
Group by CustomerID and sum the hours for that week
Then apply conditional formatting on that.

EDIT - oops - missed that June had already suggested this in post 2

[June7]

The suggested DSum() should add all hours for employee in given week regardless of the jobs.

Adding the customerID as criteria would require sum to be >= 40 for each employee/customer/week combination for field to be green. If that's what you want then go ahead. Include as many criteria in the DSum() (or aggregate query) as needed to get the desired result.

And one approach for using aggregate query presented in post 2. The more grouping criteria, the more links in joining datasets. Depends on what level detail you want to display in the report.

[Beltramo64]

Alright I am getting closer to what I want, but need some assistance, I have created a form where I can input the hours per week for each employee. What is the following 2 formulas:

1. In the conditional format, for example under field [AS1_18] I want an expression that once the sum of the field ie... [AS1_18]=40, for a particular employee [ManpowerID], to make it green. Please note that in the table where you are inputting the hours for a particular week ie.. [AS1_18] there can be more than one input under the AssignedID Field that would add up to 40 hours. This is very important in the Form, because some managers share the same workers and I need the managers to know (visually through it being green) that, a man may not be available, hence his total is green. Which leads me to my
2nd request.
2. in the same field ie... [AS1_18] a formula/expression in the validation rule that only allows for one [ManpowerID] to have a sum of 40 hours (obviously the you can have more than one [AssignedCalendarID] adding up to a total of 40 hours) under the field ie... [AS1_18]. If the sum exceeds 40 hours, validation text will be displayed to says "the man has already been used for this week".


Thanks in advance,
Currently using the expression for conditional formatting for field AS1_18 and it does not work
DSum("[AS1/18]","TBL_AssignedCalendar","manpowerID=" & [manpowerID] & " AND [AssignedCalendarID]=" & [AssignedCalendarID])>=40

[June7]

The field name is AS1_18 not AS1/18.

[Beltramo64]

yes, you are correct it works now. any thoughts on the 2nd request

[June7]

So in addition to the color of textbox, you want a popup message?

I don't know enough about your data structure and form design.