469,568 Members | 1,795 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,568 developers. It's quick & easy.

Translate Access queries to ASP

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#));
Jul 19 '05 #1
10 2172
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#));


Jul 19 '05 #2
<<<<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#));

Jul 19 '05 #3
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
Jul 19 '05 #4
Bob Barrows wrote:
cn.QryLevel1CostTotals rsLevel1Totals

This should be:
cn.QryLevel1CostTotals dEntryDate, rsLevel1Totals
Jul 19 '05 #5
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

Jul 19 '05 #6
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?
Jul 19 '05 #7
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

Jul 19 '05 #8
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?

Jul 19 '05 #9
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#));



Jul 19 '05 #10
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#));

Jul 19 '05 #11

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by Jegger | last post: by
11 posts views Thread by maryjones11289 | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.