[Year 12 IT Apps] Access Help Please!!!

[Timmer-Arends]

Hello Keith

I'd be tempted to follow Mark's second suggestion re another lookup table, 
but otherwise you can follow up his first by creating a VBA function in the 
'modules' page which is passed the age and returns, say, U13, U15 etc. You 
then incorporate the function in the query grid as though  its just another 
Access function. It should then produce a calculated field containing U13 
etc.

Regards
Robert T-A

----- Original Message ----- 
From: "Mark Kelly" <kel at mckinnonsc.vic.edu.au>
To: "Year 12 IT Applications Teachers' Mailing List" 
<itapps at edulists.com.au>
Sent: Tuesday, February 27, 2007 11:40 AM
Subject: Re: [Year 12 IT Apps] Access Help Please!!!


> In Filemaker at least, you could use a calculated field containing a CASE 
> structure to work out group based on age.
>
> e.g.
>
> CASE( Age<13; "Junior";
>       Age>12 and Age<15; "Intermediate";
>       Age>14 and Age<19; "Senior" )
>
> (I fixed the age overlap between inter and senior)
>
> With more than a few groups, create a second table with the age limits and 
> group names (a bit like an Excel lookup table) and create a relationship 
> between the kid's age in the first table and the lookup ages in the second 
> table to fetch the related group name.
>
> But that's in Filemaker.  I don't do Access.
>
> Keith Richardson wrote:
>> Hi all you Access Experts.
>> Three of us have a problem - how do we get Access to tell us which age
>> group an individual belongs to (Junior 10-12, Intermediate 13-14, Senior
>> 14-18) starting with date of birth (an entered field in the members
>> table). We worked out how to create a query that would get current age
>> based on date-now and dob, then selection based on a particular age
>> range for criteria, but that simply gives me a list of member names who
>> fall into
>> that age group at any time. What I want to do is flow that information
>> into something that will flow into a report, without having to manually
>> look through the lists of names of members in a particular age group.
>> Hope that isn't too complicated!
>> Regards, Keith (for Keith, Henry & Diane)
>> Keith Richardson
>>
>> _______________________________________________
>> http://www.edulists.com.au - FAQ, resources, subscribe, unsubscribe
>> IT Applications Mailing List kindly supported by
>> http://www.vcaa.vic.edu.au - Victorian Curriculum and Assessment 
>> Authority and
>> http://www.vitta.org.au  - VITTA Victorian Information Technology 
>> Teachers Association Inc
>>
>>
>
> -- 
> Mark Kelly
> Manager - Information Systems
> McKinnon Secondary College
> McKinnon Rd McKinnon 3204, Victoria, Australia
> Direct line / Voicemail: 8520 9085
> School Phone +613 8520 9000 << new number!
> School Fax   +613 9578 9253
>
> Webmaster - http://www.mckinnonsc.vic.edu.au
> IT Lecture notes: http://vceit.com
> Moderator: IPM Mailing List
>
> Doctor - We must all face reality sooner or later.
> Dowd   - I wrestled with reality for 35 years, doctor, and I'm happy to 
> say I won out over it.  ('Harvey', 1950)
>
>
> _______________________________________________
> http://www.edulists.com.au - FAQ, resources, subscribe, unsubscribe
> IT Applications Mailing List kindly supported by
> http://www.vcaa.vic.edu.au - Victorian Curriculum and Assessment Authority 
> and
> http://www.vitta.org.au  - VITTA Victorian Information Technology Teachers 
> Association Inc