Translate Access queries to ASP

[Marco Alting]

Hi,

I'm still confused about my queries, I want to do something is ASP that is
easily done in Access. I'll post the Access queries below as a reference.
The main idea is that the queries depend on each other in a hierarchical
manner. It all works fine in Access, but in the last query
(Qrylevel3CostTotals) there's a criteria which I would like to set from an
HTML form and thats where I am confused! How do I make this work in an ASP
solution?
QryLevel1CostTotals:

SELECT QryNodesParents.ParentID, QryNodesParents.ParentLabel,
Sum(QryLevel2CostTotals.SumOfSumOfBudget) AS SumOfSumOfSumOfBudget,
Sum(QryLevel2CostTotals.SumOfSumOfVariation) AS SumOfSumOfSumOfVariation,
Sum(QryLevel2CostTotals.SumOfLastOfCommitted) AS SumOfSumOfLastOfCommitted,
Sum(QryLevel2CostTotals.SumOfLastOfActual) AS SumOfSumOfLastOfActual,
Sum(QryLevel2CostTotals.SumOfLastOfWork) AS SumOfSumOfLastOfWork,
Sum(QryLevel2CostTotals.SumOfLastOfAccrual) AS SumOfSumOfLastOfAccrual
FROM QryLevel2CostTotals INNER JOIN QryNodesParents ON
QryLevel2CostTotals.TblNodeID = QryNodesParents.TblNodeID
GROUP BY QryNodesParents.ParentID, QryNodesParents.ParentLabel;

QryLevel2CostTotals:

SELECT TblNode.TblNodeID, TblNode.NodeLabel,
Sum(QryLevel3CostTotals.SumOfBudget) AS SumOfSumOfBudget,
Sum(QryLevel3CostTotals.SumOfVariation) AS SumOfSumOfVariation,
Sum(QryLevel3CostTotals.LastOfCommitted) AS SumOfLastOfCommitted,
Sum(QryLevel3CostTotals.LastOfActual) AS SumOfLastOfActual,
Sum(QryLevel3CostTotals.LastOfWork) AS SumOfLastOfWork,
Sum(QryLevel3CostTotals.LastOfAccrual) AS SumOfLastOfAccrual,
QryLevel3CostTotals.LastOfEntryDate
FROM TblNode INNER JOIN QryLevel3CostTotals ON TblNode.TblNodeID =
QryLevel3CostTotals.TblNodeID
GROUP BY TblNode.TblNodeID, TblNode.NodeLabel,
QryLevel3CostTotals.LastOfEntryDate;

QryLevel3CostTotals:

SELECT TblNode.TblNodeID, TblNode.NodeLabel, TblMainEntry.cbsID,
TblCBSandBudget.CBSdescription, Sum(TblCBSandBudget.Budget) AS SumOfBudget,
Sum(IIf(IsNull([TblMainEntry].[Variation]),0,[TblMainEntry].[Variation])) AS
SumOfVariation,
Last(IIf(IsNull([TblMainEntry].[Committed]),0,[TblMainEntry].[Committed]))
AS LastOfCommitted,
Last(IIf(IsNull([TblMainEntry].[Actual]),0,[TblMainEntry].[Actual])) AS
LastOfActual,
Last(IIf(IsNull([TblMainEntry].[Worked]),0,[TblMainEntry].[Worked])) AS
LastOfWork,
Last(IIf(IsNull([TblMainEntry].[Accrual]),0,[TblMainEntry].[Accrual])) AS
LastOfAccrual,
Last(IIf(IsNull([TblMainEntry].[EntryDate]),0,[TblMainEntry].[EntryDate]))
AS LastOfEntryDate
FROM (TblMainEntry RIGHT JOIN TblCBSandBudget ON TblMainEntry.cbsID =
TblCBSandBudget.cbsID) INNER JOIN TblNode ON TblCBSandBudget.NodeID =
TblNode.TblNodeID
GROUP BY TblNode.TblNodeID, TblNode.NodeLabel, TblMainEntry.cbsID,
TblCBSandBudget.CBSdescription
HAVING
(((Last(IIf(IsNull([TblMainEntry].[EntryDate]),0,[TblMainEntry].[EntryDate])
))<#9/1/2003#));

[Marco Alting]

Thanks Bob,

I Think this look pretty good, although I'm not that familiar with the ASP
yet, I am used to setting up my connections using the following syntax:

Dim rsLevel1Totals
Dim rsLevel1Totals_numRows

Set rsLevel1Totals = Server.CreateObject("ADODB.Recordset")
rsLevel1Totals.ActiveConnection = MM_ICR_STRING
rsLevel1TotalsQry = "SELECT * FROM QryLevel1CostTotals"
rsLevel1Totals.CursorType = 0
rsLevel1Totals.CursorLocation = 2
rsLevel1Totals.LockType = 1
rsLevel1Totals.Open (rsLevel1TotalsQry dEntryDate, rsLeve11Totals)

I've put your code in the last line, but I get an error. Can you post me an
example of how you would do it?

Thanks,
Marco
"Bob Barrows" <re*******@yahoo.com> schreef in bericht
news:ub*************@TK2MSFTNGP11.phx.gbl...

Replace this:
))<#9/1/2003#));
with this:
))<[pDate]));

