Grouping data and calculating a result

[scott_taylor]

Please help me setup my Formulas to get what I am trying to achieve. I wish I could paste screen shots to help explain this but I cannot figure out how to do that.

I have 2 big data sources, that I am trying to cross reference to get an output result.

The first data source is Hours Worked.  This is a report that contains employee hours worked with tags for:

1. date (day)
2. WBS element (work code)
3. Foreman Name
4. Hours

The second data source is Quantity report.  This is a report stating what work was completed and contains some common tags as the hours report.  It has tags for:

1. date (day)
2. WBS element (work code)
3. Foreman Name
4. Quantity Earned
5. Tag number (this identifies a component that is part of the WBS code.  For example, the WBS element could be "build walls", and the Tag number identifies the individual walls that make up the total WBS element.

The first step I want to do is group the data in both tables, by week.  I have setup a Calendar table to do this.

The second step I want to do is group into a table the following information from the Quantity report:

• group the sum of "Quantity Earned" by
• week
• WBS element
• Foreman Name
• Tag number

Then next step I want to do is group the Hours from the Hours Worked report:

• group the sum of "Hours" by
• week
• WBS element
• Foremane Name

My modeled relationships look like this right now;

Once I have those grouped hours, I need to divide them by the Total Quantity earned in the table we made for the Quantity earned grouping (where the week/WBS element and foreman name match).  This result will be the hours per quantity earned, by week, by foreman and by WBS.

The final step is to then take that result of hours/Total quantity earned and multiply by all the Tag number quantities in the week - to get a reult of hours worked, by tag number (by week, WBS and foreman name).

I think I am very close - please help - let me know how I can show you my screen shots to help understand more what I am doing!

I have made 2 grouped tables (1 from each report) that I think might get me closer, but am not using them in my relationships yet.  Perhaps they could be used to help?  They look like this:

[Nathaniel_C]

@scott_taylor ,

The best way is use the snipping tool and then post here using photos.

You can also copy and paste your table into Code above </>
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel