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

"Marco Alting" <ma***@alting-multimedia.nl> wrote in message
news:kC******** **************@ amsnews03.chell o.com...

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?

[Manohar Kamath [MVP]]

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

--
Manohar Kamath
Editor, .netBooks
www.dotnetbooks.com
"Marco Alting" <ma***@alting-multimedia.nl> wrote in message
news:kC******** **************@ amsnews03.chell o.com...

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?

[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.SumOfB udget) AS SumOfSumOfBudge t

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

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Marco Alting]

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

"SHAPE {SELECT TblNode.TblNode ID, TblNode.NodeLab el, "_
& "Sum(QryLevel3C ostTotals.SumOf Budget) AS SumOfSumOfBudge t, "_
& "Sum(QryLevel3C ostTotals.SumOf Variation) AS SumOfSumOfVaria tion, "_
& "Sum(QryLevel3C ostTotals.LastO fCommitted) AS SumOfLastOfComm itted, "_
& "Sum(QryLevel3C ostTotals.LastO fActual) AS SumOfLastOfActu al, "_
& "Sum(QryLevel3C ostTotals.LastO fWork) AS SumOfLastOfWork , "_
& "Sum(QryLevel3C ostTotals.LastO fAccrual) AS SumOfLastOfAccr ual "_
& "FROM TblNode"_
& "GROUP BY TblNode.TblNode ID, TblNode.NodeLab el} "_
&"APPEND {SELECT TblNode.TblNode ID, TblNode.NodeLab el,
TblMainEntry.cb sID, TblCBSandBudget .CBSdescription , " _
& "Sum(TblCBSandB udget.Budget) AS SumOfBudget, "_
&
"Sum(IIf(IsNull ([TblMainEntry].[Variation]),0,[TblMainEntry].[Variation]
)) AS SumOfVariation, " _
&
"Last(IIf(IsNul l([TblMainEntry].[Committed]),0,[TblMainEntry].[Committed
])) AS LastOfCommitted , " _
& "Last(IIf(IsNul l([TblMainEntry].[Actual]),0,[TblMainEntry].[Actual]))
AS LastOfActual," _
& "Last(IIf(IsNul l([TblMainEntry].[Worked]),0,[TblMainEntry].[Worked]))
AS LastOfWork," _
&
"Last(IIf(IsNul l([TblMainEntry].[Accrual]),0,[TblMainEntry].[Accrual]))
AS LastOfAccrual," _
&
"Last(IIf(IsNul l([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].[EntryDa
te])))<#9/1/2003#))"_
& "} AS QryLevel3CostTo tals "_
&"RELATE {TblNodeID TO TblNodeID}"

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!