Create and open a connection object. I'll assume you name it "cn". Then do
this:

dim cn, rs, dEntryDate
dEntryDate=cDate("9/1/2003")
cn.QryLevel2CostTotals dEntryDate, rs

And you're done.

HTH,
Bob Barrows
Marco Alting wrote:

Hi,

I'm still confused about my queries, I want to do something is ASP
that is easily done in Access. I'll post the Access queries below as
a reference. The main idea is that the queries depend on each other
in a hierarchical manner. It all works fine in Access, but in the
last query (Qrylevel3CostTotals) there's a criteria which I would
like to set from an HTML form and thats where I am confused! How do I
make this work in an ASP solution?
QryLevel1CostTotals:

SELECT QryNodesParents.ParentID, QryNodesParents.ParentLabel,
Sum(QryLevel2CostTotals.SumOfSumOfBudget) AS SumOfSumOfSumOfBudget,
Sum(QryLevel2CostTotals.SumOfSumOfVariation) AS
SumOfSumOfSumOfVariation,
Sum(QryLevel2CostTotals.SumOfLastOfCommitted) AS
SumOfSumOfLastOfCommitted, Sum(QryLevel2CostTotals.SumOfLastOfActual)
AS SumOfSumOfLastOfActual, Sum(QryLevel2CostTotals.SumOfLastOfWork)
AS SumOfSumOfLastOfWork, Sum(QryLevel2CostTotals.SumOfLastOfAccrual)
AS SumOfSumOfLastOfAccrual
FROM QryLevel2CostTotals INNER JOIN QryNodesParents ON
QryLevel2CostTotals.TblNodeID = QryNodesParents.TblNodeID
GROUP BY QryNodesParents.ParentID, QryNodesParents.ParentLabel;

QryLevel2CostTotals:

SELECT TblNode.TblNodeID, TblNode.NodeLabel,
Sum(QryLevel3CostTotals.SumOfBudget) AS SumOfSumOfBudget,
Sum(QryLevel3CostTotals.SumOfVariation) AS SumOfSumOfVariation,
Sum(QryLevel3CostTotals.LastOfCommitted) AS SumOfLastOfCommitted,
Sum(QryLevel3CostTotals.LastOfActual) AS SumOfLastOfActual,
Sum(QryLevel3CostTotals.LastOfWork) AS SumOfLastOfWork,
Sum(QryLevel3CostTotals.LastOfAccrual) AS SumOfLastOfAccrual,
QryLevel3CostTotals.LastOfEntryDate
FROM TblNode INNER JOIN QryLevel3CostTotals ON TblNode.TblNodeID =
QryLevel3CostTotals.TblNodeID
GROUP BY TblNode.TblNodeID, TblNode.NodeLabel,
QryLevel3CostTotals.LastOfEntryDate;

QryLevel3CostTotals:

