Table to table comparison

[telpaman]

I have two tables with almost matching data fields:
And I need to compare and match the exacting data to each other.
However, one table may have more items listed or Nulls from one that don't
match the other table.

Here are the tables and field names:
Table 1: ASL Planned data
Fields:Location,Material & Reorder Point
Table 2: On Hand Inventory
Fields:Storage location (location),Last Material (Material), Standard Price
& On Hand Inventory

I basically need the list by location and material (part number) side by
side or Cross-tabbed to show what is on hand, reorder point for that item and
the matching data next to it.

Thanks.

 

[KARL DEWEY]

Use a union query then join it in a select query --
ASL_Material --
SELECT [ASL Planned data].Location, [ASL Planned data].Material
FROM [ASL Planned data]
UNION SELECT [On Hand Inventory].[Storage location], [On Hand
Inventory].[Last Material]
FROM [On Hand Inventory];

SELECT ASL_Material.Location, ASL_Material.Material, [ASL Planned
data].[Reorder Point], [On Hand Inventory].[On Hand Inventory], [On Hand
Inventory].[Standard Price]
FROM (ASL_Material LEFT JOIN [ASL Planned data] ON (ASL_Material.Material =
[ASL Planned data].Material) AND (ASL_Material.Location = [ASL Planned
data].Location)) LEFT JOIN [On Hand Inventory] ON (ASL_Material.Material =
[On Hand Inventory].[Last Material]) AND (ASL_Material.Location = [On Hand
Inventory].[Storage location]);