If difference is bigger than 0.1, then some more information show upon the report

  • Thread starter Thread starter Tracey
  • Start date Start date
T

Tracey

Hi there,

I have a question for my report. We used to only put target time and
actual time on the report. But recently my boss wanted to list more
information on the report. If the difference between actual time and
target time is bigger than 0.1, then list runtime for each worker who
worked for this job at this workcenter (usually, many jobs at
different workcenters are listed on the report).

How can I make this condition formula in Query or Report? Please help
me.

Thanks,
Tracey
 
Tracey said:
I have a question for my report. We used to only put target time and
actual time on the report. But recently my boss wanted to list more
information on the report. If the difference between actual time and
target time is bigger than 0.1, then list runtime for each worker who
worked for this job at this workcenter (usually, many jobs at
different workcenters are listed on the report).

How can I make this condition formula in Query or Report?
Click to expand...


I think you might need to create a report to display the
workers. This report would need the workcenter and jobs for
each worker, but those text boxes do not need to be visible.

Then your existing report should remove the workers table
from its record source query. Drag and drop the workers
report into the detail section of your existing report and
set the LinkChild/Master properties to the Workcenter and
Job ID fields.

To calculate the actual times for a job, use a text box
(named txtJobTotal) in the subreport's Report Footer section
with an expression like =Sum([actual time])
Then main report can display that job total time in a text
box using the expression
=IIf(subreportcontrol.Report.HasData,subreportcontrol.Report.txtJobTotal,0)

The difference between the target and actual time can be
calculated in the detail section's Format event procedure
using code like:
Me.difference = Me.targetTime -
IIf(subreportcontrol.Report.HasData,subreportcontrol.Report.txtJobTotal,0)

And hide the subreport for when the difference is small:
Me.subreportcontrol.Visible = difference > .1

If you can not create the subreport's record source workers
query, it's probably because you tables are not properly
normalized. If they are not, I doubt that you can do what
you want without creating some gruesome workarounds.
 
Tracey said:
I have a question for my report. We used to only put target time and
actual time on the report. But recently my boss wanted to list more
information on the report. If the difference between actual time and
target time is bigger than 0.1, then list runtime for each worker who
worked for this job at this workcenter (usually, many jobs at
different workcenters are listed on the report).
Click to expand...
How can I make this condition formula in Query or Report?
Click to expand...

I think you might need to create a report to display the
workers.  This report would need the workcenter and jobs for
each worker, but those text boxes do not need to be visible.

Then your existing report should remove the workers table
from its record source query.  Drag and drop the workers
report into the detail section of your existing report and
set the LinkChild/Master properties to the Workcenter and
Job ID fields.

To calculate the actual times for a job, use a text box
(named txtJobTotal) in the subreport's Report Footer section
with an expression like  =Sum([actual time])
Then main report can display that job total time in a text
box using the expression
=IIf(subreportcontrol.Report.HasData,subreportcontrol.Report.txtJobTotal,0)

The difference between the target and actual time can be
calculated in the detail section's Format event procedure
using code like:
Me.difference = Me.targetTime -
IIf(subreportcontrol.Report.HasData,subreportcontrol.Report.txtJobTotal,0)

And hide the subreport for when the difference is small:
Me.subreportcontrol.Visible = difference > .1

If you can not create the subreport's record source workers
query, it's probably because you tables are not properly
normalized.  If they are not, I doubt that you can do what
you want without creating some gruesome workarounds.
Click to expand...

Hi Marsh,

Thank you for your great help. I will have a try and then let you know
how it works.

Tracey
 
Tracey said:
I have a question for my report. We used to only put target time and
actual time on the report. But recently my boss wanted to list more
information on the report. If the difference between actual time and
target time is bigger than 0.1, then list runtime for each worker who
worked for this job at this workcenter (usually, many jobs at
different workcenters are listed on the report).
Click to expand...
How can I make this condition formula in Query or Report?
Click to expand...

I think you might need to create a report to display the
workers.  This report would need the workcenter and jobs for
each worker, but those text boxes do not need to be visible.

Then your existing report should remove the workers table
from its record source query.  Drag and drop the workers
report into the detail section of your existing report and
set the LinkChild/Master properties to the Workcenter and
Job ID fields.

To calculate the actual times for a job, use a text box
(named txtJobTotal) in the subreport's Report Footer section
with an expression like  =Sum([actual time])
Then main report can display that job total time in a text
box using the expression
=IIf(subreportcontrol.Report.HasData,subreportcontrol.Report.txtJobTotal,0)

