Retreiving records from DB tables into single table 1

[dgdavid]

The following SQL statement keeps generating the syntax error below:

<%SQL= &quot; select tblProducts, tblCustomer, tblProductType, tblPricing, tblOrder, tblOrderDetails where tblProductType.ProductTypeID=tblProducts.ProductTypeID and tblProducts.PriceID=tblPricing.PriceID and tblOrder.CustomerID=tblCustomer.CustomerID and tblOrder.OrderID=tblOrderDetails.OrderID and tblOrderDetails.UPC=tblProducts.UPC&quot;

Set rsOrders = Server.CreateObject(&quot;ADODB.Recordset&quot;)
    		rsOrders.Open SQL, objConn, adOpenDynamic, adLockOptimistic, Cmdtext

Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'tblOrderDetails where tblProductType.ProductTypeID=tblProducts.ProductTypeID and tblProducts.PriceID=tblPricing.PriceID and tblOrder.CustomerID=tblCustomer.CustomerID and tblOrder.OrderID=tblOrderDetails.OrderID and tblOrderDetails.UPC=tblProducts.UPC'.


I have been stuck on this one for a while and cannot recognise the error,
Can someone help please..

All I'm tryng to do is a dynamic retreive of records into i single table..
The DB consists of 10 tables
the data retreive can allow further data manipu;lation

I cannot for the life of me figure out what operator is missing
Thanks

 

[jrbarnett]

Simple answer: There is no FROM clause in your query.

I think the following will work, but it depends partly on the structure of your database.

<%SQL= &quot; select tblProducts, tblCustomer, tblProductType, tblPricing, tblOrder, tblOrderDetails
From tblProductType, tblProducts, tblPricing, tblOrder, tblOrderDetails, tblCustomer
where tblProductType.ProductTypeID=tblProducts.ProductTypeID and tblProducts.PriceID=tblPricing.PriceID and tblOrder.CustomerID=tblCustomer.CustomerID and tblOrder.OrderID=tblOrderDetails.OrderID and tblOrderDetails.UPC=tblProducts.UPC&quot;

John