Calculating Fields MASTER-Detail1-Detail2-Detail3

[M4A]

Hi, I have a relatively simple database consisting of 5 Tables WH-Budget(Master), ViewDetails1, ViewDetails2, ect.... WH-Budget is master to viewdetails1, viewdetails1 is master to viewdetails2, Viewdetails2 is master to viewdetails3 and viewdetails3 is master to viewdetails4. The Master is set out as: ID | WBS Element | Baseline Budget | Revised Cost | Revised Saving | Variance | Ordered | Invioced | And the 4 detail pages are as: ID | Master Element | Parent Element | WBS Element | Baseline Budget | Revised Cost | Revised Saving | Variance | Ordered | Invioced | The purpose of my Database is to be a budget planner for a project.I run my access database through aspmaker using an extension (Previewrow) that allows me to drill down into the master detail, with a nice front end. The relationships work fine : [WH-Budget].[WBS Element]-->Viewdetails1.[Parent Element]Viewdetails1.[WBS Element]-->Viewdetails2.[Parent Element]and [WH-Budget].[WBS Element] is the [Master Element] for Viewdetails 1, 2, 3 & 4 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 four detail pages e.g.[WH-Budget].[baseline Budget] = Viewdetails1.[baseline Budget] + Viewdetails2.[baseline Budget] + Viewdetails3.[baseline Budget] + Viewdetails4.[baseline Budget] and i need to do this for each field in the master table apart from the [WBS Element] I am inserting the SQL in the events, I have tried and tried but to no avail. Here's a snippet: ' Page Load eventSub Page_Load()Dim SELECTSQLa, SELECTSQLb, v3 v3 = (SELECTSQLa) + (SELECTSQLb) SELECTSQLa = ("SELECT [baseline Budget] FROM ViewDetails1;") SELECTSQLb = ("SELECT [baseline Budget] FROM ViewDetails2;")ew_Execute(SELECTSQLa)ew_Execute(SELECTSQLb)ew_Execute("UPDATE [WH-Budget] SET [baseline Budget]=" & v3 & ";")End Sub I know this looks pretty messed up and miles away, but all the things i thought would of worked i tried the first 200 attempts: i started off with..... Dim v1, v2, v3v1 = ... ' Budget 1v2 = ... ' Budget 2v3 = v1 + v2ew_Execute("UPDATE <Table> SET <Field> = ' & v3 & " WHERE <condition>") ......and got very lost. I realise this will need many queries, but i cant get it started. Please You Clever People, Share your knowledge with me and i will be eternally gratefull.

[justsomeguy]

For one thing, you're trying to calculate v3 before you have the other 2 values. You need to execute the queries, get the values they return, and then add them up. SELECTSQLa and SELECTSQLb are not numeric values, they are strings of text that you send to the database. I'm not sure what your ew_execute function does, but you're going to need to get the rows that the queries return and add up the appropriate fields from the results to update the next table. It's also possible to use SELECT queries inside an UPDATE query.

[M4A]

Hi iv been trying and trying, i think this is the closest i came: ' Page Load eventSub Page_Load() Dim v1, v2, v3v1 = ViewDetails1.[baseline budget]v2 = ViewDetails2.[baseline budget]v3 = v1 + v2ew_Execute("UPDATE [WH-Budget] SET [baseline budget] = " & v3 & " WHERE [WH-Budget].[WBS Element] = ViewDetails1.[Master Element];")End Sub but i recieve this error: Error Type:Microsoft VBScript runtime (0x800A01B6)Object doesn't support this property or method: 'ViewDetails1.Baseline budget'/project9/WH2DBudgetlist.asp, line 1942I have been through the database and checked all the field types. I'v tried changing field types putting default values in.Where am i goig wrong?

[justsomeguy]

ViewDetails1 and ViewDetails2 are not defined that code.

[M4A]

Hello again, ok i tried to get the SQL right first in access to put into the VB. I got this to work: SELECT Sum([ViewDetails1]![baseline Budget]+[ViewDetails2]![baseline Budget]) AS Expr1FROM ViewDetails1, ViewDetails2; ' Then this: INSERT INTO [WH-Budget] ( [baseline Budget] )SELECT Sum([ViewDetails1]![baseline Budget]+[ViewDetails2]![baseline Budget]) AS Expr1FROM ViewDetails1, ViewDetails2; But NOT this: .Function Row_Inserting(rsold, rsnew) If Not EW_DEBUG_ENABLED Then On Error Resume Next ew_Execute("INSERT INTO [WH-Budget] ( [baseline Budget] ) SELECT Sum([ViewDetails1]![baseline Budget]+[ViewDetails2]![baseline Budget]) FROM ViewDetails1, ViewDetails2 WHERE [WH-Budget].[WBS ELEMENT]=Viewdetails1.[Master ELEMENT] AND [WH-Budget].[WBS ELEMENT]=Viewdetails2.[Master ELEMENT];") Row_Inserting = TrueEnd Function Do you think i am going about this project the wrong way or Would this "once completed" Be an adequette way of making a web based, drill down table?

[justsomeguy]

What do you mean that doesn't work, what happens when you run it? Is there an error message? It's hard to comment on whether this is the right way without being familiar with all of the details of your project, the requirements, database structure, etc. There are any number of ways to solve a given problem.

[M4A]

To be honest my aspmaker trial just ran out an hour ago im just about to buy liscence for $200 then i'll note the error message.I,v only got 36 hrs to finish it. Cheers for your help

[M4A]

If i have 3 tables: MASTER|DETAIL|DETAIL2 with 3 fields: ID|Title|Budget all the same.How do i make the master table budget field be calculated from the other 2? e.g. Master.Budget=Detail.Budget+Detail2.Budget I,v completely lost track. All I Need is that little snippet and ill be able to write all the calculations

[justsomeguy]

I believe you can do something like this: INSERT INTO master (id, title, budget) SELECT d1.id, d1.title, d1.budget + d2.budget FROM detail AS d1 INNER JOIN detail2 AS d2 ON d1.id=d2.id