The difference between the target and actual time can be
calculated in the detail section's Format event procedure
using code like:
Me.difference = Me.targetTime -
IIf(subreportcontrol.Report.HasData,subreportcontrol.Report.txtJobTotal,0)

And hide the subreport for when the difference is small:
Me.subreportcontrol.Visible = difference > .1

If you can not create the subreport's record source workers
query, it's probably because you tables are not properly
normalized.  If they are not, I doubt that you can do what
you want without creating some gruesome workarounds.
Click to expand...

Hello Marsh,

Total actual time is already showed up on the main report, so I didn't
try to calculate and use this expression on the main report =IIf
(subreportcontrol.Report.HasData,subreportcontrol.Report.txtJobTotal,
0)

I did put following codes in the detail section's Format event
precedure
Me. difference=Me.targetTime-actualTime
Me.subreportcontrol.Visible=difference>.1
Then I was told micro 'Me' can't be found. I am just a beginner to use
Access and know nothing about micro yet. So you please help what I
should do?

And if we change the difference to rate, rate=(actualtime-targettime)/
targettime. When rate>0.1, subreport should be visible. What should I
do?

Please help,
Thanks,
Tracey
 
Tracey said:
Tracey said:
I have a question for my report. We used to only put target time and
actual time on the report. But recently my boss wanted to list more
information on the report. If the difference between actual time and
target time is bigger than 0.1, then list runtime for each worker who
worked for this job at this workcenter (usually, many jobs at
different workcenters are listed on the report).
Click to expand...
How can I make this condition formula in Query or Report?
Click to expand...

I think you might need to create a report to display the
workers.  This report would need the workcenter and jobs for
each worker, but those text boxes do not need to be visible.

Then your existing report should remove the workers table
from its record source query.  Drag and drop the workers
report into the detail section of your existing report and
set the LinkChild/Master properties to the Workcenter and
Job ID fields.

To calculate the actual times for a job, use a text box
(named txtJobTotal) in the subreport's Report Footer section
with an expression like  =Sum([actual time])
Then main report can display that job total time in a text
box using the expression
=IIf(subreportcontrol.Report.HasData,subreportcontrol.Report.txtJobTotal,0)

The difference between the target and actual time can be
calculated in the detail section's Format event procedure
using code like:
Me.difference = Me.targetTime -
IIf(subreportcontrol.Report.HasData,subreportcontrol.Report.txtJobTotal,0)

And hide the subreport for when the difference is small:
Me.subreportcontrol.Visible = difference > .1

If you can not create the subreport's record source workers
query, it's probably because you tables are not properly
normalized.  If they are not, I doubt that you can do what
you want without creating some gruesome workarounds.
Click to expand...

Total actual time is already showed up on the main report, so I didn't
try to calculate and use this expression on the main report =IIf
(subreportcontrol.Report.HasData,subreportcontrol.Report.txtJobTotal,
0)
Click to expand...

Are you sure you calculation for actual time is correct? If
it is, then either you do not have that field in the workers
or a related table or you did not remove that info from the
main report's record source query. In both those cases, I
can't say how the problem can be solved with a subreport.

OTOH, maybe I have completely misunderstood what you are
doing and my idea is out in left field.

I did put following codes in the detail section's Format event
precedure
Me. difference=Me.targetTime-actualTime
Me.subreportcontrol.Visible=difference>.1
Then I was told micro 'Me' can't be found. I am just a beginner to use
Access and know nothing about micro yet. So you please help what I
should do?
Click to expand...

That error implies that you tried to put the VBA code in the
report's OnFormat ***property*** instead of the Format event
***procedure***
And if we change the difference to rate, rate=(actualtime-targettime)/
targettime. When rate>0.1, subreport should be visible. What should I
do?
Click to expand...

That's only a minor modification of the code I posted, but
let's not get distracted with that until the report is
running properly.
 
