CODEWITHSUNDEEP
sqlms-access
Glubs
Glubs

Reputation: 106

My sql statement results in zero

SELECT COUNT (Age) AS TwentyToTwentyNineYearOlds
FROM [Copy Of SurveyData]
WHERE Age = 20-29 AND TravelLength = '2+ weeks';

This is my sql statement. I was looking for twenty to twenty-nine-year-olds that were interested in going on a trip for more than 2 weeks. In the database that I am using the actual data is 2+ weeks, I am not looking for Weeks > 2. Every time I run this I get 0. Please tell me what I am doing wrong. I have checked using the database filters and there should be 2 showing up instead of zero. I am using Microsoft Access database. The data for age is called 20-29.

I have fixed this by adding quotations around 20-29.

Upvotes: 0

Views: 33

Answers (2)

Cahaba Data
Cahaba Data

Reputation: 622

So I think you probably should build your query in steps. I assume you're using the query design view and not actually composing an sql statement manually.

First I would do a basic select query, not a count, and with just 1 criteria - not multiple. Sanity check your result and go from there.

www.CahabaData.com

Upvotes: 0

cha
cha

Reputation: 10411

Your expression Age = 20-29 results in this expression: Age = -9. MS Access makes an arithmetic calculation and as a result you are searching for people aged -9 years. What you need to do instead is this:

SELECT COUNT (Age) AS TwentyToTwentyNineYearOlds
FROM [Copy Of SurveyData]
WHERE Age >= 20 and Age <=29 AND TravelLength = '2+ weeks';

Or use the BETWEEN operator:

SELECT COUNT (Age) AS TwentyToTwentyNineYearOlds
FROM [Copy Of SurveyData]
WHERE Age BETWEEN 20 and 29 AND TravelLength = '2+ weeks';

Upvotes: 3

Related Questions