Join tables in Access

how do i join tables in access and how do i get asp to output information from two separate tables that have the same primary key?

select * from table1 inner join table2 on table1.primKey = table2.primKey

thanks. do i need to create another odbc connection for the second table before i write this statement?

Give this a shot...

<%
db = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=whatever.mdb"

set rs = server.createobject("adodb.recordset")
sql = "select Table1.Field1 as Field1, Table1.Field2 as Field2,"
sql = sql & " Table2.Field1 as Field3, Table2.Field2 as Field4"
sql = sql & " from Table1"
sql = sql & " INNER JOIN Table2 ON Table1.Id = Table2.Id"
rs.open sql, db, 3, 3
if not rs.eof then

response.write rs("Field1") & "<br>"
response.write rs("Field2") & "<br>"
response.write rs("Field3") & "<br>"
response.write rs("Field4") & "<br>"

end if
rs.close
set rs = nothing
%>

ok. got the join table thing to work. but now from the page before it i have a search where it searches a date from table2. and then the results page needs to give me my results from both table1 and table2.
any ideas on the search script and the results script?

Assuming that Field2 from Table2 is the date field you would like to search and you are using Access, you can add this clause to your select statement. You must enclose the Date Value you are passing with # signs.

" where Table2.Field2 = #" & MyDate & "#"

If this doesn't seem to work try converting the value in your where clause to a date format, "cDate()", as follows:

" where Table2.Field2 = #" & cDate(MyDate) & "#"

Also, the part below that says MyDate = request("MyDate") is just the page requesting the date value from the previous page. You need to request it in order to include it into the where clause.


<%
db = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=whatever.mdb"

MyDate = request("MyDate")

set rs = server.createobject("adodb.recordset")
sql = "select Table1.Field1 as Field1, Table1.Field2 as Field2,"
sql = sql & " Table2.Field1 as Field3, Table2.Field2 as Field4"
sql = sql & " from Table1"
sql = sql & " INNER JOIN Table2 ON Table1.Id = Table2.Id"
sql = sql & " where Table2.Field2 = #" & MyDate & "#"
rs.open sql, db, 3, 3
if not rs.eof then

response.write rs("Field1") & "<br>"
response.write rs("Field2") & "<br>"
response.write rs("Field3") & "<br>"
response.write rs("Field4") & "<br>"

end if
rs.close
set rs = nothing
%>

Something must be wrong with my search because if I leave out the MyDate stuff it displays the information properly. the moment i put a search function before it, it doesn't work. the database searches too long that i just get a server is too busy error. and there are only 4 entries in Table 2.

if u r calling from another page for MyDate, you need to either place a request.form or request.querystring depending on your previous page condition.