Tracey said:
Tracey wrote:
I have a question for my report. We used to only put target time and
actual time on the report. But recently my boss wanted to list more
information on the report. If the difference between actual time and
target time is bigger than 0.1, then list runtime for each worker who
worked for this job at this workcenter (usually, many jobs at
different workcenters are listed on the report).
How can I make this condition formula in Query or Report?
I think you might need to create a report to display the
workers.  This report would need the workcenter and jobs for
each worker, but those text boxes do not need to be visible.
Then your existing report should remove the workers table
from its record source query.  Drag and drop the workers
report into the detail section of your existing report and
set the LinkChild/Master properties to the Workcenter and
Job ID fields.
To calculate the actual times for a job, use a text box
(named txtJobTotal) in the subreport's Report Footer section
with an expression like  =Sum([actual time])
Then main report can display that job total time in a text
box using the expression
=IIf(subreportcontrol.Report.HasData,subreportcontrol.Report.txtJobTotal,0)
The difference between the target and actual time can be
calculated in the detail section's Format event procedure
using code like:
Me.difference = Me.targetTime -
IIf(subreportcontrol.Report.HasData,subreportcontrol.Report.txtJobTotal,0)
And hide the subreport for when the difference is small:
Me.subreportcontrol.Visible = difference > .1
If you can not create the subreport's record source workers
query, it's probably because you tables are not properly
normalized.  If they are not, I doubt that you can do what
you want without creating some gruesome workarounds.
Click to expand...
Click to expand...
Total actual time is already showed up on the main report, so I didn't
try to calculate and use this expression on the main report =IIf
(subreportcontrol.Report.HasData,subreportcontrol.Report.txtJobTotal,
0)
Click to expand...

Are you sure you calculation for actual time is correct?  If
it is, then either you do not have that field in the workers
or a related table or you did not remove that info from the
main report's record source query.  In both those cases, I
can't say how the problem can be solved with a subreport.

OTOH, maybe I have completely misunderstood what you are
doing and my idea is out in left field.
I did put following codes in the detail section's Format event
precedure
Me. difference=Me.targetTime-actualTime
Me.subreportcontrol.Visible=difference>.1
Then I was told micro 'Me' can't be found. I am just a beginner to use
Access and know nothing about micro yet. So you please help what I
should do?
Click to expand...

That error implies that you tried to put the VBA code in the
report's OnFormat ***property*** instead of the Format event
***procedure***


And if we change the difference to rate, rate=(actualtime-targettime)/
targettime. When rate>0.1, subreport should be visible. What should I
do?
Click to expand...

That's only a minor modification of the code I posted, but
let's not get distracted with that until the report is
running properly.

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -
Click to expand...

Sorry Marsh. Maybe I made you confused.

On our original report, we put job#, workcenter#, employee name,
target, actual to date, and run time. So everyday, it shows who does
which job for how long at which workcenter. The target is the total
time for this job at this workcenter, it doesn't matter who does the
job. Actual to date is the total time for all employees who work for
this job at this workcenter. The report looks like:
Job Workcenter Name Target Actual to Date Runtime
P17554 RB Bill 8
9 6
SS AL 21.1
8.3 0.9
CT 21.1
8.3 3.4
P17575 A Tom 15.5 11.6
0.3
F Dave 20.5
25.7 7.3

If 'Actual to Date' is 10% over 'Target', we want to know who worked
on this job at this workcenter, and how long did each of them work?
That means we hope to report turns out to be:
Job Workcenter Name Target Actual to Date Runtime
P17554 RB Bill 8
9 6

Art 3

Bill 6
SS AL 21.1
8.3 0.9
CT 21.1
8.3 3.4
P17575 A Tom 15.5 11.6
0.3
F Dave 20.5
25.7 7.3

Franco 8

Tony 8

Peter 2.4

Dave 7.3

I hope I am making it clearly. And I was thinking you understood what
we wanted before. If not, please help me on this.

Just now I put the codes in the report's Format event***procedure***,
and got this message 'compile error' 'method or data member not
found'. What does this mean.

Thanks,
Tracey
 
