Database Tables

J

John Parker

Is it possible to use mutiple table in a database to display information..

I have a table named "Manufacturers" where I have an ID and Manuf Name

I have a product table which is related to the Manufacturer table, but
everytime I try to diplay it displays only the Manuf ID shows and not the
name.

Is it possible to display the name.. I know I can only link to one table in
the wizard, so that is why I ask

John Parker
 
M

MD Websunlimited

Hi John,

Yes, it is using an JOIN in the SQL statement. Here is an example:

SELECT orders.*, product_orders.*
FROM orders INNER JOIN product_orders ON orders.[Order Number] = product_orders.[Order Number];
 
J

John Parker

Thank-you for your help, but I think it is beyond me as I have no way to
translate your example to my need... I think I must be out of my depth
here...

John Parker


MD Websunlimited said:
Hi John,

Yes, it is using an JOIN in the SQL statement. Here is an example:

SELECT orders.*, product_orders.*
FROM orders INNER JOIN product_orders ON orders.[Order Number] = product_orders.[Order Number];


--
Mike -- FrontPage MVP '97-'02
http://www.websunlimited.com
FrontPage Add-ins Since '97 FP 2003 / 2002 / 2000 Compatible

Is it possible to use mutiple table in a database to display information..

I have a table named "Manufacturers" where I have an ID and Manuf Name

I have a product table which is related to the Manufacturer table, but
everytime I try to diplay it displays only the Manuf ID shows and not the
name.

Is it possible to display the name.. I know I can only link to one table in
the wizard, so that is why I ask

John Parker
Click to expand...
Click to expand...
 
K

Kevin Spencer

Hi John,

You can put in the actual field names in the query, which simply joins 2
tables together to form a single result set. Example:

SELECT Manufacturers.Name As ManufacturerName, Products.ProductName As
ProductName
FROM Manufacturers INNER JOIN Products ON Products.ManufacturerID =
Manufacturers.ManufacturerID

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
Big things are made up
of lots of little things.

John Parker said:
Thank-you for your help, but I think it is beyond me as I have no way to
translate your example to my need... I think I must be out of my depth
here...

John Parker


MD Websunlimited said:
Hi John,

Yes, it is using an JOIN in the SQL statement. Here is an example:

SELECT orders.*, product_orders.*
FROM orders INNER JOIN product_orders ON orders.[Order Number] = product_orders.[Order Number];


--
Mike -- FrontPage MVP '97-'02
http://www.websunlimited.com
FrontPage Add-ins Since '97 FP 2003 / 2002 / 2000 Compatible

Is it possible to use mutiple table in a database to display information..

I have a table named "Manufacturers" where I have an ID and Manuf Name

I have a product table which is related to the Manufacturer table, but
everytime I try to diplay it displays only the Manuf ID shows and not the
name.

Is it possible to display the name.. I know I can only link to one
Click to expand...
Click to expand...
table
in
Click to expand...
Click to expand...
 
J

John Parker

Thank-you all I have now figured it out.. However when I enter custom SQL
code I lose the ability to use the flexibility of the "More Options" in
Wizard, is this normal?

John P.


Kevin Spencer said:
Hi John,

You can put in the actual field names in the query, which simply joins 2
tables together to form a single result set. Example:

SELECT Manufacturers.Name As ManufacturerName, Products.ProductName As
ProductName
FROM Manufacturers INNER JOIN Products ON Products.ManufacturerID =
Manufacturers.ManufacturerID

--
HTH,
Kevin Spencer
.Net Developer
Microsoft MVP
Big things are made up
of lots of little things.

John Parker said:
Thank-you for your help, but I think it is beyond me as I have no way to
translate your example to my need... I think I must be out of my depth
here...

John Parker


MD Websunlimited said:
Hi John,

Yes, it is using an JOIN in the SQL statement. Here is an example:

SELECT orders.*, product_orders.*
FROM orders INNER JOIN product_orders ON orders.[Order Number] = product_orders.[Order Number];


--
Mike -- FrontPage MVP '97-'02
http://www.websunlimited.com
FrontPage Add-ins Since '97 FP 2003 / 2002 / 2000 Compatible

Is it possible to use mutiple table in a database to display information..

I have a table named "Manufacturers" where I have an ID and Manuf Name

I have a product table which is related to the Manufacturer table, but
everytime I try to diplay it displays only the Manuf ID shows and
Click to expand...
not
the
name.

Is it possible to display the name.. I know I can only link to one
Click to expand...
table
in
the wizard, so that is why I ask

