JOIN recordsets

[Marco Alting]

Is it possible to INNER JOIN two recordsets in ASP? I don't mean the normal
JOIN you would use with two tables, but actually join the recordsets in ASP?

 

[TomB]

It would make more sense to get the data in a joined state, but I'll assume
you have a good reason not to.
You could create a new recordset and add the values from the two recordsets.
You could move everything to an array.

I think we'd need more detail to be helpful

 

[Manohar Kamath [MVP]]

In a way, yes... you will have to use the SHAPEd queries to do this.

 

[Marco Alting]

I'm trying to implement the SHAPEd queries, but I'm not sure how to
translate my Access queries to the shaped ones. Let me explain a litle
bit further; I have three levels of values, the lowest level being a Sum
and LastOf fields of cost items (thats the first query lets call it
Qry3), Then in Qry2 a level higher, all values of Qry3 are Summed again
using a related table and an ID. And Qry1 is a Summing of all values in
Qry2. So in Access it goes something like this for Qry2:

Sum(Qry3.SumOfBudget) AS SumOfSumOfBudget

I the line above, the SumOfbudget is referenced from the Qry3 query. Can
I do the same call in the SHAPEd query?

 

[Marco Alting]

Here's my (not working) SHAPEd query, can you tell me whats wrong here:

"SHAPE {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 "_
& "FROM TblNode"_
& "GROUP BY TblNode.TblNodeID, TblNode.NodeLabel} "_
&"APPEND {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].[EntryDa
te])))<#9/1/2003#))"_
& "} AS QryLevel3CostTotals "_
&"RELATE {TblNodeID TO TblNodeID}"