Proper Table Structure for a Hierarchy (1 Viewer)

Nothing attached to

clarify your ramble

Click to expand...

see these for ideas
http://www.utteraccess.com/wiki/ind...rarchical_Data_Structure&diff=3261&oldid=3233

https://www.experts-exchange.com/articles/2153/Managing-Trees-and-Hierachies-in-Access.html

. Each level of the budget (Status,Phase,Activity) could have dollars or Start/End dates associated with them

Click to expand...

That doesn't sound right. Please give me an example where the Phase Start/End date wouldn't be the earliest/latest Activity Date attached to it. Also, a Phases budget just be the Sum of all its activities budget? Please provide example data to demonstrate.

View attachment Example.accdb

Sorry just got back.

As for an example:
Project X has a Planning period, Design time, Construction, and Post-Occupancy work.

Those are four of the statuses used, and each has separate events under them. I'll just do a bit to clarify.

Status
--- Phase
------- Activity

Design
--- Development
------- Site Visit $500, 4/22/17
------- Develop Preliminary Plan $1500, 4/25/17-6/1/17
------- Draft Statement of Work $500, 6/15/17

--- Procurement
------- Contracting Review/Authorization
------- Solicit Bids
------- Award Contract

When a user creates an initial draft budget, they don't really know what specific activities might be required for a project, so a quick estimate might just have $10,000 attached to Design.

As they gain more knowledge of the project, more data is added. This could be bookend dates, dollars, etc.

We can roll dollars/dates up since the cost of the line items will total their phase or status...bookend dates of each activity would fall within the bookends of the phase, like MS Project. But if the data is stored in different tables how do you keep that consistent, etc.

Overall Development should have a total budget of $2500 and run between 4/22/17 to 6/15/17. If a user edited the Site Visit to cost more or last longer, the data upstream of it should adjust accordingly.

JDraw,

I've looked at the example files and read the information with them.

I'm trying to picture how I would adopt this table structure since it does sound appropriate for me.

How would I manage the relationship between Status etc?

So is the budget line item data best flattened into one table that has all the values of Status, Phase, Activity, or three hierarchal tables using FKs?

Click to expand...

Yes.

Your auditors and accountants will want the details. Your management will want the summary. Your engineers will want something in between, perhaps broken out by performing group / department.

This is a case for one of the "Old Programmer" rules: Access won't tell you anything you didn't tell IT first (or at least tell it HOW to tell you). The practical translation of that advice is simple: Decide what you want to get out of this beastie and be sure that you are getting data needed to support those outputs. Sometimes that means working backwards from requirements.

I'm leaning towards the full hierarchical structure where each layer adds another key.


It doesn't matter if you have standardized statuses, phases, or activities that can be plucked from a list. The code for that individual items could be just a code number taken from a list of options (and thus offer a secondary or translation relationship to identify details about the item in question). This is where queries and relationships will save your hide. They can build the joins for you if you define the relationships correctly.

If you relate each record type to its hierarchical parent then a bunch of queries involving a GROUP BY clause will help you evaluate things more quickly.

For instance, let's say you wanted to detect a line-item that has a date after the parent item's projected end date. You would include a Yes/No field in EACH item so that you could run tests on dates. For example,


Lot of typing to be had, perhaps, but strict hierarchies lend themselves to this kind of joining pretty easily. (Easy - but tedious.)

Thank you that is a very helpful explanation. I had never really thought about having an all purpose y/n field like that.

For the structure, are you indicating that each level would be its own table (5 data tables and some value tables), or have one data table with all of the foreign key fields in it? Sorry, I'm just trying to picture this. For some reason my brain does not want to visualize this.

View attachment Example.accdb

I updated my example dB to start trying to apply your description, but I think I'm off track somewhere

I am not talking about one table. From the context of your discussion, the "properties" of each level differ and at least some of them are predictable elements. Like in Design status you have Development and Procurement phases, which have comment elements that represent either roll-ups from or limits to the items of the next level down in your pyramid.

Also you might wish to have more than one marker depending on how many different things you can test for compliance at each level. This is up to you, of course, but there is no reason you couldn't have a cash flag and a schedule/date flag just based on the two things you named earlier.

Normally I would NOT recommend this structure but I have worked in an environment that was at least similar. It IS possible to have roll-ups of the information at the next lower level, but you stated that sometimes you put estimates in a record and see if you can make the next level down conform to that number, so the roll-up would be a comparison to the original estimate. Therefore, normalization is not being violated because the two items you would compare differ in the time at which they were created (and in the method of creation as well since one is an estimate and one is a measurement.)

Unfortunately, due to issues I've had in the past with downloading other people's samples, I don't download databases. Other folks here might do so but once bitten, twice shy as the old phrase goes.

I did look at your structure and it is incorrect. I'm basing that on my knowledge in general, not on my understanding of your database (which I don't have). I am looking at your relationship tool and the key violation I see is multiple paths between tables.

For example, between tblBudgetStatus and tblActivities I can trace 4 paths. that is incorrect, there should only be 1 way to get from tblBudgetStatus to tblActivities. I don't know what that path should be in your instance, but there should only be one.

Again, I think you should budget only at the lowest level (tblActivities) and then roll it up from there whenever you want to see the budget at a higher level.

Lastly, the concept I cannot understand is your Status. You refer to Status as an object in your database (putting it among Phase and Project and Activity), but to me Status is an attribute. It seems to be outside those objects and modifying them: A Phase can be in X status, a Project is in the Y status, etc.

