Relative Newbie: How to compare two columns

[photoguy53]

How does one create a query that will return a record set where the contents of one column is greater than the other?

Table Name=SURVEY_RAW_DATA
Column Name=TOTAL_EXP
Column Name=TOTAL_NX

There are obviously many more columns than those listed above in the table, but these are the two I want to compare. Also the two columns contain numbers.

In SQL I would I write it like this:

Select * FROM SURVEY_RAW_DATA
WHERE TOTAL_EXP > TOTAL NX;

Thanks in advance,
Ken

 

[PHV]

And the problem is ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[photoguy53]

...and the problem is.....
the table is an MS Access table, and it won't accept that syntax....I thought if I went into Sequel Specific/Data Definition dialog box where you can apparantly enter SQL, it would return what I was looking for....apparantly straight sequel doesn't work in access....

Any suggestions?

sorry for not making that clear in the post....

 

[PHV]

it won't accept that syntax
Depending of the real column name, replace this:
WHERE TOTAL_EXP > TOTAL NX
By either this:
WHERE TOTAL_EXP > TOTAL_NX
Or this:
WHERE TOTAL_EXP > [TOTAL NX]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[r937]

straight SQL does work in access

- create a new query
- choose Design View
- close the Show Tables dialog
- main menu View -> SQL View
- paste the SQL
- main menu Query -> Run

vwalah, eh

r937.com | rudy.ca

 

[photoguy53]

Thanks r937! That worked like a charm!

Thanks to PHV as well, with a little modification (putting the column names in [] square brackets) that method worked as well, by putting the where clause in the "Criteria" section of the design view query dialog.