Groups by Age and Female, male

[Totte via AccessMonster.com]

Hello
Can sombody please help me, I saw the code below in another discussion group.
I wan´t use it but with more categories. how can i make it work?
I would like to have it like this.
1 Female 8-11
2 Female 12-14
3 Female 15-17
3 Male 8-11
4 Male 12-14
5 Male 15-17
Can i use a similar code?

IIf([TargetGroupName]="Female", IIf([TargetGroupAge] < 25, "Female <25",
"Female >25"), IIf([TargetGroupAge] < 25, "Male <25", "Male >25"))

Thanks

 

[John W. Vinson]

Hello
Can sombody please help me, I saw the code below in another discussion group.
I wan´t use it but with more categories. how can i make it work?
I would like to have it like this.
1 Female 8-11
2 Female 12-14
3 Female 15-17
3 Male 8-11
4 Male 12-14
5 Male 15-17
Can i use a similar code?

IIf([TargetGroupName]="Female", IIf([TargetGroupAge] < 25, "Female <25",
"Female >25"), IIf([TargetGroupAge] < 25, "Male <25", "Male >25"))

Thanks

If you have more categories, then I would avoid the clumsy and complex nested
IIF method and instead create a little lookup table AgeGroups with four
fields: Gender, Low, High and Groupname:

Female 8 11 "Females 8-11"
Female 12 14 "Females 12-14"

etc. You can then create a Query joining this table to your age calculation by
including it in the query with a "Non Equi Join" such as

SELECT yourtable.*, AgeGroups.Groupname
FROM yourtable
INNER JOIN AgeGroups
ON yourtable.Gender = AgeGroups.Gender
AND yourtable.Age >= AgeGroups.Low
AND yourtable.Age <= AgeGroups.High;

 

[KARL DEWEY]

Try using a translation table and the query below.
Translation_Table ---
Sex LowAge HighAge Group
Female 8 11 1 Female 8-11
Female 12 14 2 Female 12-14
Female 15 17 3 Female 15-17
Male 8 11 3 Male 8-11
Male 12 14 4 Male 12-14
Male 15 17 5 Male 15-17

SELECT YourDataTable.*, Translation_Table.Group
FROM YourDataTable, Translation_Table
WHERE (((YourDataTable.Age) Between [LowAge] And [HighAge]) AND
((Translation_Table.Sex)=[YourDataTable].[Sex]));

 

[Totte via AccessMonster.com]

Thanks for your quick answer!
I´m sorry i did´t express my self clear,
I want it to be automatic, otherwise I have to change the group every year of
january the first,
for example
1 Girl younger 8-11
2 Girl older 12-14
3 Women younger 15-17
3 Boy younger 8-11
4 Boy older 12-14
5 Man younger 15-17
I have a table: NAME, AGE OF YEAR and GENDER

Try using a translation table and the query below.
Translation_Table ---
Sex LowAge HighAge Group
Female 8 11 1 Female 8-11
Female 12 14 2 Female 12-14
Female 15 17 3 Female 15-17
Male 8 11 3 Male 8-11
Male 12 14 4 Male 12-14
Male 15 17 5 Male 15-17

SELECT YourDataTable.*, Translation_Table.Group
FROM YourDataTable, Translation_Table
WHERE (((YourDataTable.Age) Between [LowAge] And [HighAge]) AND
((Translation_Table.Sex)=[YourDataTable].[Sex]));

Hello
Can sombody please help me, I saw the code below in another discussion group.

[quoted text clipped - 12 lines]

Thanks

 

[Totte via AccessMonster.com]

Thanks for your quick answer!
I´m sorry i did´t express my self clear,
I want it to be automatic, otherwise I have to change the group every year of
january the first,
for example
1 Girl younger 8-11
2 Girl older 12-14
3 Women younger 15-17
3 Boy younger 8-11
4 Boy older 12-14
5 Man younger 15-17
I have a table: NAME, AGE OF YEAR and GENDER

