simple query problem

  • Thread starter Thread starter Jonathan Wells
  • Start date Start date
J

Jonathan Wells

Is there a way to show a query where if you have a product table and a
custom field table which are linked by product id, to display a list in a
query of all the products and if they have a custom field attached to them.
At the moment I can only get a query displaying the products with custom
fields?
 
You want to show all records from "product" table, and also show fields from
"custom field" table if there are records in this latter table?

SELECT Product.*, [Custom Field Table].*
FROM Product LEFT JOIN [Custom Field Table]
ON Product.[Product ID]=[Custom Field Table].[Product ID];
 
Hi Ken,
Thanks for the info. I have really basic knowledge of access, where abouts
in a query do I put the information you provided?

Thanks.

Ken Snell said:
You want to show all records from "product" table, and also show fields
from "custom field" table if there are records in this latter table?

SELECT Product.*, [Custom Field Table].*
FROM Product LEFT JOIN [Custom Field Table]
ON Product.[Product ID]=[Custom Field Table].[Product ID];
--

Ken Snell
<MS ACCESS MVP>


Jonathan Wells said:
Is there a way to show a query where if you have a product table and a
custom field table which are linked by product id, to display a list in a
query of all the products and if they have a custom field attached to
them. At the moment I can only get a query displaying the products with
custom fields?
Click to expand...
Click to expand...
 
What I posted is the SQL statement (generic) that you would use to create
the query. Create a new query (design view), select no tables, close the
"Add table" window, and then select the SQL view from top left side of
toolbar. Into that open window, paste the SQL statement that I provided,
except change the names of the tables (Product, Customer Field Table) and
the field names (Product ID) to the correct names.
--

Ken Snell
<MS ACCESS MVP>



Jonathan Wells said:
Hi Ken,
Thanks for the info. I have really basic knowledge of access, where abouts
in a query do I put the information you provided?

Thanks.

Ken Snell said:
You want to show all records from "product" table, and also show fields
from "custom field" table if there are records in this latter table?

SELECT Product.*, [Custom Field Table].*
FROM Product LEFT JOIN [Custom Field Table]
ON Product.[Product ID]=[Custom Field Table].[Product ID];
--

Ken Snell
<MS ACCESS MVP>


Jonathan Wells said:
Is there a way to show a query where if you have a product table and a
custom field table which are linked by product id, to display a list in
a query of all the products and if they have a custom field attached to
them. At the moment I can only get a query displaying the products with
custom fields?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top