将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屋！
