Custom Query/Multiple Tables in FP '00

[diana5]

Hello-

I'm attempting to read from 3 tables in an Access '97 database. I'm writing a custom SQL statement and am "debugging" at the line level. I've come up with the following error and am having trouble finding documentation. I believe I have a syntax problem OR the issue has to do with using JOIN versus UNION. I've tried both by the way. Which is the correct operator in this instance? Please note that I never made it to referencing the 3rd table. Figured when I had 2 working than I'd go all out and throw in the third!

The other component is that I have a one field form asking the user for a ProductNumber. According to FP '00 I should be putting %% on either side of the form field. I do that and I get no where also.

Code:
======
SELECT* FROM T_ProductIngredients
WHERE [ProductNumber] = :roductNumber::

JOIN SELECT * FROM T_BrandTelephone
WHERE [ProductNumber]= :roductNumber::

Error:
-------
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in
query expression '[ProductNumber] = 1



JOIN SELECT * FROM T_BrandTelephone

WHERE [ProductNumber]= 2'.

Source: Microsoft OLE DB Provider for ODBC Drivers
Number: -2147217900 (0x80040e14)

****************************************************

2nd Code Attempt:
==================
SELECT [ProductNumber], [ProductDescription], [Size], [Brand],
[Ingredients] FROM T_ProductIngredients
WHERE [ProductNumber] = %%ProductNumber%%

UNION SELECT [Brand] FROM T_BrandTelephone
WHERE [ProductNumber] = %%ProductNumber%%

Error:
-------
[Microsoft][ODBC Microsoft Access Driver] Syntax error in
query expression '[ProductNumber] = %%ProductNumber%%'.

Source: Microsoft OLE DB Provider for ODBC Drivers
Number: -2147467259 (0x80004005)

*********************************************

Any sources you can point me to on line are fine OR code attempts.

Other Information:
O/S: '98
Front Page '00
Access '97
Working directly off the server at ISP site.

Thank you ahead for your time and thoughts!

 

[diana5]

I've actually figured out part of my own problem but am currently only pulling from 2 tables.

The code I entered into Custom Query is as follows:

SELECT T_ProductIngredients.ProductNumber, T_ProductIngredients.ProductDescription,
T_ProductIngredients.Size, T_ProductIngredients.Ingredients, T_ProductIngredients.Brand,
T_BrandTelephone.Supplier
FROM T_ProductIngredients, T_BrandTelephone
WHERE T_ProductIngredients.Brand =T_BrandTelephone.Brand AND (ProductNumber = ':roductNumber::')

In addition, as long as I didn't put any validation on the field, which is identified as text but actually stores only numbers (I think) in the database, it works fine. I've found a lot of resources on the web but had to go through it because much of the information contradicted itself. One site would say quotes and another would say not, etc. etc.

The key is persistance!

Thanks-

Diana

 

[diana5]

Ok... to add the 3rd table the code is as follows (was simpler than I thought):

SELECT T_ProductIngredients.ProductNumber, T_ProductIngredients.ProductDescription,
T_ProductIngredients.Size, T_ProductIngredients.Ingredients, T_ProductIngredients.Brand,
T_BrandTelephone.Supplier, T_UPCProductNumbers.OrgKosherCode

FROM T_ProductIngredients, T_BrandTelephone, T_UPCProductNumbers

WHERE T_ProductIngredients.Brand = T_BrandTelephone.Brand AND
T_ProductIngredients.ProductNumber = T_UPCProductNumbers.ProductNumber AND
(T_ProductIngredients.ProductNumber = ':roductNumber::')

The issue was only that in the WHERE I needed to indicate the table I was pulling the product number from. This shows in the ( ).

You'll note that I prefix the tables in my database with a T which is what that is.

That's all!

Diana