Tracey said:
Tracey wrote:
I have a question for my report. We used to only put target time and
actual time on the report. But recently my boss wanted to list more
information on the report. If the difference between actual time and
target time is bigger than 0.1, then list runtime for each worker who
worked for this job at this workcenter (usually, many jobs at
different workcenters are listed on the report).
How can I make this condition formula in Query or Report?
I think you might need to create a report to display the
workers.  This report would need the workcenter and jobs for
each worker, but those text boxes do not need to be visible.
Then your existing report should remove the workers table
from its record source query.  Drag and drop the workers
report into the detail section of your existing report and
set the LinkChild/Master properties to the Workcenter and
Job ID fields.
To calculate the actual times for a job, use a text box
(named txtJobTotal) in the subreport's Report Footer section
with an expression like  =Sum([actual time])
Then main report can display that job total time in a text
box using the expression
=IIf(subreportcontrol.Report.HasData,subreportcontrol.Report.txtJobTotal,0)
The difference between the target and actual time can be
calculated in the detail section's Format event procedure
using code like:
Me.difference = Me.targetTime -
IIf(subreportcontrol.Report.HasData,subreportcontrol.Report.txtJobTotal,0)
And hide the subreport for when the difference is small:
Me.subreportcontrol.Visible = difference > .1
If you can not create the subreport's record source workers
query, it's probably because you tables are not properly
normalized.  If they are not, I doubt that you can do what
you want without creating some gruesome workarounds.
Total actual time is already showed up on the main report, so I didn't
try to calculate and use this expression on the main report =IIf
(subreportcontrol.Report.HasData,subreportcontrol.Report.txtJobTotal,
0)
Click to expand...
Click to expand...
Are you sure you calculation for actual time is correct?  If
it is, then either you do not have that field in the workers
or a related table or you did not remove that info from the
main report's record source query.  In both those cases, I
can't say how the problem can be solved with a subreport.
Click to expand...
OTOH, maybe I have completely misunderstood what you are
doing and my idea is out in left field.
Click to expand...
That error implies that you tried to put the VBA code in the
report's OnFormat ***property*** instead of the Format event
***procedure***
Click to expand...
That's only a minor modification of the code I posted, but
let's not get distracted with that until the report is
running properly.
Click to expand...
- Show quoted text -
Click to expand...

Sorry Marsh. Maybe I made you confused.

On our original report, we put job#, workcenter#, employee name,
target, actual to date, and run time. So everyday, it shows who does
which job for how long at which workcenter. The target is the total
time for this job at this workcenter, it doesn't matter who does the
job. Actual to date is the total time for all employees who work for
this job at this workcenter. The report looks like:
Job          Workcenter  Name    Target   Actual to Date   Runtime
P17554         RB           Bill         8
9                   6
                    SS           AL        21.1
8.3               0.9
                                   CT         21.1
8.3              3.4
P17575          A            Tom       15.5          11.6
0.3
                     F            Dave       20.5
25.7            7.3

If 'Actual to Date' is 10% over 'Target', we want to know who worked
on this job at this workcenter, and how long did each of them work?
That means we hope to report turns out to be:
Job          Workcenter  Name    Target   Actual to Date   Runtime
P17554         RB           Bill         8
9                   6

Art       3

Bill       6
                    SS           AL        21.1
8.3               0.9
                                   CT         21.1
8.3              3.4
P17575          A            Tom       15.5          11.6
0.3
                     F            Dave       20.5
25.7            7.3

Franco  8

Tony     8

Peter    2.4

Dave     7.3

I hope I am making it clearly. And I was thinking you understood what
we wanted before. If not, please help me on this.

Just now I put the codes  in the report's Format event***procedure***,
and got this message 'compile error' 'method or data member not
found'. What does this mean.

Thanks,
Tracey- Hide quoted text -

- Show quoted text -
Click to expand...

The samples of our report was messed up. The original one is like
this:
Job Workcenter Name Target Actual to Date Runtime
P17554 RB Bill 8 9 6
SS AL 21.1 8.3 0.9
CT 21.1 8.3 3.4
P17575 A Tom 15.5 11.6 0.3
F Dave 20.5 25.7 7.3

After changed, we hope it could be like this:
Job Workcenter Name Target Actual to Date Runtime
P17554 RB Bill 8 9 6
Art 3
Bill
6

SS AL 21.1 8.3 0.9
CT 21.1 8.3 3.4
P17575 A Tom 15.5 11.6 0.3
F Dave 20.5 25.7 7.3
Franco 8
Tony 8
Peter 2.4
Dave 7.3

Hope this time it would be ok.
Tracey
 
Tracey said:
The samples of our report was messed up. The original one is like
this:
Job Workcenter Name Target Actual to Date Runtime
P17554 RB Bill 8 9 6
SS AL 21.1 8.3 0.9
CT 21.1 8.3 3.4
P17575 A Tom 15.5 11.6 0.3
F Dave 20.5 25.7 7.3

After changed, we hope it could be like this:
Job Workcenter Name Target Actual to Date Runtime
P17554 RB Bill 8 9 6
Art 3
Bill
6

