QUERY (RANGE OF AGE CRITERIA?) - I'm totally unsure.

[Rudurk]

I have a table in MS Access with the fields

AutoNumber	Study ID	Activity	Age Min	Age Max
1	A01	Football	3	28
2	A01	Swimming	6	12
3	A01	Cycling	5	14
4	A01	PE Classes	5	16
5	A01	Basketball	13	16
6	A01	Gym Various/Personal Fitness	15	34
7	A01	Football	31	34
8	A02	Triathlon	31	34
9	A02	Football	10	18
10	A02	Golf	33	34

Now I seriously can't believe I can't think of a single way to query this table to get any one who played football
between the ages of 5 and 16

So Basically I would want it to return

AutoNumber	Study ID	Activity	Age Min	Age Max
1	A01	Football	3	28
9	A02	Football	10	18

So things that won't work
Age Min >=5 (Obviously its gonna filter out age min 3 coz its looking for everything is 5 or over
Age Min <=5 (It will exclude age min 10 coz that doesn't fit the criteria)
Age Min between 5 and 16 (Nope same problem as the first one 3 doesn't count)

So I need a query that counts Age Min and Max as a range and then checks if 5 or 16 is within that range. I can't think of how I can do this without having to write a function.. unfortunately a function is useless to me as it cannot be used in a Excel Data Connection.

So then.... HOW DO I QUERY THIS? My brain is drain it won't work anymore.

[ItsMe]

HOW DO I QUERY THIS?

I would start with the BETWEEN operator. You can type BETWEEN directly into the Criteria field of the Design Grid while in Design View of your Query.

Maybe start by hardcoding values and then look at SQL view. You can replace hardcoded values with variables, later.
BETWEEN 5 AND 6

http://www.w3schools.com/sql/sql_between.asp

.

[Rudurk]

I would start with the BETWEEN operator. You can type BETWEEN directly into the Criteria field of the Design Grid while in Design View of your Query.

Maybe start by hardcoding values and then look at SQL view. You can replace hardcoded values with variables, later.
BETWEEN 5 AND 6

http://www.w3schools.com/sql/sql_between.asp

.

I don't think that will work... if you read the things I have attempted you will see that I have indeed tried between clause

Age Min between 5 and 16

(But if you look in the table there is someone who started playing football age 3 to 28 so that should be included, but betwen 5 and 16 would filter that out.

[Rudurk]

I would start with the BETWEEN operator. You can type BETWEEN directly into the Criteria field of the Design Grid while in Design View of your Query.

Maybe start by hardcoding values and then look at SQL view. You can replace hardcoded values with variables, later.
BETWEEN 5 AND 6

http://www.w3schools.com/sql/sql_between.asp

.

Hmm I just replied to your post, but it seems to have disappeared.. I have tried between but that won't work.
please read the result i want and what I have already tried.

[Rudurk]

Nevermind figured it out... my query had to be

SELECT *
FROM Activities
WHERE ((Activity = "Football") AND (5 Between [Age Min] and [Age Max] OR 16 Between [Age Min] and [Age Max]))

[pbaldy]

Hmm I just replied to your post, but it seems to have disappeared.

Posts were moderated due to the link, which is an anti-spam setting. I've approved them.

[Rudurk]

Posts were moderated due to the link, which is an anti-spam setting. I've approved them.

Thanks.. How do you mark something as solved?

[ItsMe]

At the very top of the thread there is a menu towards the right. Click Thread Tools and choose the appropriate option.