Including "In Between" Information In Query

[Reese]

[Solved] Including "In Between" Information In Query

I have a table of events that includes fields for ages, a Start Age and a End Age.

I want to make a query that pulls up how many programs have been provided to a given age group. How can I make it so that the query will understand to include records that have the given age group in between the Start Age and End Age fields? I.e. if I set the query to 3rd Grade, records that start at 2nd Grade and end with 4th Grade are included in the results as well.

I was thinking that associating a number code for each age group might make it easier, but I have no idea where to go from there. Does anyone have any suggestions?

 

[plog]

Can your provide sample data from your table(s) along with table and field names and then what data should be returned from this query you created based on that sample data? Use this format for posting data:

TableNameHere
Field1Name, Field2Name, Field3Name...
12, Steven, 12/23/2012
91, Larry, 4/16/2007
111, Jenny, 5/5/2009

 

[Reese]

Sure thing. Here's the Table & Field info:

[Event Information]
Start_Age, End_Age

I'm not sure if the format you provided for the query results fits what I'm looking for. It looked like you're trying to base age by the birth date of the child. The records represent programs that are provided to schools & community events so it's based on grades and the general audience age (i.e. College Students, Adults, Senior Citizens) that we are addressing.

Here's an example in a simplified list of what the query should do:

-Query is set to search for programs offered to 3rd Graders.
-Records that have Start_Age = 3rd Grade are included (I know how to do this)
-Records that have End_Age = 3rd Grade are included (I know how to do this)
-Records where 3rd Grade falls in between the Start_Age and End_Age are included (I don't know how to do this).

 

[Brianwarnock]

Do you mean

Where startage <= agegroup and endage >= agegroup

Brian

 

[plog]

Here's the Table & Field info...

That didn't help in the least. You only have 2 fields in your table? And no data?

Please post sample data from your table, and then what you expect your query to output based on that sample data.

 

[Reese]

Where startage <= agegroup and endage >= agegroup

Yes! This works great! I created a number code for each age group (so the user sees "Kindergarten" but the query sees "3") and this formula works perfectly. Thanks Brian!

Please post sample data from your table, and then what you expect your query to output based on that sample data.

Sorry about that. I didn't realize that you wanted the actual data in the table. And, yes, my table includes more then two fields but those were the only ones that related to my question. But since Brian's suggestion works, I'm all set. Thanks anyway, though.

 

[Brianwarnock]

Happy it's sorted

Brian

 

[Wiz47]

Re: [Solved] Including "In Between" Information In Query

I have a table of events that includes fields for ages, a Start Age and a End Age.

I want to make a query that pulls up how many programs have been provided to a given age group. How can I make it so that the query will understand to include records that have the given age group in between the Start Age and End Age fields? I.e. if I set the query to 3rd Grade, records that start at 2nd Grade and end with 4th Grade are included in the results as well.

I was thinking that associating a number code for each age group might make it easier, but I have no idea where to go from there. Does anyone have any suggestions?

Another way to skin the cat. I use this in a program where I want to find the inspections between two dates (extracted from a search form)

([Inspection_Date] Between Forms!frm_Search_Db!txtStart_Date And Forms!frm_Search_Db!txtEnd_Date)