JOIN recordsets

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Marco Alting
    #1

    JOIN recordsets

    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?


    Tags: None
    • TomB
      #2
      Re: JOIN recordsets

      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" <marco@alting-multimedia.nl> wrote in message
      news:kCI5b.1180 56$cI3.6456286@ amsnews03.chell o.com...[color=blue]
      > Is it possible to INNER JOIN two recordsets in ASP? I don't mean the[/color]
      normal[color=blue]
      > JOIN you would use with two tables, but actually join the recordsets in[/color]
      ASP?[color=blue]
      >
      >[/color]


        Comment

        • Manohar Kamath [MVP]
          #3
          Re: JOIN recordsets

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

          --
          Manohar Kamath
          Editor, .netBooks
          https://www.dotnetbooks.com



          "Marco Alting" <marco@alting-multimedia.nl> wrote in message
          news:kCI5b.1180 56$cI3.6456286@ amsnews03.chell o.com...[color=blue]
          > Is it possible to INNER JOIN two recordsets in ASP? I don't mean the[/color]
          normal[color=blue]
          > JOIN you would use with two tables, but actually join the recordsets in[/color]
          ASP?[color=blue]
          >
          >[/color]


            Comment

            • Marco Alting
              #4
              Re: JOIN recordsets

              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!

                Comment

                • Marco Alting
                  #5
                  Re: JOIN recordsets

                  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!

                    Comment

                    Previous template Next
                    Working...