SS AL 21.1 8.3 0.9
CT 21.1 8.3 3.4
P17575 A Tom 15.5 11.6 0.3
F Dave 20.5 25.7 7.3
Franco 8
Tony 8
Peter 2.4
Dave 7.3
Click to expand...


I think I do have at least a vague idea of the objective.
BUT, I have no idea where the data is coming from so I am
just guessing about how to get there from an unknown
starting point. Initially, I assumed that you had a basic
Many to many table relationship with Jobs related to workers
through a junction table with the date and actual time
worked. If that's not what you have then my inital response
is probably way off.

I really need to know what tables the report uses, how they
are related and what data is in each of the tables. The
main report's and subreport's record source queries are also
very important todetermining how the calculations and
subreport are arranged.
 
Tracey said:
The samples of our report was messed up. The original one is like
this:
Job  Workcenter  Name Target Actual to Date Runtime
P17554  RB      Bill         8         9             6
           SS       AL         21.1    8.3            0.9
                       CT        21.1    8.3            3.4
P17575  A       Tom       15.5    11.6          0.3
            F       Dave       20.5    25.7         7.3
Click to expand...
After changed, we hope it could be like this:
Job  Workcenter  Name Target Actual to Date Runtime
P17554  RB      Bill         8         9             6
                                                Art       3
                                                Bill
6
Click to expand...
           SS       AL         21.1    8.3            0.9
                       CT        21.1    8.3            3.4
P17575  A       Tom       15.5    11.6          0.3
            F       Dave       20.5    25.7         7.3
                                               Franco  8
                                               Tony     8
                                               Peter    2.4
                                               Dave     7.3
Click to expand...

I think I do have at least a vague idea of the objective.
BUT, I have no idea where the data is coming from so I am
just guessing about how to get there from an unknown
starting point.  Initially, I assumed that you had a basic
Many to many table relationship with Jobs related to workers
through a junction table with the date and actual time
worked.  If that's not what you have then my inital response
is probably way off.

I really need to know what tables the report uses, how they
are related and what data is in each of the tables.  The
main report's and subreport's record source queries are also
very important todetermining how the calculations and
subreport are arranged.

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -
Click to expand...

Hi Marsh,

I got all the data from the database of our ERP system through ODBC.
So it is pretty easy to get them and make a report like above. We are
using three tables from the database, and you are right, it is many to
many table relationship with jobs related to workers through a
junction table with the date and actual time worked.

The tables are:
1. Employee(gives employee name): Employee#
2. WipJobAllLab(gives the target and actual to date time for each
workcenter of each job): Job#, Operation, workcenter
3. WipLabJnl(gives on a specific day who worked on which workcenter
for which job and for how long): Employee#, workcenter, Job#,
operation

Those components I wrote down for each table were linked together,
then I made it to the report as above.

Hope I answered all your questions.

Thanks,
Tracey
 
Tracey said:
I got all the data from the database of our ERP system through ODBC.
So it is pretty easy to get them and make a report like above. We are
using three tables from the database, and you are right, it is many to
many table relationship with jobs related to workers through a
junction table with the date and actual time worked.

The tables are:
1. Employee(gives employee name): Employee#
2. WipJobAllLab(gives the target and actual to date time for each
workcenter of each job): Job#, Operation, workcenter
3. WipLabJnl(gives on a specific day who worked on which workcenter
for which job and for how long): Employee#, workcenter, Job#,
operation

Those components I wrote down for each table were linked together,
then I made it to the report as above.

Hope I answered all your questions.
Click to expand...


That does help. After reviewing your examples where you
always want to see emplyees with an actual time and
sometimes also want to see emloyees with no actual time, I
think the subreport may not have been useful after all.

If your repot's record source query inner joins all three
tables as I would expect, then I think you can get what you
want by changing both INNER JOINs to LEFT JOINs (or maybe
RIGHT JOINs?). That should get you the Employee #s for
everyone assigned to a job regardless if they have an actual
time or not.

Then use a text box (named txtTotalActual) with =Sum([actual
time]) in the job # **group header** section (can be
invisible) to calculate the total actual time. You can then
hide the employees you don't want to see by usimg code
something like this in the detail section's Format event
procedure:

Cancel = IsNull(Me.[Actual Time]) _
And (Me.txtTotalActual - Me.target < .1)
 
