Referring to another table 1

[fiber0pti]

I have two tables Company and products. In a query I get Company.Company and Company.ID. For products I take out products.ID and products.Manufacterer. products.Manufacterer refers to Company.ID. I want to display Company.Company according to the # in products.Manufacterer. How do I do this? I'm have a lot of problems and I already have a WHERE clause in my query.

 

[link9]

Sounds like you are looking for a JOIN

SELECT Company.Company, Company.ID, products.ID, products.Manufacterer
FROM Company
JOIN Company ON products.Manufacterer = Company.ID
WHERE....

If this isn't EXACTLY what you are looking for, then I would suggest using the visual query building tool that comes with Access... it's great.

Just put both those tables in the window (you'll see what I mean), make sure that the little relationship line is drawn between ID and Manufacturer fields, and then fill in whatever else you need either in the SQL view or the bottom part of that same visual window. If you look at the design view of the query builder, this will make perfect sense (I know it's not the best of explanations) ;-)

good luck!
Paul Prewett

 

[fiber0pti]

Is it necessay to have the Where clause?

 

[link9]

Certainly not -- you just said you had one so I was demonstrating where it would go.

Did you get the query to work??

 

[fiber0pti]

Ahh okay. I still get an errror. (I'm using this for ColdFusion)
"Syntax error in FROM clause."

 

[link9]

Post your from clause...

 

[fiber0pti]

SELECT
Company.ID ,
Company.Company ,
products.ID ,
products.Manufacterer
FROM Company ,
products
WHERE products.Manufacterer = 'Company.ID'

 

[link9]

Just cash that WHERE clause... looks like you are trying to use it as a JOIN --

Where that's what the JOIN is for...

Try this and let me know how it works --

SELECT Company.Company, Company.ID, products.ID, products.Manufacterer
FROM Company
INNER JOIN Company ON products.Manufacterer = Company.ID

INNER JOIN selects only records where the id exists in both tables...
JOIN will select all records regardless of whether they exist in the other or not...
LEFT INNER JOIN will select all records where the id exists in the table that you list in the left side of the statement
RIGHT INNER JOIN will select all records where the id exists in the table that you list in the right side of the statement

There is an OUTER JOIN, too... it really starts confusing me after a while, which is why I always suggest to build up a query visually in the tools provided where possible.

Let me know
paul

 

[fiber0pti]

That gives me an error also:
Syntax error in JOIN operation.

 

[link9]

Well, I don't know about Cold Fusion, but I just created a query on a database that I have on my machine:

Simple query with two tables where one field linked both tables...

The above is what I came up with. I guess someone else is going to have to step up and correct me here, because I don't know what else to change. :-(

 

[fiber0pti]

Well thanks for trying. Where can I find those visual tools for Access?

 

[link9]

If you have the data in access in tables, then you can just go in and design a new query in design view --

add the tables that you want and ensure that the proper relationships are set up --

from that point, just right click on the blue bar at the top of that little window and ask for the SQL View -- that will show you the SQL statement to get the data that you're after.

good luck, and sorry I couldn't help more :-(
paul