Combing Results of Two DB Tables Into one ASP Table, Formatting Dates

[ejrhodes]

Guys,

Here is the problem I am presented with. The client has two tables, one for payments and one for receipts that are identical. They need me to pull the data from both tables and build a ledger so that the data is sorted by date descending.

It will be displayed in the following format:

Date - Detail Payment Receipt Balance

Depending on whether the value is positive or negative, it goes in the appropriate column. Can I combine the call from the two tables into one SQL call or do I need to read the two tables into two arrays and sort the arrays?

To make matters worse, the "dates" are stored as 8 digit 20030506 in the database and are stored in STRING form. Can I write a SQL Query that uses substrings to put the date in the correct order and do a sort on these dates?

Any help you can provide me with would be appreciated.

[Ribeyed]

Hi,
i think your looking for an SQL join, let me give you an example:

tblPayments

PaymentID*
Amount
PaymentDate
etc.

tblRecipts

ReciptID*
PaymentID
ReciptDate
etc.

Ok so 2 retating tables both with at least 1 field the same in both tables.



sql = "SELECT tblPayments.PaymentID, tblPayments.Amount,
tblPayments.PaymentDate, tblrecipts.ReciptID, tblReciptDate, tblRecipts.PaymentID FROM tblPayments,
tblRecipts WHERE tblPayments.PaymentID = tblRecipts.PaymentID"


This is just one of many sql joins you can do.
Here is another example this time with another where clause:


sql = "SELECT tblPayments.PaymentID, tblPayments.Amount,
tblPayments.PaymentDate, tblrecipts.ReciptID, tblReciptDate, tblRecipts.PaymentID FROM tblPayments,
tblRecipts WHERE tblPayments.PaymentID = tblRecipts.PaymentID AND tblPayments.Amount = 200"


Hope this helps

[ejrhodes]

Originally posted by [SWR]Ribeyed
Hi,
i think your looking for an SQL join, let me give you an example:

tblPayments

PaymentID*
Amount
PaymentDate
etc.

tblRecipts

ReciptID*
PaymentID
ReciptDate
etc.

Ok so 2 retating tables both with at least 1 field the same in both tables.



sql = "SELECT tblPayments.PaymentID, tblPayments.Amount,
tblPayments.PaymentDate, tblrecipts.ReciptID, tblReciptDate, tblRecipts.PaymentID FROM tblPayments,
tblRecipts WHERE tblPayments.PaymentID = tblRecipts.PaymentID"


This is just one of many sql joins you can do.
Here is another example this time with another where clause:


sql = "SELECT tblPayments.PaymentID, tblPayments.Amount,
tblPayments.PaymentDate, tblrecipts.ReciptID, tblReciptDate, tblRecipts.PaymentID FROM tblPayments,
tblRecipts WHERE tblPayments.PaymentID = tblRecipts.PaymentID AND tblPayments.Amount = 200"


Hope this helps


I thoght about a join, but the issue is the records from the two tables are not linked. For instance row 1 in my ASP table can be from the payment table and row 2 can be from the Receipt table. I was thinking about a union query but the column names are not identical between the two tables. Thanks for the feedback however.

[Ribeyed]

hi,

The client has two tables, one for payments and one for receipts that are identical.



I was thinking about a union query but the column names are not identical

:confused:

[ejrhodes]

Originally posted by [SWR]Ribeyed
hi,



:confused:


The content of the colum and numberof colums are the same but the names are different. I guess we can alter the table and rename it so they match up. Either way, I dont see how an inner join works in this situation. The actual page template is

http://www.13datacenter.com/creditors/ledger.html

The two tables are independent in the sense that i am not pulling values abc from table 1 and def from table 2.

btw, thanks for your help. I apologize if my original description was not clear

[Ribeyed]

hi,
so from your templete is the green row to be from table 1 and the next row is to be from table 2?

[Ribeyed]

hi,
if it is the above you want then here is a way you can do it:


<%
set dbConn = server.createobject("ADODB.Connection")
dbConn.open "database"
sql = "SELECT * FROM tblPayments"
set RS1 = dbConn.Execute(sql)
thissql = "SELECT * FROM tblRecipts"
set RS2 = dbConn.Execute(thissql)
while not RS1.EOF %>
<table >
<tr>
<td><%=rs1(0)%></td>
<td><%=rs1(1)%></td>
<td><%=rs1(2)%></td>
</tr>
</table>


<% set rs1 = rs2

%>

<table width="75%" border="1">
<tr>
<td><%=rs2(0)%></td>
<td><%=rs2(1)%></td>
<td>&nbsp;</td>
</tr>
</table>
<%rs2.movenext
set rs2 = rs1
RS1.MoveNext
Wend

dbConn.close
set dbConn = nothing
%>

hope this helps;)