John Parker
Click to expand...
Click to expand...
Click to expand...
 
K

Kathleen Anderson [MVP - FP]

Yes. If you want to make changes to your criteria or sorting options, you
would go back into the custom SQL.

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
http://www.spiderwebwoman.com/resources/


John Parker said:
Thank-you all I have now figured it out.. However when I enter custom
SQL code I lose the ability to use the flexibility of the "More
Options" in Wizard, is this normal?

John P.


Kevin Spencer said:
Hi John,

You can put in the actual field names in the query, which simply
joins 2 tables together to form a single result set. Example:

SELECT Manufacturers.Name As ManufacturerName, Products.ProductName
As ProductName
FROM Manufacturers INNER JOIN Products ON Products.ManufacturerID =
Manufacturers.ManufacturerID

--
HTH,
Kevin Spencer
.Net Developer
Microsoft MVP
Big things are made up
of lots of little things.

John Parker said:
Thank-you for your help, but I think it is beyond me as I have no
way to translate your example to my need... I think I must be out
of my depth here...

John Parker


Hi John,

Yes, it is using an JOIN in the SQL statement. Here is an example:

SELECT orders.*, product_orders.*
FROM orders INNER JOIN product_orders ON orders.[Order Number] =
product_orders.[Order Number];


--
Mike -- FrontPage MVP '97-'02
http://www.websunlimited.com
FrontPage Add-ins Since '97 FP 2003 / 2002 / 2000 Compatible

Is it possible to use mutiple table in a database to display
information..

I have a table named "Manufacturers" where I have an ID and Manuf
Name

I have a product table which is related to the Manufacturer
table, but everytime I try to diplay it displays only the Manuf
ID shows and not the name.

Is it possible to display the name.. I know I can only link to
one table in the wizard, so that is why I ask

John Parker
Click to expand...
Click to expand...
Click to expand...
 
J

John Parker

I have figured out everything and it works up to the point where I try to
add a filter ie show only a certain manuf..

The code below is giving me the error:

Database Results Wizard Error
The operation failed. If this continues, please contact your server
administrator.

Everything works okay until I add the WHERE info:


SELECT Manufacturers.Manuf, Models.Model, Lamps.Lamp_Desc,
Lamps.Lamp_MSRP_Cdn, Lamps.Lamp_SP_Cdn
FROM Manufacturers INNER JOIN (Lamps INNER JOIN Models ON Lamps.Lamp_ID =
Models.Lamp_Desc) ON (Manufacturers.Manuf_ID = Lamps.Manuf) AND
(Manufacturers.Manuf_ID = Models.Manuf)
WHERE (((Manufacturers.Manuf)="Hitachi"));






Kathleen Anderson said:
Yes. If you want to make changes to your criteria or sorting options, you
would go back into the custom SQL.

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
http://www.spiderwebwoman.com/resources/


John Parker said:
Thank-you all I have now figured it out.. However when I enter custom
SQL code I lose the ability to use the flexibility of the "More
Options" in Wizard, is this normal?

John P.


Kevin Spencer said:
Hi John,

You can put in the actual field names in the query, which simply
joins 2 tables together to form a single result set. Example:

SELECT Manufacturers.Name As ManufacturerName, Products.ProductName
As ProductName
FROM Manufacturers INNER JOIN Products ON Products.ManufacturerID =
Manufacturers.ManufacturerID

--
HTH,
Kevin Spencer
.Net Developer
Microsoft MVP
Big things are made up
of lots of little things.

Thank-you for your help, but I think it is beyond me as I have no
way to translate your example to my need... I think I must be out
of my depth here...

John Parker


Hi John,

Yes, it is using an JOIN in the SQL statement. Here is an example:

SELECT orders.*, product_orders.*
FROM orders INNER JOIN product_orders ON orders.[Order Number] =
product_orders.[Order Number];


--
Mike -- FrontPage MVP '97-'02
http://www.websunlimited.com
FrontPage Add-ins Since '97 FP 2003 / 2002 / 2000 Compatible

Is it possible to use mutiple table in a database to display
information..

I have a table named "Manufacturers" where I have an ID and Manuf
Name

I have a product table which is related to the Manufacturer
table, but everytime I try to diplay it displays only the Manuf
ID shows and not the name.

Is it possible to display the name.. I know I can only link to
one table in the wizard, so that is why I ask

John Parker
Click to expand...
Click to expand...
Click to expand...
 
Top