weekly calc question

[DEVOURU]

There are 35 hours in a week for each part. I have partA that takes 100
hours to complete, partB takes 39, partC takes 20. So, how would you suggest
to have this:

Week 1
partA labor 35
partB labor 35
partC labor 20

Week 2
partA labor 35
partB labor 4

Week 3
partA labor 30

Thankx in Advance

-JS

 

[Marshall Barton]

There are 35 hours in a week for each part. I have partA that takes 100
hours to complete, partB takes 39, partC takes 20. So, how would you suggest
to have this:

Week 1
partA labor 35
partB labor 35
partC labor 20

Week 2
partA labor 35
partB labor 4

Week 3
partA labor 30

What data fields do you have in the report's record source
query? What kind of grouping does the report have?

Without that information, all I can do is state a general
rule for reports - the key to most reports is to design the
report's record source query to get the right data into the
appropriate records.

 

[DEVOURU]

Thankx for the reply. I have a working report that groups by the week,
printing the Week Number in the header. Detail section is basically
[labor]*[qty] labeled TlLabor. I'm trying to figure how to do something like

IIF([TlLabor]>35, add excess to next week, [TlLabor])

Kinda like running sum, with any hours over 35.

Thankx again, -JS

 

[Marshall Barton]

Sorry, I should have been more specific. I need to see the
report's record source query. If it's just a table, then I
need a list of the fields (with brief explanation).

This problem is fairly tricky, so I have to figure out a
rather complex query to manage some made up records.
--
Marsh
MVP [MS Access]

Thankx for the reply. I have a working report that groups by the week,
printing the Week Number in the header. Detail section is basically
[labor]*[qty] labeled TlLabor. I'm trying to figure how to do something like

IIF([TlLabor]>35, add excess to next week, [TlLabor])

Kinda like running sum, with any hours over 35.

Thankx again, -JS

"Marshall Barton"wrote:
What data fields do you have in the report's record source
query? What kind of grouping does the report have?

Without that information, all I can do is state a general
rule for reports - the key to most reports is to design the
report's record source query to get the right data into the
appropriate records.

 

[DEVOURU]

You were specific enough, I just didn't completely read your reply. It is
somewhat a complex query. I have tried to keep the subject as simple as
possible. Is there an easy way to get you the info you look for?

-JS

Sorry, I should have been more specific. I need to see the
report's record source query. If it's just a table, then I
need a list of the fields (with brief explanation).

This problem is fairly tricky, so I have to figure out a
rather complex query to manage some made up records.
--
Marsh
MVP [MS Access]

Thankx for the reply. I have a working report that groups by the week,
printing the Week Number in the header. Detail section is basically
[labor]*[qty] labeled TlLabor. I'm trying to figure how to do something like

IIF([TlLabor]>35, add excess to next week, [TlLabor])

Kinda like running sum, with any hours over 35.

Thankx again, -JS

DEVOURU wrote:

There are 35 hours in a week for each part. I have partA that takes 100
hours to complete, partB takes 39, partC takes 20. So, how would you suggest
to have this:

Week 1
partA labor 35
partB labor 35
partC labor 20

Week 2
partA labor 35
partB labor 4

Week 3
partA labor 30
"Marshall Barton"wrote:
What data fields do you have in the report's record source
query? What kind of grouping does the report have?

Without that information, all I can do is state a general
rule for reports - the key to most reports is to design the
report's record source query to get the right data into the
appropriate records.

 

[Marshall Barton]

I really don't want to dig into all the details of a complex
situation. Let me try to describe what you have to do and
maybe you can take it from there.

What you need is to "manufacture" records that break the
labor amounts into weekly chunks. This is done by using a
new table named Numbers that only has one field named
Number. Populate the table with records containing the
numbers 1 to more than the maximum number of weeks any labor
amount is ever going to have.

Usually(?), you can create a new query based on your
existing query and this new table with **no** join line
between them (a cross product of the two data sets).