I got all the data from the database of our ERP system through ODBC.
So it is pretty easy to get them and make a report like above. We are
using three tables from the database, and you are right, it is many to
many table relationship with jobs related to workers through a
junction table with the date and actual time worked.
Click to expand...
The tables are:
1. Employee(gives employee name): Employee#
2. WipJobAllLab(gives the target and actual to date time for each
workcenter of each job): Job#, Operation, workcenter
3. WipLabJnl(gives on a specific day who worked on which workcenter
for which job and for how long): Employee#, workcenter, Job#,
operation
Click to expand...
Those components I wrote down for each table were linked together,
then I made it to the report as above.
Click to expand...
Hope I answered all your questions.
Click to expand...

That does help.  After reviewing your examples where you
always want to see emplyees with an actual time and
sometimes also want to see emloyees with no actual time, I
think the subreport may not have been useful after all.

If your repot's record source query inner joins all three
tables as I would expect, then I think you can get what you
want by changing both INNER JOINs to LEFT JOINs (or maybe
RIGHT JOINs?).  That should get you the Employee #s for
everyone assigned to a job regardless if they have an actual
time or not.

Then use a text box (named txtTotalActual) with =Sum([actual
time]) in the job # **group header** section (can be
invisible) to calculate the total actual time.  You can then
hide the employees you don't want to see by usimg code
something like this in the detail section's Format event
procedure:

        Cancel = IsNull(Me.[Actual Time]) _
                                                        And (Me.txtTotalActual - Me.target < .1)

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -
Click to expand...

Hi Marsh,

This report is run daily in order that the production manager knows
who does what and for how long everyday. So the employee who works on
that day would be definitely on the report. But you know one job at a
specific workcenter would be more than one day, that means those
people who worked for the same job at the same workcenter would not
show up on the report for that day. I hope you understand what I am
saying.

When the total actual time is 10% over the target time, our manager
wants to know why it is out and who did that job on that workcenter.
So he wants to list everybody who worked for that job at that
workcenter from the beginning of that job till up to date, not only
for that day. Here is a example:
Date Job Workcenter Employee Target Actual to date Runtime
Oct.1 P17123 P Nick 8 2
2
Oct.2 P17123 P Peter 8 10
8
If we run the report for Oct. 2, only Peter worked for workcenter P
for 8 hrs would show up. But compare the actual to date and target, it
is 10% over, so we want to list both Nick who worked for 2 hrs and
Peter who worked for 8 hrs. In this way, our manager don't have to go
back to the system to check who worked for this job at this workcenter
from this job begun.

We still want those employee who worked for that day show up even if
total actual time is not 10% over. On this point, I am thinking I made
you confused.

Thanks
Tracey
 
Tracey said:
Tracey said:
On Nov 4, 3:40 pm, Marshall Barton wrote:
Tracey wrote:
The samples of our report was messed up. The original one is like
this:
Job  Workcenter  Name Target Actual to Date Runtime
P17554  RB      Bill         8         9              6
           SS       AL         21.1    8.3            0.9
                       CT        21.1    8.3            3.4
P17575  A       Tom       15.5    11.6           0.3
            F       Dave       20.5    25.7          7.3
Click to expand...
After changed, we hope it could be like this:
Job  Workcenter  Name Target Actual to Date Runtime
P17554  RB      Bill         8         9              6
                                                 Art       3
                                                 Bill 6
Click to expand...
           SS       AL         21.1    8.3            0.9
                       CT        21.1    8.3            3.4
P17575  A       Tom       15.5    11.6           0.3
            F       Dave       20.5    25.7          7.3
                                                Franco  8
                                                Tony     8
                                                Peter    2.4
                                                Dave     7.3
Click to expand...
I think I do have at least a vague idea of the objective.
BUT, I have no idea where the data is coming from so I am
just guessing about how to get there from an unknown
starting point.  Initially, I assumed that you had a basic
Many to many table relationship with Jobs related to workers
through a junction table with the date and actual time
worked.  If that's not what you have then my inital response
is probably way off.
Click to expand...
I really need to know what tables the report uses, how they
are related and what data is in each of the tables.  The
main report's and subreport's record source queries are also
very important todetermining how the calculations and
subreport are arranged.
Click to expand...
I got all the data from the database of our ERP system through ODBC.
So it is pretty easy to get them and make a report like above. We are
using three tables from the database, and you are right, it is many to
many table relationship with jobs related to workers through a
junction table with the date and actual time worked.
Click to expand...
The tables are:
1. Employee(gives employee name): Employee#
2. WipJobAllLab(gives the target and actual to date time for each
workcenter of each job): Job#, Operation, workcenter
3. WipLabJnl(gives on a specific day who worked on which workcenter
for which job and for how long): Employee#, workcenter, Job#,
operation
Click to expand...
Those components I wrote down for each table were linked together,
then I made it to the report as above.
Click to expand...
Hope I answered all your questions.
Click to expand...