SELECT TblNode.TblNodeID, TblNode.NodeLabel, TblMainEntry.cbsID,
TblCBSandBudget.CBSdescription, Sum(TblCBSandBudget.Budget) AS
SumOfBudget,
Sum(IIf(IsNull([TblMainEntry].[Variation]),0,[TblMainEntry].[Variation])) AS SumOfVariation,
Last(IIf(IsNull([TblMainEntry].[Committed]),0,[TblMainEntry].[Committed])) AS LastOfCommitted,
Last(IIf(IsNull([TblMainEntry].[Actual]),0,[TblMainEntry].[Actual]))
AS LastOfActual,
Last(IIf(IsNull([TblMainEntry].[Worked]),0,[TblMainEntry].[Worked]))
AS LastOfWork,
Last(IIf(IsNull([TblMainEntry].[Accrual]),0,[TblMainEntry].[Accrual]))
AS LastOfAccrual,
Last(IIf(IsNull([TblMainEntry].[EntryDate]),0,[TblMainEntry].[EntryDate])) AS LastOfEntryDate
FROM (TblMainEntry RIGHT JOIN TblCBSandBudget ON TblMainEntry.cbsID =
TblCBSandBudget.cbsID) INNER JOIN TblNode ON TblCBSandBudget.NodeID =
TblNode.TblNodeID
GROUP BY TblNode.TblNodeID, TblNode.NodeLabel, TblMainEntry.cbsID,
TblCBSandBudget.CBSdescription
HAVING

