Reports and forms

[Chris75 via AccessMonster.com]

Hello,

I have no idea if this is at all possible.

I have a form with employee details, it is call Employees. Within this form,
I have three subforms. One for scheduling, called Schedule. One for
productivity, called Productivity and I also have a subform for workload
assignment, this subform is called Assignment.

I have a productivity report that gives me each employee's statistics based
on the
previous week's work. Each employee is also assigned a different workload,
but this could change on a daily basis. Each
workload has different standards to meet.

What I would like to do is the following. Based on the workload assigned a
particular day (or week), I would like to set the conditional formatting in
my productivity report such that if a person is below the norms, it would
appear as red.

Example:

If Bob is assigned to work in Bin 1 on Monday, and Bin 1 has a
standard of 11 widgets. I would like the productivity report (which is
generated by data entered in the Productivity table\subform), to have a
conditional formatting showing whether Bob met the standards or not, Bob's
stats appear in red if not. This pattern would continue if Bob worked in
more than one Bin that week. Bin 2 would have a standard of 30 widgets, thus
conditional formatting for that as well.

How can I link the information from two subforms, such that in the
conditional formatting of the productivity report, a number entered in one
subform (productivity) could appear in red, based on information from another
subform (assignment)?
Keep in mind that in the design of the productivity report, there is no
relationship to the assignment table (subform). The productivity report is
based solely on the productivity table (subform)

Thank you.

Thank you.

 

[Klatuu]

Whether it is on a report or a form does not matter, but you do your
conditional formatting on the form or report. The tables are not relavent.

If you can provide more details about which subforms are involved and which
controls on the subforms you are using, or if it is a report, then perhaps I
can offer a suggestion.

 

[KenSheridan via AccessMonster.com]

Forget about the subforms, they are just the interface with the data. It’s
the data in the underlying tables which are relevant to the report. The
simplest way would be to join the Productivity, Assignment and Bins tables on
the relevant employee, date and bin columns. You can then compare the
standard for the bin per employee/date and with the performance figure for
the employee/date, e.g.

SELECT Productivity.Employee, Productivity.DateWorked,
Productivity.Performance,
Productivity.Performance >= Bins.Standard AS StandardAchieved
FROM Productivity, Assignment, Bins
WHERE Assigment.Employee = Productivity.Employee
AND Assignment.AssignmentDate = Productivity.DateWorked
AND Assignment.Bin = Bins.Bin;

Using a query along the above lines as the report's RecordSource the
Performance control can be conditionally formatted to red where
StandardAchieved = False.

Ken Sheridan
Stafford, England