this is an ACCESS sql statement from my ACCESS database query:

SELECT tblCustomers.*, tblTestOrder.*, tblAbrasions.*, tblAppliedDecoration.*
FROM ((tblCustomers INNER JOIN tblTestOrder ON tblCustomers.CustID = tblTestOrder.CustomerID) INNER JOIN tblAbrasions ON tblTestOrder.TestOrderID = tblAbrasions.TestID) INNER JOIN tblAppliedDecoration ON tblTestOrder.TestOrderID = tblAppliedDecoration.ADTestID;

how can i change it to an ASP SELECT statement?

thanks

wrap it in quotes and add strSQL = before it


or do you mean you want to concatenate it using variables or submitted values?

if so which values would be replaced by variables

or is the whole ADO database and recordset calls you need to know?

thanks. it works. here's my new sql statement:

sql="SELECT tblCustomers.*, tblTestOrder.*, tblAbrasions.* FROM ((tblCustomers INNER JOIN tblTestOrder ON tblCustomers.CustID = tblTestOrder.CustomerID) INNER JOIN tblAbrasions ON tblTestOrder.TestOrderID = tblAbrasions.TestID) WHERE TestRequestNo= '" &PO& "'"

are there limitations as to how many table i can put in the Select statement? the reason for asking this question is that tblCustomers contains only the customer information. tblTestOrder contains the date required, date submit, which test to perform (16 different tests), etc. Each test result is store in its own table with testid as a foreign key to the test result table.

a customer can request multiple tests to be perform. the select statement above only retreive the test result from tblAbrasions; how can i add the rest of the other test results from the other tables?

Also can i use the same variable name in each different table?

many thanks

Is TestRequestNo a string or a number? If it's a number, you're enclosing it in quotes which may be causing your problem.

Also, it's better from a performance standpoint to only include the fields you'll actually want to retrieve in your output from an ADO query.

e.g. SELECT tblCustomers.Field_1, tblCustomers.Field_2, tblAbrasions.Field_1, tblAbrasions.Field_2, etc.