Master/Detail1/Detail2 - All Calculated.* Genius Required*

[My User Name]

Hi, I have a relatively simple database consisting of 3 Tables that are the same, WH-Budget(Master), Details1, Details2. WH-Budget is master to details1 and details1 is master to details2.
The Master is set out as:


And detail1 is:

And detail2 is:

The purpose of my Database is to be a budget planner for a project. The relationships work fine :
[WH-Budget].[WBS Element]-->details1.[Parent Element]
details1.[WBS Element]-->details2.[Parent Element]


and [WH-Budget].[WBS Element] is the [Master Element] for details2

The part i am stuck on is calculating the fields in the database. I need all the fields in the Master table to be calculated from the 2 detail pages, so i wrote this select query:

SELECT [WH-Budget].ID AS ID, [WH-Budget].[WBS ELEMENT] AS [WBS ELEMENT], Sum([DETAIL1].[Baseline Budget]+[DETAIL2].[Baseline Budget]) AS [Baseline Budget], Sum([DETAIL1].[Revised Cost]+[DETAIL2].[Revised Cost]) AS [Revised Cost], Sum([DETAIL1].Variance+[DETAIL2].Variance) AS Variance, Sum([DETAIL1].Ordered+[DETAIL2].Ordered) AS Ordered, Sum([DETAIL1].Invoiced+[DETAIL2].Invoiced) AS Invoiced, Sum([DETAIL1].Actual+[DETAIL2].Actual) AS Actual
FROM ([WH-BUDGET] INNER JOIN DETAIL1 ON [WH-BUDGET].[WBS ELEMENT]=DETAIL1.Parent) INNER JOIN DETAIL2 ON (DETAIL1.[WBS ELEMENT]=DETAIL2.Parent) AND (DETAIL1.Parent=DETAIL2.Master)
GROUP BY [WH-Budget].[WBS ELEMENT], [WH-Budget].ID;

This only works if there is one row for each element in the detail pages, so the query is obviously wrong
in the example above there are many detail2 records related to fewer detail1 and master records. I just cant get the calculations correct. I'm eventually running this through aspmaker to make a web based budget planner. Please help, i refuse to give up but honestly iv been on this for over 200hrs

[RayMilhon]

You might want to re-think your design or provide more detail on your design. From what is displayed you have a lot of the same fields in each table. Read up on normalization it'll help. Also I've never found a good reason to use relationships at the table level. In my humble opinion your better off setting the relationships using queries.

[My User Name]

I was just experementing with the relationships, thats why the tab is open. This is goin to be used as a budget planner for a site relocation. The main table has the topic headers like, people, machinery. Then when the user clicks the little plus button they can add or view an item of machinery and even add details about that. im trying to make the amounts add up and display on the main tbale for each topic. I have researched database normalization in the past and done tutorials. I'v been programming for 10 years. I'v posted this question in at least 4 other forums in 4 months and no one gets it.

[RayMilhon]

If you could post your actual table structure for each of those tables you might get additional help. Based on what I can see it looks like you have a lot of the same data elements in each table. That's a sure sign of a bad design. although it could look that way due to the relationships. I've never used them so no clue as to what is presented by your attachments. I've been designing Databases since 1991 I'll help as much as i can but need to understand your structure to help.

[My User Name]

This is a drill down, treeview, budget planner

This is the structure, master, parent and WBS ELEMENT are text, all the rest are numeric

WH-BUDGET | Details1 | Details2
Baseline Budget = Baseline Budget + Baseline Budget

+same for all numeric fields

i'v uploaded my DB too, thanks for your help budgetDB.zip

if you need any more info to advise me i will reply asap.

[RayMilhon]

Looked at your tables and I think you need to rethink your design. Also the data doesn't make sense which is caused by your design. Here's my recommendation:

Table WH-Budget
whBudgetID AutoNumber PrimaryKey
whBudgetDesc Text

Table Detail1
Detail1ID AutoNumber PrimaryKey
whbudgetid number ForeignKey to wh-Budget table
Detail1Desc Text

Table Detail2
Detail2ID AutoNumber PrimaryKey
Detail1ID Number ForeignKey to Detail1
Detail1Desc Text
Baseline Numeric
Revised Numeric
Variance Numeric
Ordered Numeric
Invoiced Numeric
Actual Numeric

[My User Name]

Hi, thanks for your great advice after thinking it over, it does seem a lot more logical. If i wanted to add an extra detail table would this be ok:

Table WH-Budget
whBudgetID AutoNumber PrimaryKey
whBudgetDesc Text

Table Detail1
Detail1ID AutoNumber PrimaryKey
whbudgetid number ForeignKey to wh-Budget table
Detail1Desc Text

Table Detail2
Detail2ID AutoNumber PrimaryKey
Detail1ID number ForeignKey to Detail1
Detail2Desc Text

Table Detail3
Detail3ID AutoNumber PrimaryKey
Detail2ID Number ForeignKey to Detail2
Detail3Desc Text
Baseline Numeric
Revised Numeric
Variance Numeric
Ordered Numeric
Invoiced Numeric
Actual Numeric

Am i right in thinking from here i would just call any tables i need with an sql select, then set the master/detail relationships?

[RayMilhon]

Yep. The relationships can be set in the queries by using Joins.

[My User Name]

Im working on the sql now but im not sure its going to work because not all the [whbudget] records will go to detail3 some may just have detail1 record and thats it. Would this mean i need to include the other fields eg. baseline budget, variance, on all the detail tables?

[RayMilhon]

Not necessarily. It's hard to analyze from here with table names detail1,detail2,detail3 Not sure what each is actually for. based on my understanding. I thought it was where the wHbudget was the category. Detail1 a sub category. Detail2 a sub category of detail1 and detail 3 the actual detail. Apparently that is inaccurate. Maybe you could explain what each detail table actually represents?