Retrieve data from multiple tables in Access

[nk9100]

Hi,

Probably a simple question, I want to get data from 5 tables to show all the data from them on the same page.

I use E_Mail as a request.Querystring as the lookup, and that E_Mail 'key', is in each of the tables.

What is the best way of doing this?

Cheers

Richard

Life is a journey that always ends up in the place

 

[Sheco]

It depends... more info is needed?

Are you wanting some fields from each of the 5 tables? If so perhaps you can construct a SQL statement that joins the tables.

Do each of the tables have the same field layout and you want the same fields from each? If this is what you want then look then you can separate each query with a UNION to return them all in a single recordset.

If you want different things from different tables and they are not related such that joins are not appropriate then there is nothing stopping you from just creating 5 separate ADO recordsets or simply reusing the one you have 5 times..

 

[DotNetGnat]

here is one way...

SELECT field1, field2 FROM mytable1 WHERE E_mail='blah'
UNION ALL
SELECT field1, field2 FROM mytable2 WHERE E_mail='blah'
UNION ALL
SELECT field1, field2 FROM mytable3 WHERE E_mail='blah'
UNION ALL
SELECT field1, field2 FROM mytable3 WHERE E_mail='blah'
UNION ALL
SELECT field1, field2 FROM mytable4 WHERE E_mail='blah'

second way...

SELECT t1.field1, t2.field1, t3.field1, t4.field1, t5.field1 from mytable t1, mytable t2, mytable t3, mytable t4,mytable t5 WHERE
t1.ID= t2.ID AND
t2.ID=t3.ID AND
t3.ID=t4.ID AND
t4.ID=t5.ID AND
t1.Key='blah'

-DNG

 

[nk9100]

Hi, thanks for the prompt reply.

All the fields are different, and want to retrieve every field, (Apart from the E_Mail)

I am currently trying with this:

strSQL = "Select * From tblDetails, tblFundingUsed WHERE E_Mail = '" &  request.QueryString("E_Mail") & "'"

which returns the following error:

The specified field 'E_Mail' could refer to more than one table listed in the FROM clause of your SQL statement.

Which I am guessing is beacause I am retrieving the E_Mail field twice?, so if I were to specify each field seperatly might that work?

Life is a journey that always ends up in the place

 

[DotNetGnat]

do this:

strSQL = "Select * From tblDetails, tblFundingUsed WHERE tblDetails.E_Mail = '" & request.QueryString("E_Mail") & "' AND tblDetails.ID=tblFundingUsed.ID"

You need to join your tables on a common key...

-DNG

 

[Sheco]

Yeah be careful with the joins or you could end up with a cross product cartesian result.

Try opening Access and making a new query in design view and then click the little button in the top left that puts you into plain SQL mode. Then work out the syntax there and after you get it right so it returns the results you want only then move it into your ASP. This will help you distinguish SQL syntax errors from ASP errors.