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
(Qrylevel3CostT otals) 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?
QryLevel1CostTo tals:

SELECT QryNodesParents .ParentID, QryNodesParents .ParentLabel,
Sum(QryLevel2Co stTotals.SumOfS umOfBudget) AS SumOfSumOfSumOf Budget,
Sum(QryLevel2Co stTotals.SumOfS umOfVariation) AS SumOfSumOfSumOf Variation,
Sum(QryLevel2Co stTotals.SumOfL astOfCommitted) AS SumOfSumOfLastO fCommitted,
Sum(QryLevel2Co stTotals.SumOfL astOfActual) AS SumOfSumOfLastO fActual,
Sum(QryLevel2Co stTotals.SumOfL astOfWork) AS SumOfSumOfLastO fWork,
Sum(QryLevel2Co stTotals.SumOfL astOfAccrual) AS SumOfSumOfLastO fAccrual
FROM QryLevel2CostTo tals INNER JOIN QryNodesParents ON
QryLevel2CostTo tals.TblNodeID = QryNodesParents .TblNodeID
GROUP BY QryNodesParents .ParentID, QryNodesParents .ParentLabel;

QryLevel2CostTo tals:

SELECT TblNode.TblNode ID, TblNode.NodeLab el,
Sum(QryLevel3Co stTotals.SumOfB udget) AS SumOfSumOfBudge t,
Sum(QryLevel3Co stTotals.SumOfV ariation) AS SumOfSumOfVaria tion,
Sum(QryLevel3Co stTotals.LastOf Committed) AS SumOfLastOfComm itted,
Sum(QryLevel3Co stTotals.LastOf Actual) AS SumOfLastOfActu al,
Sum(QryLevel3Co stTotals.LastOf Work) AS SumOfLastOfWork ,
Sum(QryLevel3Co stTotals.LastOf Accrual) AS SumOfLastOfAccr ual,
QryLevel3CostTo tals.LastOfEntr yDate
FROM TblNode INNER JOIN QryLevel3CostTo tals ON TblNode.TblNode ID =
QryLevel3CostTo tals.TblNodeID
GROUP BY TblNode.TblNode ID, TblNode.NodeLab el,
QryLevel3CostTo tals.LastOfEntr yDate;

QryLevel3CostTo tals:

