将Access查询转换为ASP [英] Translate Access queries to ASP

查看:56
本文介绍了将Access查询转换为ASP的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述




我仍​​然对我的疑问感到困惑,我想做的事情是在Access中轻松完成的ASP是
。我将在下面发布Access查询作为参考。

主要思想是查询以层级

的方式相互依赖。这一切在Access中工作正常,但在最后一个查询中

(Qrylevel3CostTotals)有一个标准,我想从一个

HTML表单设置,那就是我在哪里我很困惑!如何在ASP

解决方案中完成这项工作?

QryLevel1CostTotals:


SELECT QryNodesParents.ParentID,QryNodesParents.ParentLabel,

总和(QryLevel2CostTotals.SumOfSumOfBudget)AS SumOfSumOfSumOfBudget,

总和(QryLevel2CostTotals.SumOfSumOfVariation)AS SumOfSumOfSumOfVariation,

总和(QryLevel2CostTotals.SumOfLastOfCommitted)AS SumOfSumOfLastOfCommitted ,

总和(QryLevel2CostTotals.SumOfLastOfActual)AS SumOfSumOfLastOfActual,

Sum(QryLevel2CostTotals.SumOfLastOfWork)AS SumOfSumOfLastOfWork,

Sum(QryLevel2CostTotals.SumOfLastOfAccrual)AS SumOfSumOfLastOfAccrual

来自QryLevel2CostTotals INNER JOIN QryNodesParents ON

QryLevel2CostTotals.TblNodeID = QryNodesParents.TblNodeID

GROUP BY QryNodesParents.ParentID,QryNodesParents.ParentLabel;


QryLevel2CostTotals:


SELECT TblNode.TblNodeID,TblNode.NodeLabel,

总和(QryLevel3CostTo tals.SumOfBudget)AS SumOfSumOfBudget,

总和(QryLevel3CostTotals.SumOfVariation)AS SumOfSumOfVariation,

总和(QryLevel3CostTotals.LastOfCommitted)AS SumOfLastOfCommitted,

总和(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] 。[变化]))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]。[工作]))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# ));

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,


我认为这看起来不错,虽然我''我不熟悉ASP

,我习惯使用以下语法设置我的连接:


Dim rsLevel1Totals

Dim rsLevel1Totals_numRows


设置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)


我已将你的代码放在最后一行,但是我收到错误。你能给我发一个

如何做的例子吗?


谢谢,

Marco

Bob Barrows <再******* @ yahoo.com> schreef in bericht

news:ub ************* @ TK2MSFTNGP11.phx.gbl ...
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...
替换此:
))<#9/1/2003#));
用此:
))< [pDate]));

创建并打开一个连接对象。我假设你把它命名为cn。然后做这个:

dim cn,rs,dEntryDate
dEntryDate = cDate(" 9/1/2003")
cn.QryLevel2CostTotals dEntryDate,rs <你完成了。

HTH,
Bob Barrows

Marco Alting写道:
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:


我仍然对我的疑问感到困惑,我想做的事情就是ASP,这很容易在Access中完成。我将在下面发布Access查询作为参考。主要思想是查询以层次结构的方式相互依赖。这一切在Access中工作正常,但在最后一个查询(Qrylevel3CostTotals)中,有一个标准,我想从HTML表单设置,这就是我感到困惑的地方!我如何在ASP解决方案中完成这项工作?

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(QryLevel3C ostTotals.SumOfVariation)AS SumOfSumOfVariation,
Sum(QryLevel3CostTotals.LastOfCommitted)AS SumOfLastOfCommitted,
Sum(QryLevel3CostTotals.LastOfActual)AS SumOfLastOfActual,
Sum(QryLevel3CostTotals.LastOfWork)AS SumOfLastOfWork,
Sum(QryLevel3CostTotals.LastOfAccrual)AS SumOfLastOfAccrual,
来自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)[[Tbl] MainEntry]。[Actual]),0,[TblMainEntry]。[Actual]))
AS LastOfActual,
Last(IIf(IsNull([TblMainEntry]。[Worked]),0,[TblMainEntry] 。[工作]))
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
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])


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

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




