If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
"Incremental Values from a Table"
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. |
#2
|
|||
|
|||
"Incremental Values from a Table"
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 "Len at BV" wrote: 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. |
#3
|
|||
|
|||
"Incremental Values from a Table"
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 "Ken Sheridan" wrote: 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 "Len at BV" wrote: 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. |
#4
|
|||
|
|||
"Incremental Values from a Table"
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 a http://office.microsoft.com/en-gb/as...322711033.aspx http://www.onlamp.com/pub/a/onlamp/2.../aboutSQL.html http://www.marc-grange.net/SQL5_en.htm http://www.informit.com/articles/art...p?p=26856&rl=1 http://ocw.mit.edu/NR/rdonlyres/Urba...72/0/lect4.pdf http://www.cs.uwaterloo.ca/~david/cs338/lect-SQL3.pdf Ken Sheridan Stafford, England "Len at BV" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Using the values of a crosstab query to create new values | relacy | Running & Setting Up Queries | 1 | December 15th, 2005 10:29 PM |
Keeping a cell value constant trhoughout a list of values | borikua05 | Worksheet Functions | 2 | December 3rd, 2005 03:03 PM |
default values in queries | Jeff B | Running & Setting Up Queries | 11 | March 3rd, 2005 12:49 AM |
Average only some values | Andreas | Worksheet Functions | 2 | February 18th, 2004 10:56 AM |
finding distinct values from two lists | Frank Kabel | Worksheet Functions | 0 | February 14th, 2004 12:41 AM |