"Incremental Values from a Table"

[Guest]

I have a table with four fields: Project,Month, HoursSpentToDate,
WorkHrsThisMonth.

Project is a project name
MonthNo is the sequential number of each month in the calendar. There can
be any number of months in a project and a project can start on any month.
HoursSpentToDate is the cumulative number of hours expended on a project by
any number of employees as of the end of each month. This value will be ever
increasing for each higher MonthNo on a project.
HrsThisMonth is the constant precalculated number of work hours in a given
month based on 8hrs/workday times the number of workdays within a given month.

I want to determine how many people are spending time on the project in a
month. by dividing incremental hours spent each month by HrsThisMonth. Is
there a simple method within query design to calculate the incremental values
spent each month (this month cumulative minus last month cumulative)? Or do
I need to write a crosstab query and then a select query on the CTQ to
subtract values of one month field from its successor? This is awkward since
the calendar end will change as time passes.

Thanks for your assistance.

 

[Guest]

Try this:

SELECT Project, Month,
(HoursSpentThisMonth
–NZ(SELECT MAX(HoursSpentThisMonth)
FROM YourTable AS T2
WHERE T2.Project = T1.Project
AND T2.Month < T1.Month),0))
/WorkHoursThisMonth AS PeopleCount
FROM YourTable As T1
ORDER BY Project, Month;

This will only work if all months are in the same year of course. If a
project spans the end of the year you would need a column for the year as
well.

BTW I'd avoid names like Month or Year for columns. These are the names of
built in functions, and could get confused with them in some circumstances.
Use names like ProjectMonth instead to avoid any ambiguity.

Ken Sheridan
Stafford, England

 

[Guest]

Ken,
Thanks for the quick response.

The Nz function caused a problem as the query kept crashing Access. I tried
modifying the query in design view. When I added another field to refine it
a bit the query worked. I found a null value in the first field and upon
examination found that the Nz function was gone. When I try to reinsert it,
it disappears when I hit Enter. I don't know why this function isn't accepted
by Access.

In any case, your solution has proven to be effective. I confirmed it by
taking the 160 out of the formula to see what I got. The results are correct.

FOLLOW-UP QUESTION: I've never built a query like this with an embedded
query within a field name. How can I look this up in Help for a description
of this feature, its applications, etc.?

Thanks again for your help.

Regards,
Len at BV

 

[Guest]

Len:

In Access 2002, which I'm using, you'll find a topic on subquries by
driiling down in Help as follows:

Microsoft Jet SQL Reference
… Microsoft Jet SQL Reference
……Data manipulation language
………SQL Subqueries

A few useful links on subqueries are:


http://office.microsoft.com/en-gb/assistance/HP010322711033.aspx


http://www.onlamp.com/pub/a/onlamp/2001/07/26/aboutSQL.html


http://www.marc-grange.net/SQL5_en.htm


http://www.informit.com/articles/article.asp?p=26856&rl=1


http://ocw.mit.edu/NR/rdonlyres/Urb...637FE-1019-4EAA-9A2A-C95B440A7D72/0/lect4.pdf


http://www.cs.uwaterloo.ca/~david/cs338/lect-SQL3.pdf


Ken Sheridan
Stafford, England