That does help.  After reviewing your examples where you
always want to see emplyees with an actual time and
sometimes also want to see emloyees with no actual time, I
think the subreport may not have been useful after all.

If your repot's record source query inner joins all three
tables as I would expect, then I think you can get what you
want by changing both INNER JOINs to LEFT JOINs (or maybe
RIGHT JOINs?).  That should get you the Employee #s for
everyone assigned to a job regardless if they have an actual
time or not.

Then use a text box (named txtTotalActual) with =Sum([actual
time]) in the job # **group header** section (can be
invisible) to calculate the total actual time.  You can then
hide the employees you don't want to see by usimg code
something like this in the detail section's Format event
procedure:

        Cancel = IsNull(Me.[Actual Time]) _
                              And (Me.txtTotalActual - Me.target < .1)
Click to expand...


This report is run daily in order that the production manager knows
who does what and for how long everyday. So the employee who works on
that day would be definitely on the report. But you know one job at a
specific workcenter would be more than one day, that means those
people who worked for the same job at the same workcenter would not
show up on the report for that day. I hope you understand what I am
saying.

When the total actual time is 10% over the target time, our manager
wants to know why it is out and who did that job on that workcenter.
So he wants to list everybody who worked for that job at that
workcenter from the beginning of that job till up to date, not only
for that day. Here is a example:
Date Job Workcenter Employee Target Actual to date Runtime
Oct.1 P17123 P Nick 8 2 2
Oct.2 P17123 P Peter 8 10 8
If we run the report for Oct. 2, only Peter worked for workcenter P
for 8 hrs would show up. But compare the actual to date and target, it
is 10% over, so we want to list both Nick who worked for 2 hrs and
Peter who worked for 8 hrs. In this way, our manager don't have to go
back to the system to check who worked for this job at this workcenter
from this job begun.

We still want those employee who worked for that day show up even if
total actual time is not 10% over. On this point, I am thinking I made
you confused.
Click to expand...


So, when you runt the report for 10/2, the data for work
done on 10/1 is not available to the reprot (i.e. it's been
filtered out byt the record source query), right??

If that's correct, then you must modify the report's record
source query so the report can get its hands on and display
the required 10/1 records. OR, we go back to using a
subreport that has a record source query that selects the
required older records. Either way you have a query issue
to solve before you can worry about how make the report(s?)
display the records.
 
Tracey said:
Tracey wrote:
On Nov 4, 3:40 pm, Marshall Barton wrote:
Tracey wrote:
The samples of our report was messed up. The original one is like
this:
Job  Workcenter  Name Target Actual to Date Runtime
P17554  RB      Bill         8         9              6
           SS       AL         21.1   8.3            0.9
                       CT        21.1    8.3            3.4
P17575  A       Tom       15.5    11.6           0.3
            F       Dave       20.5    25.7          7.3
After changed, we hope it could be like this:
Job  Workcenter  Name Target Actual to Date Runtime
P17554  RB      Bill         8         9              6
                                                 Art       3
                                                 Bill       6
           SS       AL         21.1   8.3            0.9
                       CT        21.1    8.3            3.4
P17575  A       Tom       15.5    11.6           0.3
            F       Dave       20.5    25.7          7.3
                                                Franco  8
                                                Tony     8
                                                Peter    2.4
                                                Dave     7.3