<<<< AS SumOfSumOfSumOfBudget>>>>

我是一名水手的总结

向JImmy Buffet致歉.....


抱歉:>)


Bob Lehmann


" Marco Alting" <毫安*** @ alting-multimedia.nl>在消息中写道

news:7j ********************** @ amsnews03.chello.com ...
<<<<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 ...


我仍然对我的疑问感到困惑,我想做的事情是在Access中轻松完成的ASP。我将在下面发布Access查询作为参考。
主要思想是查询以层次结构的方式相互依赖。这一切在Access中工作正常,但在最后一个查询中(Qrylevel3CostTotals)我有一个标准,我想从一个
HTML表单设置,这就是我感到困惑的地方!如何在ASP
解决方案中完成这项工作?

QryLevel1CostTotals:

SELECT QryNodesParents.ParentID,QryNodesParents.ParentLabel,
Sum(QryLevel2CostTotals。 SumOfSumOfBudget)AS SumOfSumOfSumOfBudget,
总和(QryLevel2CostTotals.SumOfSumOfVariation)AS SumOfSumOfSumOfVariation,
总和(QryLevel2CostTotals.SumOfLastOfCommitted)AS
SumOfSumOfLastOfCommitted,SUM(QryLevel2CostTotals.SumOfLastOfActual)AS SumOfSumOfLastOfActual,
总和( QryLevel2CostTotals.SumOfLastOfWork)AS SumOfSumOfLastOfWork,Sum(QryLevel2CostTotals.SumOfLastOfAccrual)AS SumOfSumOfLastOfAccrual
来自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.Sum OfVariation)AS SumOfSumOfVariation,
Sum(QryLevel3CostTotals.LastOfCommitted)AS SumOfLastOfCommitted,
Sum(QryLevel3CostTotals.LastOfActual)AS SumOfLastOfActual,
Sum(QryLevel3CostTotals.LastOfWork)AS SumOfLastOfWork,
Sum( QryLevel3CostTotals.LastOfAccrual)AS SumOfLastOfAccrual,
来自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。预算)AS
SumOfBudget,Sum(IIf(IsNull([TblMainEntry]。[Variation]),0,[TblMainEntry]。[Variation]))
AS SumOfVariation,
Last(IIf( IsNull([TblMainEntry]。[Committed]),0,[TblMainEntry]。[已提交]))
AS LastOfCommitted,
Last(IIf(IsNull([TblMainEntry]。[Actual] ),0,[TblMainEntry]。[Actual]))AS
LastOfActual,
Last(IIf(IsNull([TblMainEntry]。[Worked]),0,[TblMainEntry]。[Worked]))作为LastOfWork,
最后(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#));
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写道:
Marco Alting wrote:
谢谢鲍勃,

我认为这看起来不错虽然我对ASP还不熟悉,但我习惯了使用
以下语法来建立我的联系:


那么请习惯不要这样做。总是打开一个连接

对象并用它来执行你的数据库活动。

Dim rsLevel1Totals
Dim rsLevel1Totals_numRows

设置rsLevel1Totals = Server .CreateObject(" ADODB.Recordset")


摆脱这一行:rsLevel1Totals.ActiveConnection = MM_ICR_STRING


相反。使用此连接字符串创建并打开连接对象:

dim cn

set cn = server.createobject(" adodb.connection")

cn.open MM_ICR_STRING


摆脱这一行:rsLevel1TotalsQry =" SELECT * FROM QryLevel1CostTotals"


rsLevel1Totals.CursorType = 0
rsLevel1Totals.CursorLocation = 2
rsLevel1Totals.LockType = 1


摆脱这一行:rsLevel1Totals.Open(rsLevel1TotalsQry dEntryDate,rsLeve11Totals)
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)




将其替换为


cn.QryLevel1CostTotals rsLevel1Totals


HTH,

Bob Barrows



Replace it with

cn.QryLevel1CostTotals rsLevel1Totals

HTH,
Bob Barrows


这篇关于将Access查询转换为ASP的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