SELECT Numbers.Number,
query.flda, query.fldb, . . . ,
IIf(query.Labor >= (Numbers.Number * 35),
35, query.Labor Mod 35) As WeekLabor
FROM query, table
WHERE Numbers.Number <= (query.Labor + 34) \ 35

Change the report's grouping on week to the expression
=WeekNumber + Number - 1

Good luck,
--
Marsh
MVP [MS Access]

You were specific enough, I just didn't completely read your reply. It is
somewhat a complex query. I have tried to keep the subject as simple as
possible. Is there an easy way to get you the info you look for?

Sorry, I should have been more specific. I need to see the
report's record source query. If it's just a table, then I
need a list of the fields (with brief explanation).

This problem is fairly tricky, so I have to figure out a
rather complex query to manage some made up records.

Thankx for the reply. I have a working report that groups by the week,
printing the Week Number in the header. Detail section is basically
[labor]*[qty] labeled TlLabor. I'm trying to figure how to do something like

IIF([TlLabor]>35, add excess to next week, [TlLabor])

Kinda like running sum, with any hours over 35.


DEVOURU wrote:

There are 35 hours in a week for each part. I have partA that takes 100
hours to complete, partB takes 39, partC takes 20. So, how would you
suggest
to have this:

Week 1
partA labor 35
partB labor 35
partC labor 20

Week 2
partA labor 35
partB labor 4

Week 3
partA labor 30

"Marshall Barton"wrote:
What data fields do you have in the report's record source
query? What kind of grouping does the report have?

Without that information, all I can do is state a general
rule for reports - the key to most reports is to design the
report's record source query to get the right data into the
appropriate records.

 

[DEVOURU]

I will give this a try. Thank you for your time. I figured it was going to
be ugly, but I was hoping someone had already worked on something similar.

-JS

I really don't want to dig into all the details of a complex
situation. Let me try to describe what you have to do and
maybe you can take it from there.

What you need is to "manufacture" records that break the
labor amounts into weekly chunks. This is done by using a
new table named Numbers that only has one field named
Number. Populate the table with records containing the
numbers 1 to more than the maximum number of weeks any labor
amount is ever going to have.

Usually(?), you can create a new query based on your
existing query and this new table with **no** join line
between them (a cross product of the two data sets).

SELECT Numbers.Number,
query.flda, query.fldb, . . . ,
IIf(query.Labor >= (Numbers.Number * 35),
35, query.Labor Mod 35) As WeekLabor
FROM query, table
WHERE Numbers.Number <= (query.Labor + 34) \ 35

Change the report's grouping on week to the expression
=WeekNumber + Number - 1

Good luck,
--
Marsh
MVP [MS Access]

You were specific enough, I just didn't completely read your reply. It is
somewhat a complex query. I have tried to keep the subject as simple as
possible. Is there an easy way to get you the info you look for?

Sorry, I should have been more specific. I need to see the
report's record source query. If it's just a table, then I
need a list of the fields (with brief explanation).

This problem is fairly tricky, so I have to figure out a
rather complex query to manage some made up records.


DEVOURU wrote:

Thankx for the reply. I have a working report that groups by the week,
printing the Week Number in the header. Detail section is basically
[labor]*[qty] labeled TlLabor. I'm trying to figure how to do

something
like

IIF([TlLabor]>35, add excess to next week, [TlLabor])

Kinda like running sum, with any hours over 35.


DEVOURU wrote:

There are 35 hours in a week for each part. I have partA that takes 100
hours to complete, partB takes 39, partC takes 20. So, how would you
suggest
to have this:

Week 1
partA labor 35
partB labor 35
partC labor 20

Week 2
partA labor 35
partB labor 4

Week 3
partA labor 30

"Marshall Barton"wrote:
What data fields do you have in the report's record source
query? What kind of grouping does the report have?

Without that information, all I can do is state a general
rule for reports - the key to most reports is to design the
report's record source query to get the right data into the
appropriate records.