Query from two Tables

[Simon]

I have 2 tables that holds Parts and Products, they have a unique code
and a price

How do i create a query that will have two colums 'unique code' and
'price' and will display all the data from tblParts and tbl Products

Any help would be great


Thanks

Simon

 

[Allen Browne]

So you want to combine the parts and products into one long list?

Use a UNION query. Both parts of the query must have the same columns, of
the same type, in the same order. It will probably end up looking something
like this:

SELECT Parts.[PartID] AS TheCode,
Parts.[PartName] AS TheName,
Parts.[PartPrice] AS ThePrice,
'Part' AS TheType
FROM Parts
UNION ALL
SELECT Products.[ProductID AS TheCode,
Products.[ProductName] AS TheName,
Products.[ProductPrice] AS ThePrice,
'Product' AS TheType
FROM Products
ORDER BY TheCode;

You can't create a UNION query graphically.
However, you can create 2 queries (one on each table),
then switch them to SQL View (View menu),
and paste the two halves into one query.

The alias names (such as TheCode, TheName, ...) are not crucial.

 

[Guest]

Simon - I appologize if this simplistic answer misses the point. In the main
access dialogue box select queries from the objects on the left hand side.
Then click on "Create query in Design view". A "Show Table" dialogue box
will open. Click on the tables that contain the data you wish to include in
the query then click add. These tables will appear in the top section of
your query. Close the Show Table dialogue box. Drag and drop the fields you
wish to include in your query to the field row in the query. Establish any
criteria you may have for the data you wish to examine (leave blank for all).
Choose the sort order then close and save the query.