(((Last(IIf(IsNull([TblMainEntry].[EntryDate]),0,[TblMainEntry].[EntryDate])

))<#9/1/2003#));

[Bob Lehmann]

<<<<AS SumOfSumOfSumOfBudget>>>>
I'm a SumOf Of a SumOf a Sailor
Apologies to JImmy Buffet.....

Sorry :>)

Bob Lehmann

"Marco Alting" <ma***@alting-multimedia.nl> wrote in message
news:7j**********************@amsnews03.chello.com ...

Hi,

I'm still confused about my queries, I want to do something is ASP that is
easily done in Access. I'll post the Access queries below as a reference.
The main idea is that the queries depend on each other in a hierarchical
manner. It all works fine in Access, but in the last query
(Qrylevel3CostTotals) there's a criteria which I would like to set from an
HTML form and thats where I am confused! How do I make this work in an ASP
solution?
QryLevel1CostTotals:

SELECT QryNodesParents.ParentID, QryNodesParents.ParentLabel,
Sum(QryLevel2CostTotals.SumOfSumOfBudget) AS SumOfSumOfSumOfBudget,
Sum(QryLevel2CostTotals.SumOfSumOfVariation) AS SumOfSumOfSumOfVariation,
Sum(QryLevel2CostTotals.SumOfLastOfCommitted) AS SumOfSumOfLastOfCommitted, Sum(QryLevel2CostTotals.SumOfLastOfActual) AS SumOfSumOfLastOfActual,
Sum(QryLevel2CostTotals.SumOfLastOfWork) AS SumOfSumOfLastOfWork,
Sum(QryLevel2CostTotals.SumOfLastOfAccrual) AS SumOfSumOfLastOfAccrual
FROM QryLevel2CostTotals INNER JOIN QryNodesParents ON
QryLevel2CostTotals.TblNodeID = QryNodesParents.TblNodeID
GROUP BY QryNodesParents.ParentID, QryNodesParents.ParentLabel;

QryLevel2CostTotals:

SELECT TblNode.TblNodeID, TblNode.NodeLabel,
Sum(QryLevel3CostTotals.SumOfBudget) AS SumOfSumOfBudget,
Sum(QryLevel3CostTotals.SumOfVariation) AS SumOfSumOfVariation,
Sum(QryLevel3CostTotals.LastOfCommitted) AS SumOfLastOfCommitted,
Sum(QryLevel3CostTotals.LastOfActual) AS SumOfLastOfActual,
Sum(QryLevel3CostTotals.LastOfWork) AS SumOfLastOfWork,
Sum(QryLevel3CostTotals.LastOfAccrual) AS SumOfLastOfAccrual,
QryLevel3CostTotals.LastOfEntryDate
FROM TblNode INNER JOIN QryLevel3CostTotals ON TblNode.TblNodeID =
QryLevel3CostTotals.TblNodeID
GROUP BY TblNode.TblNodeID, TblNode.NodeLabel,
QryLevel3CostTotals.LastOfEntryDate;

QryLevel3CostTotals:

SELECT TblNode.TblNodeID, TblNode.NodeLabel, TblMainEntry.cbsID,
TblCBSandBudget.CBSdescription, Sum(TblCBSandBudget.Budget) AS SumOfBudget, Sum(IIf(IsNull([TblMainEntry].[Variation]),0,[TblMainEntry].[Variation])) AS SumOfVariation,
Last(IIf(IsNull([TblMainEntry].[Committed]),0,[TblMainEntry].[Committed]))
AS LastOfCommitted,
Last(IIf(IsNull([TblMainEntry].[Actual]),0,[TblMainEntry].[Actual])) AS
LastOfActual,
Last(IIf(IsNull([TblMainEntry].[Worked]),0,[TblMainEntry].[Worked])) AS
LastOfWork,
Last(IIf(IsNull([TblMainEntry].[Accrual]),0,[TblMainEntry].[Accrual])) AS
LastOfAccrual,
Last(IIf(IsNull([TblMainEntry].[EntryDate]),0,[TblMainEntry].[EntryDate]))
AS LastOfEntryDate
FROM (TblMainEntry RIGHT JOIN TblCBSandBudget ON TblMainEntry.cbsID =
TblCBSandBudget.cbsID) INNER JOIN TblNode ON TblCBSandBudget.NodeID =
TblNode.TblNodeID
GROUP BY TblNode.TblNodeID, TblNode.NodeLabel, TblMainEntry.cbsID,
TblCBSandBudget.CBSdescription
HAVING
(((Last(IIf(IsNull([TblMainEntry].[EntryDate]),0,[TblMainEntry].[EntryDate]) ))<#9/1/2003#));

[Bob Barrows]

Marco Alting wrote:

Thanks Bob,

I Think this look pretty good, although I'm not that familiar with
the ASP yet, I am used to setting up my connections using the
following syntax:
Well please get used to not doing it this way. Always open a connection
object and use it to perform your database activities.

Dim rsLevel1Totals
Dim rsLevel1Totals_numRows

Set rsLevel1Totals = Server.CreateObject("ADODB.Recordset")
Get rid of this line: rsLevel1Totals.ActiveConnection = MM_ICR_STRING
Instead. create and open a connection object using this connection string:
dim cn
set cn=server.createobject("adodb.connection")
cn.open MM_ICR_STRING

Get rid of this line: rsLevel1TotalsQry = "SELECT * FROM QryLevel1CostTotals"
rsLevel1Totals.CursorType = 0
rsLevel1Totals.CursorLocation = 2
rsLevel1Totals.LockType = 1
Get rid of this line: rsLevel1Totals.Open (rsLevel1TotalsQry dEntryDate, rsLeve11Totals)

Replace it with

cn.QryLevel1CostTotals rsLevel1Totals

HTH,
Bob Barrows

[Bob Barrows]

Bob Barrows wrote:

cn.QryLevel1CostTotals rsLevel1Totals

This should be:
cn.QryLevel1CostTotals dEntryDate, rsLevel1Totals

[Marco Alting]

Thanks Bob,

I implemented all of what you written, and it seems to bel working (sort
of). Because the HTML table generated from the queries still shows all
records... Could this have something to do with the way we set the variable:
dEntryDate=cDate("9/1/2003")?

Regards,
Marco

"Bob Barrows" <re*******@yahoo.com> schreef in bericht
news:OS*************@TK2MSFTNGP11.phx.gbl...

Bob Barrows wrote:

cn.QryLevel1CostTotals rsLevel1Totals

This should be:
cn.QryLevel1CostTotals dEntryDate, rsLevel1Totals

[Bob Barrows]

Marco Alting wrote:

Thanks Bob,

I implemented all of what you written, and it seems to bel working
(sort of). Because the HTML table generated from the queries still
shows all records... Could this have something to do with the way we
set the variable: dEntryDate=cDate("9/1/2003")?

I don't know. what happens when you test the query in Access?

[Marco Alting]

Another thing that now comes up, is that my MoveFirst() doesn't work
anymore, what could be wrong there?
"Bob Barrows" <re*******@yahoo.com> schreef in bericht
news:OS*************@TK2MSFTNGP11.phx.gbl...

Bob Barrows wrote:

cn.QryLevel1CostTotals rsLevel1Totals

This should be:
cn.QryLevel1CostTotals dEntryDate, rsLevel1Totals

[Marco Alting]

It works fine in Access, but I think I solved that now, the problem now is I
think in the cursorType, because my MoveFirst doesn't work anymore...
"Bob Barrows" <re*******@yahoo.com> schreef in bericht
news:eK**************@TK2MSFTNGP09.phx.gbl...

Marco Alting wrote:

Thanks Bob,

I implemented all of what you written, and it seems to bel working
(sort of). Because the HTML table generated from the queries still
shows all records... Could this have something to do with the way we
set the variable: dEntryDate=cDate("9/1/2003")?

I don't know. what happens when you test the query in Access?

[TomB]

Take this part
Set rsLevel1Totals = Server.CreateObject("ADODB.Recordset")
rsLevel1Totals.ActiveConnection = MM_ICR_STRING
rsLevel1TotalsQry = "SELECT * FROM QryLevel1CostTotals"
rsLevel1Totals.CursorType = 0
rsLevel1Totals.CursorLocation = 2
rsLevel1Totals.LockType = 1
rsLevel1Totals.Open (rsLevel1TotalsQry dEntryDate, rsLeve11Totals)

and change it to.....

Set cn=CreateObject("ADODB.Connection")
CN.Open MM_ICR_STRING
dEntryDate=cDate("9/1/2003")
cn.QryLevel2CostTotals dEntryDate, rsLevel1Totals
this is a cool feature of the connection object. It'll take a query/stored
procedure as though it was a subroutine. Taking the query parameters as
subroutine parameters, and the final parameter is the recordset to fill.

So in your case you have a query called QryLevel2CostTotals that takes one
parameter, dEntryDate, and returns a recordset called rsLevel1Totals

"Marco Alting" <ma***@alting-multimedia.nl> wrote in message
news:CO**********************@amsnews03.chello.com ...

Thanks Bob,

I Think this look pretty good, although I'm not that familiar with the ASP
yet, I am used to setting up my connections using the following syntax:

Dim rsLevel1Totals
Dim rsLevel1Totals_numRows

Set rsLevel1Totals = Server.CreateObject("ADODB.Recordset")
rsLevel1Totals.ActiveConnection = MM_ICR_STRING
rsLevel1TotalsQry = "SELECT * FROM QryLevel1CostTotals"
rsLevel1Totals.CursorType = 0
rsLevel1Totals.CursorLocation = 2
rsLevel1Totals.LockType = 1
rsLevel1Totals.Open (rsLevel1TotalsQry dEntryDate, rsLeve11Totals)

I've put your code in the last line, but I get an error. Can you post me an example of how you would do it?

Thanks,
Marco
"Bob Barrows" <re*******@yahoo.com> schreef in bericht
news:ub*************@TK2MSFTNGP11.phx.gbl...

Replace this:
))<#9/1/2003#));
with this:
))<[pDate]));