SELECT TblNode.TblNode ID, TblNode.NodeLab el, TblMainEntry.cb sID,
TblCBSandBudget .CBSdescription , Sum(TblCBSandBu dget.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.cb sID =
TblCBSandBudget .cbsID) INNER JOIN TblNode ON TblCBSandBudget .NodeID =
TblNode.TblNode ID
GROUP BY TblNode.TblNode ID, TblNode.NodeLab el, TblMainEntry.cb sID,
TblCBSandBudget .CBSdescription
HAVING
(((Last(IIf(IsN ull([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.CreateOb ject("ADODB.Rec ordset")
rsLevel1Totals. ActiveConnectio n = MM_ICR_STRING
rsLevel1TotalsQ ry = "SELECT * FROM QryLevel1CostTo tals"
rsLevel1Totals. CursorType = 0
rsLevel1Totals. CursorLocation = 2
rsLevel1Totals. LockType = 1
rsLevel1Totals. Open (rsLevel1Totals Qry 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*******@yaho o.com> schreef in bericht
news:ub******** *****@TK2MSFTNG P11.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=cDat e("9/1/2003")
cn.QryLevel2Cos tTotals 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 (Qrylevel3CostT otals) 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?
QryLevel1CostTo tals:

SELECT QryNodesParents .ParentID, QryNodesParents .ParentLabel,
Sum(QryLevel2Co stTotals.SumOfS umOfBudget) AS SumOfSumOfSumOf Budget,
Sum(QryLevel2Co stTotals.SumOfS umOfVariation) AS
SumOfSumOfSumOf Variation,
Sum(QryLevel2Co stTotals.SumOfL astOfCommitted) AS
SumOfSumOfLastO fCommitted, Sum(QryLevel2Co stTotals.SumOfL astOfActual)
AS SumOfSumOfLastO fActual, Sum(QryLevel2Co stTotals.SumOfL astOfWork)
AS SumOfSumOfLastO fWork, Sum(QryLevel2Co stTotals.SumOfL astOfAccrual)
AS SumOfSumOfLastO fAccrual
FROM QryLevel2CostTo tals INNER JOIN QryNodesParents ON
QryLevel2CostTo tals.TblNodeID = QryNodesParents .TblNodeID
GROUP BY QryNodesParents .ParentID, QryNodesParents .ParentLabel;

QryLevel2CostTo tals:

SELECT TblNode.TblNode ID, TblNode.NodeLab el,
Sum(QryLevel3Co stTotals.SumOfB udget) AS SumOfSumOfBudge t,
Sum(QryLevel3Co stTotals.SumOfV ariation) AS SumOfSumOfVaria tion,
Sum(QryLevel3Co stTotals.LastOf Committed) AS SumOfLastOfComm itted,
Sum(QryLevel3Co stTotals.LastOf Actual) AS SumOfLastOfActu al,
Sum(QryLevel3Co stTotals.LastOf Work) AS SumOfLastOfWork ,
Sum(QryLevel3Co stTotals.LastOf Accrual) AS SumOfLastOfAccr ual,
QryLevel3CostTo tals.LastOfEntr yDate
FROM TblNode INNER JOIN QryLevel3CostTo tals ON TblNode.TblNode ID =
QryLevel3CostTo tals.TblNodeID
GROUP BY TblNode.TblNode ID, TblNode.NodeLab el,
QryLevel3CostTo tals.LastOfEntr yDate;

QryLevel3CostTo tals:

SELECT TblNode.TblNode ID, TblNode.NodeLab el, TblMainEntry.cb sID,
TblCBSandBudget .CBSdescription , Sum(TblCBSandBu dget.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.cb sID =
TblCBSandBudget .cbsID) INNER JOIN TblNode ON TblCBSandBudget .NodeID =
TblNode.TblNode ID
GROUP BY TblNode.TblNode ID, TblNode.NodeLab el, TblMainEntry.cb sID,
TblCBSandBudget .CBSdescription
HAVING

(((Last(IIf(IsN ull([TblMainEntry].[EntryDate]),0,[TblMainEntry].[EntryDate])

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

[Bob Lehmann]

<<<<AS SumOfSumOfSumOf Budget>>>>
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.chell o.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
(Qrylevel3CostT otals) 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?
QryLevel1CostTo tals:

SELECT QryNodesParents .ParentID, QryNodesParents .ParentLabel,
Sum(QryLevel2Co stTotals.SumOfS umOfBudget) AS SumOfSumOfSumOf Budget,
Sum(QryLevel2Co stTotals.SumOfS umOfVariation) AS SumOfSumOfSumOf Variation,
Sum(QryLevel2Co stTotals.SumOfL astOfCommitted) AS SumOfSumOfLastO fCommitted, Sum(QryLevel2Co stTotals.SumOfL astOfActual) AS SumOfSumOfLastO fActual,
Sum(QryLevel2Co stTotals.SumOfL astOfWork) AS SumOfSumOfLastO fWork,
Sum(QryLevel2Co stTotals.SumOfL astOfAccrual) AS SumOfSumOfLastO fAccrual
FROM QryLevel2CostTo tals INNER JOIN QryNodesParents ON
QryLevel2CostTo tals.TblNodeID = QryNodesParents .TblNodeID
GROUP BY QryNodesParents .ParentID, QryNodesParents .ParentLabel;

QryLevel2CostTo tals:

SELECT TblNode.TblNode ID, TblNode.NodeLab el,
Sum(QryLevel3Co stTotals.SumOfB udget) AS SumOfSumOfBudge t,
Sum(QryLevel3Co stTotals.SumOfV ariation) AS SumOfSumOfVaria tion,
Sum(QryLevel3Co stTotals.LastOf Committed) AS SumOfLastOfComm itted,
Sum(QryLevel3Co stTotals.LastOf Actual) AS SumOfLastOfActu al,
Sum(QryLevel3Co stTotals.LastOf Work) AS SumOfLastOfWork ,
Sum(QryLevel3Co stTotals.LastOf Accrual) AS SumOfLastOfAccr ual,
QryLevel3CostTo tals.LastOfEntr yDate
FROM TblNode INNER JOIN QryLevel3CostTo tals ON TblNode.TblNode ID =
QryLevel3CostTo tals.TblNodeID
GROUP BY TblNode.TblNode ID, TblNode.NodeLab el,
QryLevel3CostTo tals.LastOfEntr yDate;

QryLevel3CostTo tals:

SELECT TblNode.TblNode ID, TblNode.NodeLab el, TblMainEntry.cb sID,
TblCBSandBudget .CBSdescription , Sum(TblCBSandBu dget.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.cb sID =
TblCBSandBudget .cbsID) INNER JOIN TblNode ON TblCBSandBudget .NodeID =
TblNode.TblNode ID
GROUP BY TblNode.TblNode ID, TblNode.NodeLab el, TblMainEntry.cb sID,
TblCBSandBudget .CBSdescription
HAVING
(((Last(IIf(IsN ull([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.CreateOb ject("ADODB.Rec ordset")
Get rid of this line: rsLevel1Totals. ActiveConnectio n = MM_ICR_STRING
Instead. create and open a connection object using this connection string:
dim cn
set cn=server.creat eobject("adodb. connection")
cn.open MM_ICR_STRING

Get rid of this line: rsLevel1TotalsQ ry = "SELECT * FROM QryLevel1CostTo tals"
rsLevel1Totals. CursorType = 0
rsLevel1Totals. CursorLocation = 2
rsLevel1Totals. LockType = 1
Get rid of this line: rsLevel1Totals. Open (rsLevel1Totals Qry dEntryDate, rsLeve11Totals)

Replace it with

cn.QryLevel1Cos tTotals rsLevel1Totals

HTH,
Bob Barrows

[Bob Barrows]

Bob Barrows wrote:

cn.QryLevel1Cos tTotals rsLevel1Totals

This should be:
cn.QryLevel1Cos tTotals 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=cDat e("9/1/2003")?

Regards,
Marco

"Bob Barrows" <re*******@yaho o.com> schreef in bericht
news:OS******** *****@TK2MSFTNG P11.phx.gbl...

Bob Barrows wrote:

cn.QryLevel1Cos tTotals rsLevel1Totals

This should be:
cn.QryLevel1Cos tTotals 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=cDat e("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*******@yaho o.com> schreef in bericht
news:OS******** *****@TK2MSFTNG P11.phx.gbl...

Bob Barrows wrote:

cn.QryLevel1Cos tTotals rsLevel1Totals

This should be:
cn.QryLevel1Cos tTotals 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*******@yaho o.com> schreef in bericht
news:eK******** ******@TK2MSFTN GP09.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=cDat e("9/1/2003")?

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

[TomB]

Take this part
Set rsLevel1Totals = Server.CreateOb ject("ADODB.Rec ordset")
rsLevel1Totals. ActiveConnectio n = MM_ICR_STRING
rsLevel1TotalsQ ry = "SELECT * FROM QryLevel1CostTo tals"
rsLevel1Totals. CursorType = 0
rsLevel1Totals. CursorLocation = 2
rsLevel1Totals. LockType = 1
rsLevel1Totals. Open (rsLevel1Totals Qry dEntryDate, rsLeve11Totals)

and change it to.....

Set cn=CreateObject ("ADODB.Connect ion")
CN.Open MM_ICR_STRING
dEntryDate=cDat e("9/1/2003")
cn.QryLevel2Cos tTotals 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 QryLevel2CostTo tals that takes one
parameter, dEntryDate, and returns a recordset called rsLevel1Totals

"Marco Alting" <ma***@alting-multimedia.nl> wrote in message
news:CO******** **************@ amsnews03.chell o.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.CreateOb ject("ADODB.Rec ordset")
rsLevel1Totals. ActiveConnectio n = MM_ICR_STRING
rsLevel1TotalsQ ry = "SELECT * FROM QryLevel1CostTo tals"
rsLevel1Totals. CursorType = 0
rsLevel1Totals. CursorLocation = 2
rsLevel1Totals. LockType = 1
rsLevel1Totals. Open (rsLevel1Totals Qry 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*******@yaho o.com> schreef in bericht
news:ub******** *****@TK2MSFTNG P11.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=cDat e("9/1/2003")
cn.QryLevel2Cos tTotals 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 (Qrylevel3CostT otals) 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?
QryLevel1CostTo tals:

SELECT QryNodesParents .ParentID, QryNodesParents .ParentLabel,
Sum(QryLevel2Co stTotals.SumOfS umOfBudget) AS SumOfSumOfSumOf Budget,
Sum(QryLevel2Co stTotals.SumOfS umOfVariation) AS
SumOfSumOfSumOf Variation,
Sum(QryLevel2Co stTotals.SumOfL astOfCommitted) AS
SumOfSumOfLastO fCommitted, Sum(QryLevel2Co stTotals.SumOfL astOfActual)
AS SumOfSumOfLastO fActual, Sum(QryLevel2Co stTotals.SumOfL astOfWork)
AS SumOfSumOfLastO fWork, Sum(QryLevel2Co stTotals.SumOfL astOfAccrual)
AS SumOfSumOfLastO fAccrual
FROM QryLevel2CostTo tals INNER JOIN QryNodesParents ON
QryLevel2CostTo tals.TblNodeID = QryNodesParents .TblNodeID
GROUP BY QryNodesParents .ParentID, QryNodesParents .ParentLabel;

QryLevel2CostTo tals:

SELECT TblNode.TblNode ID, TblNode.NodeLab el,
Sum(QryLevel3Co stTotals.SumOfB udget) AS SumOfSumOfBudge t,
Sum(QryLevel3Co stTotals.SumOfV ariation) AS SumOfSumOfVaria tion,
Sum(QryLevel3Co stTotals.LastOf Committed) AS SumOfLastOfComm itted,
Sum(QryLevel3Co stTotals.LastOf Actual) AS SumOfLastOfActu al,
Sum(QryLevel3Co stTotals.LastOf Work) AS SumOfLastOfWork ,
Sum(QryLevel3Co stTotals.LastOf Accrual) AS SumOfLastOfAccr ual,
QryLevel3CostTo tals.LastOfEntr yDate
FROM TblNode INNER JOIN QryLevel3CostTo tals ON TblNode.TblNode ID =
QryLevel3CostTo tals.TblNodeID
GROUP BY TblNode.TblNode ID, TblNode.NodeLab el,
QryLevel3CostTo tals.LastOfEntr yDate;

QryLevel3CostTo tals:

SELECT TblNode.TblNode ID, TblNode.NodeLab el, TblMainEntry.cb sID,
TblCBSandBudget .CBSdescription , Sum(TblCBSandBu dget.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.cb sID =
TblCBSandBudget .cbsID) INNER JOIN TblNode ON TblCBSandBudget .NodeID =
TblNode.TblNode ID
GROUP BY TblNode.TblNode ID, TblNode.NodeLab el, TblMainEntry.cb sID,
TblCBSandBudget .CBSdescription
HAVING

(((Last(IIf(IsN ull([TblMainEntry].[EntryDate]),0,[TblMainEntry].[EntryDate])

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