将Access查询转换为ASP [英] Translate Access queries to 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屋!