Selecting Multiple Tables Using SELECT Statment problem

I am Trying to use the SQL SELECT Statment to open records from multiple tables into one recordset.

I know the basic syntax of the Select statment but I wanted to know if its possible to recive the table name from the feild or the record that I am readinf from the recordset in any given moment.

To make things more clear, my example:

-Open a connection

SQL = "SELECT * FROM TABLE1,TABLE2 WHERE TABLE1.UserID=" & uid & " And TABLE2.UserID=" & uid

rs.open sql,conn,3,3

while not rs.EOF

-read from record

rs.movenext
wend
rs.close
conn.close


now, in the part that I read from the record, is their any way I can know if that record is from table1 or table2?

thanks, Ben.


P.S. vlince, I looked a bit in google so u don't have to send me a link unless I am way of the topic I'm looking for...

absolutely, but you'll have to change your SQL statement to


that should work i believe....don't have anything to test it on right now but i will by the time your respond if this doesn't work.

This way I will have to write diffrent code to the records from Table1 and table2 and that would be like opening each of them apart...

I want to make the recordset be like a join of the to tables, with the same feilds, not in diffrent feilds... There is avcourse a problem this way because the ID feild that is AUTONUMBER can be the same for both tables, I don't have a problem with this, but maybe SQL will have...

Is there a way to join the tables temperntaly, only when the script is runing?

you can use UNION or UNION ALL if the data types of the fields you select are the same. i only difference between the two is UNION ALL removes duplicates.

flip it nopoints, UNION removes duplicates i believe

Thanks, I'll Check it out...