Workload determination

[paul]

Hi, the data I have is a persons name, a title (a journal issue, for
argument's sake), the time an
edit job takes (can be 2 or 10 weeks), a job name, a start date, an
end date and the number of
pages to be edited. From this data, I need to create an overview per
title, per person, per week of
the number of pages this person is responsible for. A grand total
should list the person's total
number of pages per week (so a total of all the titles he/she is
responsible for). Mind you, the
number of pages is simply copied throughout the weeks, so for each
title, every week should show the same amount of pages. Example: in
week 1 person X has one product (50 pages) that takes 2 weeks to
complete. In week two, a second product is added (25 pages) that takes
10 weeks to complete, in week 3 no product is added and in week 4
another product is added (30 pages), this time for 10 weeks. A report
would have: the first week to show 50 pages, the second week to show
50 + 25= 75 pages, week 3 to show 25 pages again (first product is
completed) and week 4 to show 55 pages. There's got to be a way to do
this but I'm lost. Can someone help? Thanks, Paul

 

[Guest]

How are you defining the date range of your report? And what layout are you
looking for.. 1 row per week or 1 column per week?

 

[paul]

Hi Lance, thanks. I'm planning 1.5 years ahead, the lay out would be 1
column per week. Maybe I should go for 1 row per week (I'm only
tracking about 10 people), or maybe I should export the result to
Excel.

 

[Guest]

I'm usually not a fan of the "just export it to excel" option.. but if you
really want to display 1.5 years of weekly data ( 78 columns ) that may well
be your only option.

 

[paul]

Yes I know, it's not ideal. I might go for the weeks in the row. That
still leaves me with the question how to arrange the data. Do you have
any idea? Thanks, Paul

 

[Guest]

This function will build a table by column, broken down by week.

Sub buildTable()
Dim i As Integer
Dim sql_string As String

sql_string = "SELECT Table1.F1, Table1.D1, Table1.D2"
For i = 1 To 78
sql_string = sql_string & ", IIf(DateAdd('ww'," & Str(i) &
",Date())>=[D1] And DateAdd('ww'," & Str(i) & ",Date())<=[D2],[F4],0) AS
Week" & Trim(Str(i))
Next i

sql_string = sql_string & " INTO outputTable FROM Table1"

CurrentDb.Execute (sql_string)
End Sub

You will need to edit it to include the fields and table you need. In this
example D1 is start date and D2 is end date, F1 is an ID field. It breaks
the data down by week starting from the current date.

 

[Larry Daugherty]

For whatever reason, I don't see the Original Poster's posts. This is
directed at "Paul"

It kind of sounds like your data is arranged as a single flat source.
For best use in Access, it should be in at least three tables
something like: Person, Publication, Task

The Person and Publication tables would grow relatively slowly. The
Task table would have one record for every assignment and would grow
much more quickly.

Establishing the proper relationships would then yield a schema that's
well suited to creating just about any kind of report you wish.

By organizing your schema as above, the Reports would naturally and
easily be designed to show one row per unit of time; you have chosen
"Week" so I'd imagine that you assign tasks on the basis of "week
number".

In your reports, it would be easy to have one report that organizes
and reports each person's load by that week number. It would also be
able to create another report that is organized by Person and shows
that person's load for every week between two selected dates.

Since I don't see your posts in my offline reader, I invite, *in this
instance only*, email correspondence if you would like more help/ideas
from me on this issue. If we do correspond, I will post back here
with the results.

HTH

 

[paul]

Hi Larry, thanks for your concerns. There are no other posts. The flat
source you are referring to is actually a (made) table, showing the
result of some queries on imported tables from other applications. I
omitted this for clarity reasons and because I have no influence ion
the contents of these original tables. thanks again, Paul

 

[paul]

Hi Lance, thanks very much. I will try this out tonight and let you
know. Paul

 

[Guest]

[F4] being the field to sum up.. if that wasn't clear.