Hi, I am struggling with pulling data for multiple projects and then standardizing the data so it can be used in modeling and comparisons.
Overview: We are building a modeling system that allows the user to select multiple projects. The system then pulls the project schedule duration and actual labor hours of employees for each project. The system will use the average of the projects as the base model to be applied to other projects. Since the projects selected don’t all start on the same day we want to turn dates (Month/Year) into a count so that we can compare month 1 in all projects. In the simplest terms I have 3 Columns of data:
1. Identifies the Project
2. Identifies the Year & Month
3. Provides the labor hours for the Project, Year & Month
What I am trying to do is turn the Year & Month into a count for each project. Example: Project 1 has duration of 41 Months and Project 2 has duration of 33 Months and Project 3 has duration of 60 months. When we count the months for each project I will be able to provide Average hours for month 1 based on the first month the project has labor hours.
Is there a good way to accomplish this?
Rabbit, I found that when I multiplied by 4 the next year in the same project I would be back at 1 instead of 13.
When I multiplied the year by 12 and added the month then subtracted the Min result and added 1 I got the results I was looking for. Example: 2010*12+5 = 24124-24125+1 = 1. When you get into 2011 the calculation still works 2011*12+5 = 24137-24125+1 = 13.
Thank you for your assistance!
7  1265 
We would need to see sample data and results along with the table metadata.
Rabbit, I have attached an excel worksheet that contains the Raw Data (columns A-D); the Raw Data with the field I am trying to calculate highlighted (columns F-J); and a sample pivot table/chart that is the result I am trying to get to.
This is the first step in the chart that the users want. I am not sure if I can provide the final results in Access or if I will need to take the data to Excel to get the results I need, but I know the whole project hinges on being able to compare data in the first month of one project with data in the first month of another project.
Thank you for your willingness to look at this with me.
Sorry but our firewall blocks file downloads. Please post a sample of data within the thread itself.
If you take the year and multiply by 4 then add the month, that gives you an incremental count of months from year 0. So for row one that's 2010 * 4 + 5 = 8045. For row 2 that's 2010 * 4 + 6 = 8046.
Then for each row, you subtract the minimum count of months per project and add 1. That gives you your mcount field. The minimum count of months for project P39 is 8045. So for row 1 that's 8045 - 8045 + 1 = 1. For row 2 that's 8046 - 8045 + 1 = 2.
For the final result, you do a crosstab query using that new mcount field.
Rabbit, I found that when I multiplied by 4 the next year in the same project I would be back at 1 instead of 13.
When I multiplied the year by 12 and added the month then subtracted the Min result and added 1 I got the results I was looking for. Example: 2010*12+5 = 24124-24125+1 = 1. When you get into 2011 the calculation still works 2011*12+5 = 24137-24125+1 = 13.
Thank you for your assistance!
Sorry, it should have been 12. I have no idea why I said 4. 12 months to a year, I must have been thinking of quarters or something.
Glad you got it working. Good luck with the rest of your project.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: DKode |
last post by:
I have 2 projects in a solution:
Web
EmployeeManagement
The namespace for Web is : CompanyName.Hybrid.Web
The namespace for EmployeeManagement is just : CompanyName.Hybrid
...
|
by: Zero.NULL |
last post by:
Hi,
We are using Month-year tables to keep the history of long transaction
of our application. For example:
We capture the details of a certain action in table...
|
by: cybertof |
last post by:
Hi !
What is the common use of sharing a single .cs across multiple project
files ?
I think it's to share common classes between projects.
I have actually a .cs file shared accross multiple...
|
by: Rathtap |
last post by:
Suppose I have the following enum that I need in multiple projects:
enum Ratings{Poor=1,Satisfactory,Medium,Good,Excellent};
Instead of defining it in every project that needs it, I want to...
|
by: TaeHo Yoo |
last post by:
Hi all,
I have a solution which contains multiple projects.
Those multiple projects should share the same session. For example,
users login, create the session for users then these session...
|
by: Stanley Glass |
last post by:
Ok I have our Intranet up and I want to add a new project to run under the
intranet, making use of its sessions.However, when I move the new project to
the dev system (removing the globabl.asax and...
|
by: Brian Henry |
last post by:
is there any way to make an assembly file accessable across multiple
projects (we have over 20 that need the info all to be the same at build
version, title, desc, copyright,etc) i saw MS did this...
|
by: sonali_reddy123 |
last post by:
Hi all,
I have developed an application in which there are uptill now 12
projects and few of them are
dependent on each other.
The problem is I am not able to manage the references across...
|
by: ryan.mclean |
last post by:
Hi all,
I am new to 2.0 and themes. i would like to make all of my web projects
have the same themes when I create a new project. Is this possible?
Thanks and have a great day,
Ryan
|
by: Fidencio |
last post by:
I have multiple projects that each have their own "My.Settings" configuration
files.
Is it possible to access the my.settings variables from other project's
setting files?
Let me give an...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
| |
