UNION of Tables

neo_jakey · January 31st, 2006, 02:41 PM

Hi fellow programmers,

I have run into unfamiliar territory and could use some assistance. I am making a stock control system that has a table for entries(EntradasAlmacen) in to the warehouse and another table(NonSerials) for removals. I want to create a report that shows a particular product that user has selected between a time frame 1st/Jan/2006 to 1st/Feb/2006.

I am trying to tie the two table together inside an SQL statement as follows:

Code:

<% Set oRSbt=Server.CreateObject("ADODB.recordset")
strSQL = "SELECT ProductID, AlmacenID, Fecha, Quantity FROM EntradasAlmacen" _
       & " WHERE ProductID = '" & Request.Form("ddProduct") & "' ORDER BY Fecha" _
       & " UNION SELECT ProductID, AlmacenID, Fecha, Quantity FROM NonSerials" _
       & " WHERE ProductID = '" & Request.Form("ddProduct") & "' ORDER BY Fecha;"
oRSbt.open strSQL, "DSN=sanmarino" %>

When I loop through the entries I call a particular record as follows:

Code:

<%=oRSbt("Quantity")%>

However, I want this "Quantity" to be shown as either - or + depending if it is "EntradasAlmacen" or a "NonSerials", so that the report reflects that it is an Entry or Exit of stock.

I haven't incorporated the date aspect of the report yet as I am too busy trying to resolve this problem.

Any help will be greatly received.

Many thanks,

Paul
[email protected]

Imar · January 31st, 2006, 03:00 PM

Hi Paul,

You can add an additional column to your resultset on the fly that determines the source table, like this:

SELECT Column1, Column2, 'EntradasAlmacen' AS QtyType FROM Table1
SELECT Column1, Column2, 'NonSerials' AS QtyType FROM Table2

Then in your ASP, you can use oRSbt("QtyType") to determine whether you're working with a EntradasAlmacen or a NonSerials.

Hope this helps,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

neo_jakey · January 31st, 2006, 03:08 PM

Worked a treat... Many Many thanks

Paul