Hello
Can sombody please help me, I saw the code below in another discussion group.

[quoted text clipped - 12 lines]

Thanks

If you have more categories, then I would avoid the clumsy and complex nested
IIF method and instead create a little lookup table AgeGroups with four
fields: Gender, Low, High and Groupname:

Female 8 11 "Females 8-11"
Female 12 14 "Females 12-14"

etc. You can then create a Query joining this table to your age calculation by
including it in the query with a "Non Equi Join" such as

SELECT yourtable.*, AgeGroups.Groupname
FROM yourtable
INNER JOIN AgeGroups
ON yourtable.Gender = AgeGroups.Gender
AND yourtable.Age >= AgeGroups.Low
AND yourtable.Age <= AgeGroups.High;

 

[John W. Vinson]

Thanks for your quick answer!
I´m sorry i did´t express my self clear,
I want it to be automatic, otherwise I have to change the group every year of
january the first,
for example
1 Girl younger 8-11
2 Girl older 12-14
3 Women younger 15-17
3 Boy younger 8-11
4 Boy older 12-14
5 Man younger 15-17
I have a table: NAME, AGE OF YEAR and GENDER

What is in AGE OF YEAR?

If you're storing the child's age in a table field, you are aware, surely,
that every record will be WRONG 366 days after it's been entered? You would do
much better to store the child's birthdate, and calculate the age dynamically
in a Query using an expression:

Age: DateDiff("yyyy", [DOB], Date()) - IIF(Format([DOB], "mmdd") >
Format(Date(), "mmdd"), 1, 0)

If you use that as the calculated age, then no year to year adjustment is
needed.

Or do you mean that this year you want the first group to be

Girl younger 8-11

and next year you want the same girls to be

Girl younger 9-12

and so on???

 

[Totte via AccessMonster.com]

Thank you for your replay

AGE OF YEAR
is that year they turn, ex Girl younger 8-11 is girl younger from that
calendar year she becom 8 and until last dec that year she will be 11 year,
that year she turns 12 she will be Girl older 12-14. And so on...

Hope you understand what i mean my english is not so good

Thanks for your quick answer!
I´m sorry i did´t express my self clear,

[quoted text clipped - 8 lines]

5 Man younger 15-17
I have a table: NAME, AGE OF YEAR and GENDER

What is in AGE OF YEAR?

If you're storing the child's age in a table field, you are aware, surely,
that every record will be WRONG 366 days after it's been entered? You would do
much better to store the child's birthdate, and calculate the age dynamically
in a Query using an expression:

Age: DateDiff("yyyy", [DOB], Date()) - IIF(Format([DOB], "mmdd") >
Format(Date(), "mmdd"), 1, 0)

If you use that as the calculated age, then no year to year adjustment is
needed.

Or do you mean that this year you want the first group to be

Girl younger 8-11

and next year you want the same girls to be

Girl younger 9-12

and so on???

 

[KARL DEWEY]

I do not follow. I never heard of changing groupings every year.
--
KARL DEWEY
Build a little - Test a little

Thanks for your quick answer!
I´m sorry i did´t express my self clear,
I want it to be automatic, otherwise I have to change the group every year of
january the first,
for example
1 Girl younger 8-11
2 Girl older 12-14
3 Women younger 15-17
3 Boy younger 8-11
4 Boy older 12-14
5 Man younger 15-17
I have a table: NAME, AGE OF YEAR and GENDER

Try using a translation table and the query below.
Translation_Table ---
Sex LowAge HighAge Group
Female 8 11 1 Female 8-11
Female 12 14 2 Female 12-14
Female 15 17 3 Female 15-17
Male 8 11 3 Male 8-11
Male 12 14 4 Male 12-14
Male 15 17 5 Male 15-17

SELECT YourDataTable.*, Translation_Table.Group
FROM YourDataTable, Translation_Table
WHERE (((YourDataTable.Age) Between [LowAge] And [HighAge]) AND
((Translation_Table.Sex)=[YourDataTable].[Sex]));

