Compare two columns in the same table

Topic

New Reply

Hi,
I’m struggling with something I thoought would be easy.
I have a table with 3 columns
ID, Selected, PreviousSelected

The Selected and PreviousSelected would contain the value “Yes”

Code:

ID Selected PreviousSelected
1   Yes	 Yes
2		   Yes
3   Yes
4   Yes

What I want to do is compare the two columns to see if there is a difference between Selected and PreviousSelected.
Basically return a count of the differences. The above would show 3 differences.

I tried using
SELECT tblBranch.BranchID, tblBranch.Selected
FROM tblBranch
WHERE (((tblBranch.Selected)[tblBranch].[PreviousSelected]));
Also tried not in:
SELECT tblBranch.Selected
FROM tblBranch
WHERE (((tblBranch.Selected) Not In (select distinct [tblBranch].[PreviousSelected] from [tblBranch])));
I don’t know what I’m doing wrong but would appreciate assistance.
Thanks,
Scott

Reply | Quote

Replies

You have to treat null (blank) values separately. Try

SELECT BranchID, Selected, PreviousSelected
FROM tblBranch
WHERE (SelectedPreviousSelected) Or (Selected Is Not Null And PreviousSelected Is Null) Or (Selected Is Null And PreviousSelected Is Not Null)

Reply | Quote

You have to treat null (blank) values separately. Try

SELECT BranchID, Selected, PreviousSelected
FROM tblBranch
WHERE (SelectedPreviousSelected) Or (Selected Is Not Null And PreviousSelected Is Null) Or (Selected Is Null And PreviousSelected Is Not Null)

Thanks Hans that did the trick
Scott

Reply | Quote