Where Clause in SQL - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Where Clause in SQL

Brent Davis

3 years ago

OK, I have the following SQL statement and would like to add a WHERE clause where i want to only see the records that are -0.25% or greater below last years numbers. The alias field is PercentChangeRev. The calculations work as far as giving me the percent change and they order properly, i just cant figure out the where clause. I have tried the google machine but couldn't get anything to work. Any assistance is appreciated.

SELECT RevPrevYrQ.Customer, RevPrevYrQ.AccountNumber, RevPrevYrQ.Revenue AS RevLastYr, RevThisYr.Revenue AS RevThisYr, CDbl(([RevThisYr]-[RevLastYr])/[RevLastYr]) AS PercentChangeRev
FROM RevPrevYrQ INNER JOIN RevThisYr ON RevPrevYrQ.AccountNumber = RevThisYr.AccountNumber
ORDER BY 5;

	Gregory Clancey @Reply 3 years ago
Brent, maybe enter your query definition string above in an Access query build editor (in the SQL tab) and then add your criterion in the regular tab and switch back to the SQL tab -- finally copy and paste the result. Fool around with this idea. See if you can figure out how to have Access generate the SQL for you.

Brent Davis OP @Reply

3 years ago

Thanks for the reply Gregory! I copied the SQL above from the query I built. From what I have gathered, a where clause in a calculated field has to be handled differently than a normal field, you cannot just put the alias name created for that field as the where condition. I have tried placing the calculation that returns the alias (PercentChangeRev) in the where clause but have not been successful.

	Richard Rost @Reply 3 years ago
Yeah usually you need a second query for that

	Gregory Clancey @Reply 3 years ago
Yes. Your idea is the proper approach. At this stage, when I face an issue like this guy's, I expect to be surprised by the ultimate solution. It's like opening the backyard garden shed in the Spring after it has been sealed since the Autumn. You will find what it is that you must do only if you're willing to face the possibility of some nasty encounter.

	Richard Rost @Reply 3 years ago
Since I live in Florida now that is an issue anymore however pretty much anytime you walk Barefoot in the grass you have to be ready for a nasty encounter. LOL. But I do remember spending many winters in Buffalo New York where I lived most of my life and that first time out in the shed after the snow cleared was indeed apocalyptic at times.

Brent Davis OP @Reply

3 years ago

Fixed. Thanks Richard! Third query worked. I knew I needed to break it down further and when I started thinking back to some of your lessons, it clicked. With your assistance, I am slowly learning this stuff to where I am dangerous. So many cool things you can do that actually make data so readily accessible! Literally save myself so much time in planning my sales calls because the data is right at my fingertips. On to next solution! Have a great day!

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View