Create and open a connection object. I'll assume you name it "cn". Then do

this:

dim cn, rs, dEntryDate
dEntryDate=cDate("9/1/2003")
cn.QryLevel2CostTotals dEntryDate, rs

And you're done.

HTH,
Bob Barrows
Marco Alting wrote:

Hi,

I'm still confused about my queries, I want to do something is ASP
that is easily done in Access. I'll post the Access queries below as
a reference. The main idea is that the queries depend on each other
in a hierarchical manner. It all works fine in Access, but in the
last query (Qrylevel3CostTotals) there's a criteria which I would
like to set from an HTML form and thats where I am confused! How do I
make this work in an ASP solution?
QryLevel1CostTotals:

SELECT QryNodesParents.ParentID, QryNodesParents.ParentLabel,
Sum(QryLevel2CostTotals.SumOfSumOfBudget) AS SumOfSumOfSumOfBudget,
Sum(QryLevel2CostTotals.SumOfSumOfVariation) AS
SumOfSumOfSumOfVariation,
Sum(QryLevel2CostTotals.SumOfLastOfCommitted) AS
SumOfSumOfLastOfCommitted, Sum(QryLevel2CostTotals.SumOfLastOfActual)
AS SumOfSumOfLastOfActual, Sum(QryLevel2CostTotals.SumOfLastOfWork)
AS SumOfSumOfLastOfWork, Sum(QryLevel2CostTotals.SumOfLastOfAccrual)
AS SumOfSumOfLastOfAccrual
FROM QryLevel2CostTotals INNER JOIN QryNodesParents ON
QryLevel2CostTotals.TblNodeID = QryNodesParents.TblNodeID
GROUP BY QryNodesParents.ParentID, QryNodesParents.ParentLabel;

