Access 2003 - Matching Columns in same table

[hld24]

I have one table with multiple columns and I need to bring back via a query all records that have matching data AND a Review By > 0

IE: S_Money = Money and S_APR = APR, etc.


S_Money Money S_APR APR S_Income Income Reviewed by
50 50 7.8 7.8 40000 40000 1234
35 35 8.1 8.1 33000 33000 2332
77 76 5.6 5.6 48000 48000 4567
56 56 6.1 6.1 69000 49000 6578
44 44 5.8 5.8 51000 51000

What I expect to get back in this query would be the first two rows of information as all columns match AND those records were reviewed. Rows three and four had data that did not match and row five the data matched and no one reviewed the information.
Any help would be appreciated.

 

[jrbarnett]

Select S_Money, Money, S_APR, APR, S_Income, Income, [Reviewed by]
From Yourtable
Where (S_Money = Money) And
 (s_Apr = APR) And
 (s_Income = Income) and
 [Reviewed by] is not null

John

 

[hld24]

Thank you for your quick response. I failed to mention the "null value" fields. The example I enclosed should have also included two columns of information where two of the fields IES_Money and Money) had no value and there by are equal, but the way I had it written and the way I changed it to mirror your input my query brings back no records and I assume it is because of these null value fields.

Here is what I have AFTER the select statement:
WHERE (Data.S_Race_1=Data.Race_1) AND (Data.S_Race_2=Data.Race_2) AND (Data.S_Race_3=Data.Race_3) AND (Data.S_Race_4 = Data.Race_4) and data.[EditUser ID] is not null

There are a total of 78 columns to compare of which many will have No Value.

 

[PHV]

Have a look at the Nz function:
WHERE Nz(S_Money)=Nz(Money) AND ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[hld24]

Thank you. I took a look at the Nz function and put in my query, but I still came back with zero records.

 

[jrbarnett]

Can you post your exact SQL statement? If you have implemented PHV's suggestion fully, it should work.

John

 

[hld24]

WHERE Nz((Data.S_Race_1)=Nz(Data.Race_1)) AND Nz((Data.S_Race_2)=Nz(Data.Race_2)) AND Nz((Data.S_Race_3)=Nz(Data.Race_3)) AND Nz((Data.S_Race_4) = Nz(Data.Race_4)) and data.[EditUser ID] is not null;

 

[PHV]

Why all these misplaced parentheses ????

 

[traingamer]

Is it possible that data.[EditUser ID] is an empty string (i.e "") rather than null?

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[hld24]

That's what I get for not previewing my post. This is what I should have pasted.

WHERE Nz(Data.S_Race_1)=Nz(Data.Race_1) AND Nz(Data.S_Race_2)=Nz(Data.Race_2) AND Nz(Data.S_Race_3)=Nz(Data.Race_3) AND Nz(Data.S_Race_4) = Nz(Data.Race_4) and data.[EditUser ID] is not null;


Written this way it appears to work fine. Now to add the other fields and verify against the Data table.

Thank you.