That makes it seem like a Status should also be applied at the lowest level (Activity) and then rolled up similar to the budget.

Thanks for the replies! A lot of my trouble with this are the constraints the company wants...they of course want easy scalable data collection that takes little work and provides excellent metrics. I am also stuck with the terminology given to me, so I understand the confusion, particularly with Status.

To this organization, activities are specific events like "install HVAC unit", which happen during a phase of the project like "construction", which are grouped into categories they call a status.

The concept behind this database is that we need scalable project tracking…in some cases from a one line budget, all the way up to a few hundred line items, ditto for dates.

We may have a project that simply needs to cut a check for $1000 and the user enters a brief date period and that single dollar figure.

The next project though, may require very detailed accounting for compliance scrutiny, outside accounting audits, etc., so the entry system needs to be able to scale deeper.

Poor terminology aside, many PM packages allow this kind of data collection, but how do you properly build a data structure for it?

The end game for management is:
• The ability to pull up a list of projects and see “expected cost vs actual cost”, “expect timeframe vs actual”.
• The list of projects should have a “status” field that is an indication of where the project is in its lifecycle.
• The ability to drill into a project so that failure points can be identified and examined.
• The ability to stage gate activities with approvals.

The structure that got defined (Status/Phase/Activity) basically represents that scaling of details. A simple project enters values at the Status level and goes no further. A single (or a very small amount) line to contain a cost figure and some dates.

A very complex project would need all specific work mapped down to the Activity level with dates and dollars all along the tree.

Attached is a screenshot of the current structure.
tblStatus, tblPhase, tblActivitity are just data source tables for field names...status ID 3 is "Construction" for example. Those IDs are saved into the tblBudgetStatus for a project. Not all projects will have all statuses, etc.

plog said:

That makes it seem like a Status should also be applied at the lowest level (Activity) and then rolled up similar to the budget.

Click to expand...

One of my attempts was done this way, but was rejected by management because it required users to log even simplistic projects down to a high level of detail. Some projects we really don't know (or care) about specific activities, just that it gets done.

Sorry to keep posting the same idea, but I feel you may not completely grasp that it will accomplish what you want. Let me demonstrate it with data using just Project->Phase->Activity to track budget and deadlines for 2 projects--one with multiple activities, one with just one activity:

tblProject
ProjID, ProjDescription
1, Pay 2016 Property Taxes
2, Build Tool Shed

tblPhase
PhaseID, ProjID, PhaseDescription
1, 1, Pay 2016 Property Taxes
2, 2, Design
3, 2, Order Materials
4, 2, Construction

tblActivities
ActivityID, PhaseID, ActivityDescription, Deadline_Due, Deadline_Actual, Cost_Budget, Cost_Actual
1, 1, Pay 2016 Property Taxes, 12/31/2016, 12/21/2016, 3800, 3815.46
2, 2, Get Customer Specifications, 1/1/2017, 1/8/2017, 100, 37.50
3, 2, Send to architecture firm, 1/8/2017, 1/10/2017, 1500, 1407.99
4, 2, Finalize plans and permits, 2/1/2017, 1/31/2017, 450, 512.37
5, 3, Order foundation, 2/1/2017, 1/31/2017, 2300, 2219.47
6, 3, Order lumber, 2/1/2017, 1/31/2017, 800, 1108.12
7, 4, Foundation pour, 3/1/2017, 3/14/2017, 600, 776.88
8, 4, Frame building, 3/8/2017, 4/1/2017, 1600, 1522.77
9, 4, Roofing, 3/15/2017, , 500, ,


Now, when I want to report on budget or deadlines I just roll up everything from the Activities table. It works no matter how in-depth a project is.

I've get what you are going for - doing all the dates and funds at the activity level rolls up fine, and it does in my current dB.

I think the issue is more of a perception problem from my users. The PM doing your first example is not willing to expand the Phase down to that single Activity. I kid you not, that is a constraint placed on me. They want to type in their $3800 tax bill without diving into any hierarchies...that means not expanding down to the Activity.

The user will never click the "+" to expand the Phase, they just want to add money at that higher level. Your example is good but imagine not being able to use the "Activity" word for that first budget.

I could have some placeholder Activity automatically added (but the user doesn't see it) and store the money there anyway...the user sees what they want, but the data is stored in this manner. Maybe by doing the form unbound and saving the data through code rather than binding to fields.

Hi, been following this thread and though I would add an idea which may help with using a rough typed in estimate vs a total of the underlying "estimates".

I have developed a database very similar to the one you are talking about.

What I did was have a checkbox chkEstimate and a field Estimate at the Project level which if the checkbox was ticked it would allow the user to enter a figure to the textbox on screen, which would get saved back to the field.

When the form loaded, it would do a check of whether the checkbox was checked and if so change the record source to the Estimate field, but if not checked it would total up the estimates of the underlying records. It wouldn't save this to the Estimate field though. I just had a function that would take the ProjectNum and total everything associated down the line to save writing code over and over.

Others may say it might not be the best way to achieve this, but it worked and allowed those simple projects to be entered super quick.


Sent from my iPhone using Tapatalk

That is an interesting idea. That is simple but covers the scaling consistency issue. I was trying something similar once...if no values were found at a hierarchy level, ignore it and use the numbers above.

The other thing I am doing at the moment is playing with a treeview demo because the underlying data structure (which is self-join based) seems solid. I may or may not use the control since it is Active X, but the structure seems good. This was mentioned back near the beginning of the thread.