QryLevel2CostTotals:

SELECT TblNode.TblNodeID, TblNode.NodeLabel,
Sum(QryLevel3CostTotals.SumOfBudget) AS SumOfSumOfBudget,
Sum(QryLevel3CostTotals.SumOfVariation) AS SumOfSumOfVariation,
Sum(QryLevel3CostTotals.LastOfCommitted) AS SumOfLastOfCommitted,
Sum(QryLevel3CostTotals.LastOfActual) AS SumOfLastOfActual,
Sum(QryLevel3CostTotals.LastOfWork) AS SumOfLastOfWork,
Sum(QryLevel3CostTotals.LastOfAccrual) AS SumOfLastOfAccrual,
QryLevel3CostTotals.LastOfEntryDate
FROM TblNode INNER JOIN QryLevel3CostTotals ON TblNode.TblNodeID =
QryLevel3CostTotals.TblNodeID
GROUP BY TblNode.TblNodeID, TblNode.NodeLabel,
QryLevel3CostTotals.LastOfEntryDate;

QryLevel3CostTotals:

SELECT TblNode.TblNodeID, TblNode.NodeLabel, TblMainEntry.cbsID,
TblCBSandBudget.CBSdescription, Sum(TblCBSandBudget.Budget) AS
SumOfBudget,

Sum(IIf(IsNull([TblMainEntry].[Variation]),0,[TblMainEntry].[Variation])) AS SumOfVariation,
Last(IIf(IsNull([TblMainEntry].[Committed]),0,[TblMainEntry].[Committed])) AS LastOfCommitted,
Last(IIf(IsNull([TblMainEntry].[Actual]),0,[TblMainEntry].[Actual]))
AS LastOfActual,
Last(IIf(IsNull([TblMainEntry].[Worked]),0,[TblMainEntry].[Worked]))
AS LastOfWork,
Last(IIf(IsNull([TblMainEntry].[Accrual]),0,[TblMainEntry].[Accrual]))
AS LastOfAccrual,
Last(IIf(IsNull([TblMainEntry].[EntryDate]),0,[TblMainEntry].[EntryDate])) AS LastOfEntryDate
FROM (TblMainEntry RIGHT JOIN TblCBSandBudget ON TblMainEntry.cbsID =
TblCBSandBudget.cbsID) INNER JOIN TblNode ON TblCBSandBudget.NodeID =
TblNode.TblNodeID
GROUP BY TblNode.TblNodeID, TblNode.NodeLabel, TblMainEntry.cbsID,
TblCBSandBudget.CBSdescription
HAVING