Hello
Can sombody please help me, I saw the code below in another discussion group.

[quoted text clipped - 12 lines]

Thanks

 

[John W. Vinson]

Thank you for your replay

AGE OF YEAR
is that year they turn, ex Girl younger 8-11 is girl younger from that
calendar year she becom 8 and until last dec that year she will be 11 year,
that year she turns 12 she will be Girl older 12-14. And so on...

Hope you understand what i mean my english is not so good

I'm sorry, I really don't understand. Could you post a sample of the data with
three or four children; their birthdate; the value of AGE OF YEAR as of today;
the value of AGE OF YEAR as it will be on March 22, 2009; and how the AGE OF
YEAR is going to be changed.

 

[Totte via AccessMonster.com]

Okay I will try, in Swedish wrestling the group is change that year he/she is
8, 12, 15 or 17 years old. Here is an exampel from USA Wrestling <<
http://www.usawrestling.org/forms/Weights.pdf>> they have different agegroup
and groupname but the same system as in sweden.

ex, Mark is born 2000-03-20, he is 8 year, and this year 2008-01-01 he become
"Boy younger 8-11", and 2011-03-20 he will be 11 years old and he is still
"Boy younger 8-11".
In 2012-01-01 he will be (12 year during 2012) "Boy older 12-14".

NAME AGE OF YEAR GENDER CATEGORY
Mark 8 (this year) Male Boy younger 8-11
Robin 11 (this year) Male Boy younger 8-11
Viktor 12 (this year) Male Boy older 12-14
Anton 15 (this year) Male Man younger 15-17

I want the CATEGORY to change automatic, ex: between 8-11year he/she is Boy
younger/Girl younger and between 12-14year he/she is Boy older/Girl older.And
so on.....

Thanks Totte

Thank you for your replay

[quoted text clipped - 4 lines]

Hope you understand what i mean my english is not so good

I'm sorry, I really don't understand. Could you post a sample of the data with
three or four children; their birthdate; the value of AGE OF YEAR as of today;
the value of AGE OF YEAR as it will be on March 22, 2009; and how the AGE OF
YEAR is going to be changed.

 

[Totte via AccessMonster.com]

Okay I will try, in Swedish wrestling the group is change that year he/she is
8, 12, 15 or 17 years old. Here is an exampel from USA Wrestling <<
http://www.usawrestling.org/forms/Weights.pdf>> they have different agegroup
and groupname but the same system as in sweden.

ex, Mark is born 2000-03-20, he is 8 year, and this year 2008-01-01 he become
"Boy younger 8-11", and 2011-03-20 he will be 11 years old and he is still
"Boy younger 8-11".
In 2012-01-01 he will be (12 year during 2012) "Boy older 12-14".

NAME AGE OF YEAR GENDER CATEGORY
Mark 8 (this year) Male Boy younger 8-11
Robin 11 (this year) Male Boy younger 8-11
Viktor 12 (this year) Male Boy older 12-14
Anton 15 (this year) Male Man younger 15-17

I want the CATEGORY to change automatic, ex: between 8-11year he/she is Boy
younger/Girl younger and between 12-14year he/she is Boy older/Girl older.And
so on.....

Thanks Totte

I do not follow. I never heard of changing groupings every year.

Thanks for your quick answer!
I´m sorry i did´t express my self clear,

[quoted text clipped - 29 lines]

 

[John W. Vinson]

Okay I will try, in Swedish wrestling the group is change that year he/she is
8, 12, 15 or 17 years old. Here is an exampel from USA Wrestling <<
http://www.usawrestling.org/forms/Weights.pdf>> they have different agegroup
and groupname but the same system as in sweden.

ex, Mark is born 2000-03-20, he is 8 year, and this year 2008-01-01 he become
"Boy younger 8-11", and 2011-03-20 he will be 11 years old and he is still
"Boy younger 8-11".
In 2012-01-01 he will be (12 year during 2012) "Boy older 12-14".

