Combining tables

[craigmorley]

Help needed again please.

I have 2 tables, 1 with a list of PRODUCTS, the other showing al
PRODUCTS which have been allocated an INCLUDE (picking bin in th
warehouse).

I need to show all PRODUCTS from the first table against all PRODUCT
with their relevant INCLUDE from the second table.

I then need to query the 2 tables so that any PRODUCTS which appear i
the first table but do not appear in the second table are thrown into
third table.

This is likely to be quite a simple one to do but I'm not ver
experienced at Access, so anyone who can help will be handsomel
rewarded. Probably!!

Thanks,
Crai

 

[Naresh Nichani MVP]

Hi:

Assume that first table is "tblData1" and second table is "tblData2" and we
join on a common Name field

1. This gives all data from tblData1 irrespespective of whether it is or not
in tblData2.

SELECT tblData1.Name, tblData1.Address, tblData2.Address2
FROM tblData1 LEFT JOIN tblData2 ON tblData1.Name = tblData2.Name;

2. This gives all data from tblData1 where there is a matching record in
tblData2

SELECT tblData1.Name, tblData1.Address, tblData2.Address2
FROM tblData1 INNER JOIN tblData2 ON tblData1.Name = tblData2.Name;

Note how the JOIN is defined in SQL. In Query Designer in Access you can
join field and right click the John Line and click Properties to change Join
Properties.

Regards,

Naresh Nichani
Microsoft Access MVP

 

[craigmorley]

Works like a dream. Many thanks for your help