(((Last(IIf(IsNull([TblMainEntry].[EntryDate]),0,[TblMainEntry].[EntryDate])

))<#9/1/2003#));

[Marco Alting]

Thanks for all the help!!!

It is solved and working fine!

Thanks again!
"Marco Alting" <ma***@alting-multimedia.nl> schreef in bericht
news:7j**********************@amsnews03.chello.com ...

Hi,

I'm still confused about my queries, I want to do something is ASP that is
easily done in Access. I'll post the Access queries below as a reference.
The main idea is that the queries depend on each other in a hierarchical
manner. It all works fine in Access, but in the last query
(Qrylevel3CostTotals) there's a criteria which I would like to set from an
HTML form and thats where I am confused! How do I make this work in an ASP
solution?
QryLevel1CostTotals:

SELECT QryNodesParents.ParentID, QryNodesParents.ParentLabel,
Sum(QryLevel2CostTotals.SumOfSumOfBudget) AS SumOfSumOfSumOfBudget,
Sum(QryLevel2CostTotals.SumOfSumOfVariation) AS SumOfSumOfSumOfVariation,
Sum(QryLevel2CostTotals.SumOfLastOfCommitted) AS SumOfSumOfLastOfCommitted, Sum(QryLevel2CostTotals.SumOfLastOfActual) AS SumOfSumOfLastOfActual,
Sum(QryLevel2CostTotals.SumOfLastOfWork) AS SumOfSumOfLastOfWork,
Sum(QryLevel2CostTotals.SumOfLastOfAccrual) AS SumOfSumOfLastOfAccrual
FROM QryLevel2CostTotals INNER JOIN QryNodesParents ON
QryLevel2CostTotals.TblNodeID = QryNodesParents.TblNodeID
GROUP BY QryNodesParents.ParentID, QryNodesParents.ParentLabel;

QryLevel2CostTotals:

SELECT TblNode.TblNodeID, TblNode.NodeLabel,
Sum(QryLevel3CostTotals.SumOfBudget) AS SumOfSumOfBudget,
Sum(QryLevel3CostTotals.SumOfVariation) AS SumOfSumOfVariation,
Sum(QryLevel3CostTotals.LastOfCommitted) AS SumOfLastOfCommitted,
Sum(QryLevel3CostTotals.LastOfActual) AS SumOfLastOfActual,
Sum(QryLevel3CostTotals.LastOfWork) AS SumOfLastOfWork,
Sum(QryLevel3CostTotals.LastOfAccrual) AS SumOfLastOfAccrual,
QryLevel3CostTotals.LastOfEntryDate
FROM TblNode INNER JOIN QryLevel3CostTotals ON TblNode.TblNodeID =
QryLevel3CostTotals.TblNodeID
GROUP BY TblNode.TblNodeID, TblNode.NodeLabel,
QryLevel3CostTotals.LastOfEntryDate;

QryLevel3CostTotals:

SELECT TblNode.TblNodeID, TblNode.NodeLabel, TblMainEntry.cbsID,
TblCBSandBudget.CBSdescription, Sum(TblCBSandBudget.Budget) AS SumOfBudget, Sum(IIf(IsNull([TblMainEntry].[Variation]),0,[TblMainEntry].[Variation])) AS SumOfVariation,
Last(IIf(IsNull([TblMainEntry].[Committed]),0,[TblMainEntry].[Committed]))
AS LastOfCommitted,
Last(IIf(IsNull([TblMainEntry].[Actual]),0,[TblMainEntry].[Actual])) AS
LastOfActual,
Last(IIf(IsNull([TblMainEntry].[Worked]),0,[TblMainEntry].[Worked])) AS
LastOfWork,
Last(IIf(IsNull([TblMainEntry].[Accrual]),0,[TblMainEntry].[Accrual])) AS
LastOfAccrual,
Last(IIf(IsNull([TblMainEntry].[EntryDate]),0,[TblMainEntry].[EntryDate]))
AS LastOfEntryDate
FROM (TblMainEntry RIGHT JOIN TblCBSandBudget ON TblMainEntry.cbsID =
TblCBSandBudget.cbsID) INNER JOIN TblNode ON TblCBSandBudget.NodeID =
TblNode.TblNodeID
GROUP BY TblNode.TblNodeID, TblNode.NodeLabel, TblMainEntry.cbsID,
TblCBSandBudget.CBSdescription
HAVING
(((Last(IIf(IsNull([TblMainEntry].[EntryDate]),0,[TblMainEntry].[EntryDate]) ))<#9/1/2003#));