Trying to compare 2 tables...

[twaccess]

Hi all,

I have a query that works fine in that it produces a comparison between 2 tables. The problem is that it does not return any values where there is an instance on one table but not the other.

I'm afraid that I'm not into SQL yet and hope there is a simple user interface way of dealing with this. The tables I have are...

Areas : Area Id (AI)
Area Desc

Items : Item Code (IC)
Item Desc

Tally : Area Id (AI)
Item Code (IC)
Item Qty Needed (IQN)

Ordered : Area Id (AI)
Item Code (IC)
Item Ordered Qty (IOQ)

The report I am getting is like this...

AI IC IQN IOQ
Lobby Item-x 4 4
Lobby Item-y 4 3
Lobby Item-z 5 5

But the report I would like to see is one where a part that is needed but has not been ordered or vice versa also appears on the report, like this...

AI IC IQN IOQ
Lobby Item-x 4 4
Lobby Item-y 4 3
Lobby Item-z 5 5
Lobby Item-a 4 0
Lobby Item-b 0 3
Lobby Item-c 5 0

This kind of report would enable me to see at a glance across 3500 order lines where I'm short or missing or too much..

Hope you can help with this.

Thanks

TW

 

[Mile-O]

You need to create a LEFT or RIGHT join between your tables.

I trust that you have them related so, double click the relational line, and then select the option that suits you best.