NAME AGE OF YEAR GENDER CATEGORY
Mark 8 (this year) Male Boy younger 8-11
Robin 11 (this year) Male Boy younger 8-11
Viktor 12 (this year) Male Boy older 12-14
Anton 15 (this year) Male Man younger 15-17

I want the CATEGORY to change automatic, ex: between 8-11year he/she is Boy
younger/Girl younger and between 12-14year he/she is Boy older/Girl older.And
so on.....

Storing the fact that Mark is 8 this year is INCORRECT DESIGN, because it
requires that you edit the value in this field every year for every child!

If you instead store the fact that Mark was born in 2000, and Robin was born
in 1997, and Viktor was born in 1996 - by storing their birthdate - then you
can dynamically calculate the category for ANY person at ANY time, by using
DateDiff() to calculate their current age as of the time the query is
executed.

 

[Totte via AccessMonster.com]

Thanks for your replay
Okay but how should the code be, will you give me an example.

Okay I will try, in Swedish wrestling the group is change that year he/she is
8, 12, 15 or 17 years old. Here is an exampel from USA Wrestling <<

[quoted text clipped - 15 lines]

younger/Girl younger and between 12-14year he/she is Boy older/Girl older.And
so on.....

Storing the fact that Mark is 8 this year is INCORRECT DESIGN, because it
requires that you edit the value in this field every year for every child!

If you instead store the fact that Mark was born in 2000, and Robin was born
in 1997, and Viktor was born in 1996 - by storing their birthdate - then you
can dynamically calculate the category for ANY person at ANY time, by using
DateDiff() to calculate their current age as of the time the query is
executed.

 

[John W. Vinson]

Thanks for your replay
Okay but how should the code be, will you give me an example.

Okay I will try, in Swedish wrestling the group is change that year he/she is
8, 12, 15 or 17 years old. Here is an exampel from USA Wrestling <<

[quoted text clipped - 15 lines]

younger/Girl younger and between 12-14year he/she is Boy older/Girl older.And
so on.....

Storing the fact that Mark is 8 this year is INCORRECT DESIGN, because it
requires that you edit the value in this field every year for every child!

If you instead store the fact that Mark was born in 2000, and Robin was born
in 1997, and Viktor was born in 1996 - by storing their birthdate - then you
can dynamically calculate the category for ANY person at ANY time, by using
DateDiff() to calculate their current age as of the time the query is
executed.

Ok... if you have the date of birth stored in the field named DOB, and the age
group changes on January 1 of the year that the child turns 8, try a query
like

SELECT yourtable.*, AgeGroups.Groupname
FROM yourtable
INNER JOIN AgeGroups
ON yourtable.Gender = AgeGroups.Gender
WHERE Year(Date()) - Year([DOB]) >= AgeGroups.Low
AND Year(Date()) - Year([DOB]) <= AgeGroups.High;

 

[Totte via AccessMonster.com]

Thank you for your patience with me, my problem is solved.

new problem, if i use the code for wrestlers it works fine but my other
members (not wrestlers) fall out. Can you help me?

Thanks again Totte

Thanks for your replay
Okay but how should the code be, will you give me an example.

[quoted text clipped - 13 lines]
Ok... if you have the date of birth stored in the field named DOB, and the age
group changes on January 1 of the year that the child turns 8, try a query
like

SELECT yourtable.*, AgeGroups.Groupname
FROM yourtable
INNER JOIN AgeGroups
ON yourtable.Gender = AgeGroups.Gender
WHERE Year(Date()) - Year([DOB]) >= AgeGroups.Low
AND Year(Date()) - Year([DOB]) <= AgeGroups.High;

 

[John W. Vinson]

Thank you for your patience with me, my problem is solved.

new problem, if i use the code for wrestlers it works fine but my other
members (not wrestlers) fall out. Can you help me?

Please describe how your table distinguishes wrestlers from us wimps, what you
mean by "fall out", and the actual code you're using.