[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 774
  • Last Modified:

Combine two recordsets in asp.

Hello everyone,

My  limited knowledge in queries is frustrating therefore I come forth to ask for help. I have two recordset that I would like to combine into one.


First recordset:

SELECT *
FROM Trusts
WHERE trust_status = 'Open'
ORDER BY Trust_Number DESC

Second recordset:

SELECT Sum(hcredit - hDebit) AS Sumemoney
FROM thistory
WHERE thistory.hTrust_number=  ____                  <--------- this is were the trust number from the first query should be placed but I cannot figure out how.

The first query gives me a list of all the records and I would like to get the Trust_number and query a different table to get the SUM for that Trust_number.

Hope this is enough information and thank you in advance.

0
skyfreedomdotnet
Asked:
skyfreedomdotnet
  • 5
  • 4
  • 2
  • +3
1 Solution
 
rockiroadsCommented:
can u not join the query

SELECT Trusts.Field1, Trusts.Field2, Sum(hcredit - hDebit) AS Sumemoney
FROM Trusts, tHistory
WHERE Trusts.trust_id = tHistory.hTrust_number
AND trust_status = 'Open'
GROUP BY Trusts.Field1, Trusts.Field2


Thats if there is a direct join, this gets all the matching trusts and histories

0
 
rockiroadsCommented:
oh, forgot your ORDER BY, that goes as the last line
0
 
skyfreedomdotnetAuthor Commented:
Rockiroads im not sure i understand your post. There are two tables with a comon field.

Table 1 called TRUSTS with field Trust_Number. Table 2 called THISTORY with field Htrust_Number.

In THISTORY trust 20060023 has Credit (hcredit) of $1000 and debit (hdebit) of $0 so sum should be $1000.

Lets say I want pull up Trust_Number 20060023 from table one, then I want to query table two for all the 20060023 records and do a SUM of THISTORY.hcredit and THISTORY.hdebit

Sorry Im not sure how to explain it
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
csachdevaCommented:
You can create new recordset to join 2 recordsets

set rs=server.CreateObject("ADODB.recordset")
rs.open(parametters here)
while not RS1.eof
    rs.AddNew
    rs.("name")=RS1("name")
    rs.("score")=RS1("score")
RS1.movenext() 'or some method to move cursor
wend

while not RS2.eof
    rs.AddNew
    rs.("name")=RS2("name")
    rs.("score")=RS2("score")
RS2.movenext() 'or some method to move cursor
wend

Hope it will help u to sort your query

Regards,
Chetan Sachdeva
0
 
rockiroadsCommented:
The history table has what u call a trust number. This trust number the unique key to the trust table. Is that right?
Well, that means, u can create one sql which allows you to join the two tables together, this avoids having to create two recordsets

pick field1, field2 from the trust table
sum up hcredit - hdebit for that one trust

SELECT Trusts.Field1, Trusts.Field2, Sum(hcredit - hDebit) AS Sumemoney


these are the tables we get

FROM Trusts, tHistory


we define the relationship between the two

WHERE Trusts.trust_id = tHistory.hTrust_number


add in your filtering

AND trust_status = 'Open'


because we use, sum, we have to have a group by of all the fields we selected i.e. field1, field2, not the fields on the sum
GROUP BY Trusts.Field1, Trusts.Field2

and finally your order by


now u just have one recordset
0
 
zawedikianCommented:
hi

i would say u should do it like this:

SELECT Sum(hcredit - hDebit) AS Sumemoney
FROM thistory
WHERE thistory.hTrust_number IN (SELECT hTrust_number
     FROM Trusts
    WHERE trust_status = 'Open'
    ORDER BY Trust_Number DESC)  

it should work.

zeina
0
 
Arthur_WoodCommented:
try this:

SELECT hTrust_number ,Sum(hcredit - hDebit) AS Sumemoney
FROM thistory
WHERE thistory.hTrust_number IN (SELECT hTrust_number
     FROM Trusts
    WHERE trust_status = 'Open')  
GROUP BY hTrust_number

AW
0
 
skyfreedomdotnetAuthor Commented:
Trust Number      Trust Date      Seller      Buyer      Address      List Agent      Sell Agent      E-Money      Sales Price      Status
2005027                1/17/2005      Eguren      Shaw      201 W 1st      Rose      Rose      $100       73000      Sale Fell
2004113      3/26/2004 8:11      Walker      Priest Lake      9200 S Five Mile      FSBO      Kerry      $5,000       175000      Open
2005261      4/25/2005      Sun Country      Aiani      Lot 8 Block 1 Kipling Way      Remax      B/J/T      $500       149950      Open
2005266      4/26/2005      Aspen Homes      Monchar      2974 W Divide Creek      C/B      Vladimir      $500       186900      Open
2005281      4/29/2005      Sun Country      Aiani      Lot 7 Blk 1 Kipling Way      Remax      B/J/T      $500       161478      Open
0
 
skyfreedomdotnetAuthor Commented:
QUERY:

SELECT *
FROM Trusts
WHERE trust_status = 'Open'
ORDER BY Trust_Number DESC

RESULTS:

Trust Number      Trust Date      Seller                 Buyer               Address                            List Agent          Sell Agent       >>> E-Money <<<<       Sales Price      Status
2005027         1/17/2005      Jane                     Shaw             123 Main Street                       Rose                    Rose                 $100                     73000        Sale Fell
2004113                3/26/2004      Jane                  Lake              123 Main Street                       FSBO                     Kerry                $5,000                       175000           Open
2005261                4/25/2005      John                 Aiani              123 Main Street                             B/J/T                       Jane                 $500                        149950              Open
2005266                4/26/2005      Bob                Monchar           123 Main Street                           C/B                   Vladimir                $500                            186900          Open
2005281                4/29/2005      Beth                     Aiani              123 Main Street                             Rem                      B/J/T                   $500                       161478             Open

Sorry previous post was incomplete.

As you can see the query above gives me all the information from TRUSTS table and in that table there's field called E-MONEY which is being populated by another method that doesnt work well. So I would like to have the E-MONEY field be populated by a  Sum(hcredit - hDebit) AS Sumemoney from the THISTORY table fields HCREDIT and HDEBIT for that trust number.

I have tried the above post but they give me the grand total for the THISTORY table not for the specific trust.
Thanks everyone for time.


0
 
zawedikianCommented:
SELECT Trusts.TrustNumber,Trusts.Trust Date, ... , sum (THISTORY.HCREDIT,THISTORY.HDEBIT) as E-Money
FROM Trusts,THISTORY
WHERE trust_status = 'Open'

and Trusts.TrustNumber = THISTORY.TrustNumber
ORDER BY Trusts.Trust_Number DESC

zeina
0
 
rockiroadsCommented:
skyfreedomdotnet, what fields do u exactly want printing?
I know u want the sum, that is already handled, but what else

the example I gave u

SELECT Trusts.Field1, Trusts.Field2, Sum(hcredit - hDebit) AS Sumemoney
FROM Trusts, tHistory
WHERE Trusts.trust_id = tHistory.hTrust_number
AND trust_status = 'Open'
GROUP BY Trusts.Field1, Trusts.Field2


if u change Field1 to what u want

for every field u select bar those in the Sum field, u have to put by groupby


e.g. trust number

SELECT Trusts.Trust_id, Sum(thcredit - hDebit) AS Sumemoney
FROM Trusts, tHistory
WHERE Trusts.trust_id = tHistory.hTrust_number
AND trust_status = 'Open'
GROUP BY Trusts.Field1, Trusts.Field2


this assumes the field trust_id is what it is defined as in the Trusts table

0
 
skyfreedomdotnetAuthor Commented:
Well guys thanks for your help thus far.

I have tried every response and have not yielded the results i needed.

Here is the code for the page:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/Trusts.asp" -->
<%
Dim Alltrusts
Dim Alltrusts_numRows

Set Alltrusts = Server.CreateObject("ADODB.Recordset")
Alltrusts.ActiveConnection = MM_Trusts_STRING
Alltrusts.Source = "SELECT *  FROM Trusts"
Alltrusts.CursorType = 0
Alltrusts.CursorLocation = 2
Alltrusts.LockType = 1
Alltrusts.Open()

Alltrusts_numRows = 0
%>
<%
Dim TrustSum
Dim TrustSum_numRows

Set TrustSum = Server.CreateObject("ADODB.Recordset")
TrustSum.ActiveConnection = MM_Trusts_STRING
TrustSum.Source = "SELECT Sum(hcredit - hDebit) AS Sumemoney  FROM thistory  WHERE thistory.hTrust_number=" & <%=(Alltrusts.Fields.Item("Trust_emoney").Value)%>
TrustSum.CursorType = 0
TrustSum.CursorLocation = 2
TrustSum.LockType = 1
TrustSum.Open()

TrustSum_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = 10
Repeat1__index = 0
Alltrusts_numRows = Alltrusts_numRows + Repeat1__numRows
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>
<table width="59%"  border="1">
  <tr>
    <td width="27%">Trust Number </td>
    <td width="27%">Trust status </td>
    <td width="46%">Trust emoney </td>
</tr>
  <%
While ((Repeat1__numRows <> 0) AND (NOT Alltrusts.EOF))
%>
  <tr>
    <td><%=(Alltrusts.Fields.Item("Trust_Number").Value)%></td>
    <td><%=(Alltrusts.Fields.Item("Trust_Status").Value)%></td>
    <td><%=(Alltrusts.Fields.Item("Trust_emoney").Value)%></td>
  </tr>
  <%
  Repeat1__index=Repeat1__index+1
  Repeat1__numRows=Repeat1__numRows-1
  Alltrusts.MoveNext()
Wend
%>
</table>
</body>
</html>
<%
Alltrusts.Close()
Set Alltrusts = Nothing
%>
<%
TrustSum.Close()
Set TrustSum = Nothing
%>

Basicly I am trying to populate the Emoney field for each trust with the sum from thistory table for that trust.
0
 
gstiegerCommented:
SELECT Trusts.Trust_Number
,           Trusts.Trust_Status
,            ISNULL(History.Trust_EMoney, 0) Trust_EMoney
FROM Trusts
LEFT JOIN (
SELECT hTrust_number Trust_Number
,           Sum(hcredit - hDebit) Trust_EMoney
FROM   thistory
GROUP BY hTrust_number
) History
ON History.Trust_Number = Trusts.Trust_Number
ORDER BY Trusts.Trust_Number DESC
0
 
skyfreedomdotnetAuthor Commented:
GSTIEGR thank you for the code.
I worked abit on fixing some of the field names but it worked.

But I have another questions to ask. Could it be possible to narrow down the criteria to something like this: FROM Trusts  WHERE Trusts.Trust_status = "Open" OR "Sale Fell"?

Here is the code:

SELECT Trust_Address, Trust_Buyer, Trust_Close_Date, Trust_Date, Trust_emoney, Trust_Listing_Agent, Trust_Listing_Agent_Comm, Trust_Number, Trust_Office, Trust_Sales_Price, Trust_Sell_Agent, Trust_Seller, Trust_Selling_Agent_Comm, Trust_Status
FROM Trusts
LEFT JOIN (SELECT hTrust_number, Sum(hcredit - hDebit) as Trust_EMoney
FROM   thistory
GROUP BY hTrust_number) tHistory ON tHistory.hTrust_Number = Trusts.Trust_Number
ORDER BY Trusts.Trust_Number ASC

This gives me all the trust information from the TRUSTS table and the sum of the THISTORY table from fields HCREDIT, HDEBIT for every trust.
0
 
gstiegerCommented:
Yes - as follows:

SELECT Trust_Address, Trust_Buyer, Trust_Close_Date, Trust_Date, Trust_emoney, Trust_Listing_Agent, Trust_Listing_Agent_Comm, Trust_Number, Trust_Office, Trust_Sales_Price, Trust_Sell_Agent, Trust_Seller, Trust_Selling_Agent_Comm, Trust_Status
FROM Trusts
LEFT JOIN (SELECT hTrust_number, Sum(hcredit - hDebit) as Trust_EMoney
FROM   thistory
GROUP BY hTrust_number) tHistory ON tHistory.hTrust_Number = Trusts.Trust_Number
WHERE Trusts.Trust_status IN ('Open',''Sale Fell')
ORDER BY Trusts.Trust_Number ASC
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 5
  • 4
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now