CODEWITHSUNDEEP
sqlms-access-2010
aztekk
aztekk

Reputation: 1

unmatched query

I'm having trouble with getting an unmatched query to work as I want. I'm using Access 2010 and it's an .accdb application.

I have two tables. Day1 and Day2

They both have columns: position, artno, artdesc, qty

The column position is a complete list of storage positions and a position is listed even if it's empty or not.

What I want to do, is to compare Day1 and Day2 and produce a list of positions that is not exactly the same, as the day before. I.E other article, different quantity of article and even if an article was stored on the position Day1, but no longer exist on it Day2.

Call it 0-stock inventory if you will.

Any ideas?

Upvotes: 0

Views: 275

Answers (2)

Bohemian
Bohemian

Reputation: 425198

select *
from Day1
join Day2 on Day1.position = Day2.position
    and
    (
      (Day1.artno != Day2.artno 
        or (Day1.artno is null and Day2.artno is not null) 
        or (Day1.artno is not null and Day2.artno is null)
      )
      or
      (Day1.qty!= Day2.qty
        or (Day1.qty is null and Day2.qty is not null) 
        or (Day1.qty is not null and Day2.qty is null)
      )
   )

This caters for "not there" being stored as a null

Upvotes: 1

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79969

Try LEFT JOIN like so:

SELECT *
FROM Day1 d1
LEFT JOIN Day2 ON d1.position = d2.position

This will include unmatched rows, i.e rows which are no longer exist in Day2 table.

Upvotes: 0

Related Questions