I think I do have at least a vague idea of the objective.
BUT, I have no idea where the data is coming from so I am
just guessing about how to get there from an unknown
starting point.  Initially, I assumed that you had a basic
Many to many table relationship with Jobs related to workers
through a junction table with the date and actual time
worked.  If that's not what you have then my inital response
is probably way off.
I really need to know what tables the report uses, how they
are related and what data is in each of the tables.  The
main report's and subreport's record source queries are also
very important todetermining how the calculations and
subreport are arranged.
I got all the data from the database of our ERP system through ODBC.
So it is pretty easy to get them and make a report like above. We are
using three tables from the database, and you are right, it is many to
many table relationship with jobs related to workers through a
junction table with the date and actual time worked.
The tables are:
1. Employee(gives employee name): Employee#
2. WipJobAllLab(gives the target and actual to date time for each
workcenter of each job): Job#, Operation, workcenter
3. WipLabJnl(gives on a specific day who worked on which workcenter
for which job and for how long): Employee#, workcenter, Job#,
operation
Those components I wrote down for each table were linked together,
then I made it to the report as above.
Hope I answered all your questions.
That does help.  After reviewing your examples where you
always want to see emplyees with an actual time and
sometimes also want to see emloyees with no actual time, I
think the subreport may not have been useful after all.
If your repot's record source query inner joins all three
tables as I would expect, then I think you can get what you
want by changing both INNER JOINs to LEFT JOINs (or maybe
RIGHT JOINs?).  That should get you the Employee #s for
everyone assigned to a job regardless if they have an actual
time or not.
Then use a text box (named txtTotalActual) with =Sum([actual
time]) in the job # **group header** section (can be
invisible) to calculate the total actual time.  You can then
hide the employees you don't want to see by usimg code
something like this in the detail section's Format event
procedure:
        Cancel = IsNull(Me.[Actual Time]) _
                              And (Me.txtTotalActual - Me.target < .1)
Click to expand...
Click to expand...
This report is run daily in order that the production manager knows
who does what and for how long everyday. So the employee who works on
that day would be definitely on the report. But you know one job at a
specific workcenter would be more than one day, that means those
people who worked for the same job at the same workcenter would not
show up on the report for that day. I hope you understand what I am
saying.
Click to expand...
When the total actual time is 10% over the target time, our manager
wants to know why it is out and who did that job on that workcenter.
So he wants to list everybody who worked for that job at that
workcenter from the beginning of that job till up to date, not only
for that day. Here is a example:
Date    Job   Workcenter  Employee  Target Actual to date Runtime
Oct.1   P17123   P           Nick           8           2              2
Oct.2   P17123   P           Peter          8          10             8
If we run the report for Oct. 2, only Peter worked for workcenter P
for 8 hrs would show up. But compare the actual to date and target, it
is 10% over, so we want to list both Nick who worked for 2 hrs and
Peter who worked for 8 hrs. In this way, our manager don't have to go
back to the system to check who worked for this job at this workcenter
from this job begun.
Click to expand...
We still want those employee who worked for that day show up even if
total actual time is not 10% over. On this point, I am thinking I made
you confused.
Click to expand...

So, when you runt the report for 10/2, the data for work
done on 10/1 is not available to the reprot (i.e. it's been
filtered out byt the record source query), right??

If that's correct, then you must modify the report's record
source query so the report can get its hands on and display
the required 10/1 records.  OR, we go back to using a
subreport that has a record source query that selects the
required older records.  Either way you have a query issue
to solve before you can worry about how make the report(s?)
display the records.

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -
Click to expand...
Hi Marsh,

You are correct. The date for 10/1 is filtered out. Because if the
total actual time is not over 10%, we don't want those data for 10/1
show up on this report. Only when the total actual time is 10% more
than target, we want the data for 10/1 show up. So I am thinking I do
need a subreport, is that right? If a subreport needed, how can I
connect the main report with the subreport? is that same with what you
told me before and just link job and workcenter between those two
report? and do I need to put some codes at event procedure at the
details? Please help.

Thanks,
Tracey
 
Tracey said:
You are correct. The date for 10/1 is filtered out. Because if the
total actual time is not over 10%, we don't want those data for 10/1
show up on this report. Only when the total actual time is 10% more
than target, we want the data for 10/1 show up. So I am thinking I do
need a subreport, is that right? If a subreport needed, how can I
connect the main report with the subreport? is that same with what you
told me before and just link job and workcenter between those two
report? and do I need to put some codes at event procedure at the
details?
Click to expand...


Before going into the report, get the queries to generate
the needed records. It seems like that may be a project in
itself.

First, you need to select all the Job/Workcenters that have
work during the specified date (range?). How can you tell
when a job is finished?

Then it needs to select all the employees for those jobs
regardless of the days they worked on the job.

Since you have somehow managed to calculate the total time
worked on a job over all its days, I suspect that you have
done something to deal with most of that.
 
Back
Top