change result of field based on another field

[Lexy]

Hi

My query calculates the age in years and also years and months (2 separate
fields) based on a Date of Birth that's input by the user into the base Table.

I'd like a new field in the query to then automatically reflect the life
stage (there are 7 different life stages) based on what the current age is.

Can you advise how I might do this please?

 

[KARL DEWEY]

Create a translation table with three fields --
AgeStage LowAge HighAge
A 1 4.99999
B 5 9.99999
C 10 19.99999
etc

Stage: IIF([Age] Between [LowAge] and [HighAge], [AgeStage], "Error")

 

[Jeff Boyce]

Lexy

One approach would be to create a (very) small "lookup" table that holds:

LifeStage BeginAge EndAge

and put your seven records in there.

Then you could join your calculated age to that lookup table to figure out
the LifeStage.

Note that this design also allows for the possibility of a change in the
LifeStage rating scale ...

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Lexy]

Thanks Karl

Being thick here. I understand the need for a look-up table for the
differing Life Stages but I'm not sure where to use it.

I'm assuming that the Iif statement is used in expression builder in the
field for Life Stage on my form, but can you explain what I'm putting into
the query please and how the field/query know to refer to the table?

Thanks.

Create a translation table with three fields --
AgeStage LowAge HighAge
A 1 4.99999
B 5 9.99999
C 10 19.99999
etc

Stage: IIF([Age] Between [LowAge] and [HighAge], [AgeStage], "Error")

Hi

My query calculates the age in years and also years and months (2 separate
fields) based on a Date of Birth that's input by the user into the base Table.

I'd like a new field in the query to then automatically reflect the life
stage (there are 7 different life stages) based on what the current age is.

Can you advise how I might do this please?

 

[KARL DEWEY]

Open your query in design view and add the look-up table above the grid.
Then in a blank position of the Field row insert the calculated field I
posted.

Stage: IIF([Age] Between [LowAge] and [HighAge], [AgeStage], "Error")

Thanks Karl

Being thick here. I understand the need for a look-up table for the
differing Life Stages but I'm not sure where to use it.

I'm assuming that the Iif statement is used in expression builder in the
field for Life Stage on my form, but can you explain what I'm putting into
the query please and how the field/query know to refer to the table?

Thanks.

Create a translation table with three fields --
AgeStage LowAge HighAge
A 1 4.99999
B 5 9.99999
C 10 19.99999
etc

Stage: IIF([Age] Between [LowAge] and [HighAge], [AgeStage], "Error")

Hi

My query calculates the age in years and also years and months (2 separate
fields) based on a Date of Birth that's input by the user into the base Table.

I'd like a new field in the query to then automatically reflect the life
stage (there are 7 different life stages) based on what the current age is.

Can you advise how I might do this please?

 

[Lexy]

I've done that Karl but am getting odd results.

I've got the Life Stage Iif statement and the Age in Months statement in two
columns in the query, along with data from the base Table, but it's returning
lots of results instead of a result for each record i.e. 2. Each record is
appearing lots of times with varying results and lots of errors.

I'm bound to be doing something simple wrong as I'm not as familiar with 07
as with 03....

Any suggestions you can think of? Shame I can't do you a print screen.

Lexy

Open your query in design view and add the look-up table above the grid.
Then in a blank position of the Field row insert the calculated field I
posted.

Stage: IIF([Age] Between [LowAge] and [HighAge], [AgeStage], "Error")

Thanks Karl

Being thick here. I understand the need for a look-up table for the
differing Life Stages but I'm not sure where to use it.

I'm assuming that the Iif statement is used in expression builder in the
field for Life Stage on my form, but can you explain what I'm putting into
the query please and how the field/query know to refer to the table?

Thanks.

Create a translation table with three fields --
AgeStage LowAge HighAge
A 1 4.99999
B 5 9.99999
C 10 19.99999
etc

Stage: IIF([Age] Between [LowAge] and [HighAge], [AgeStage], "Error")


:

Hi

My query calculates the age in years and also years and months (2 separate
fields) based on a Date of Birth that's input by the user into the base Table.

I'd like a new field in the query to then automatically reflect the life
stage (there are 7 different life stages) based on what the current age is.

Can you advise how I might do this please?

 

[KARL DEWEY]

Open your query in design view, click on SQL icon, highlight all, copy, and
paste in a post.
I'll lok at it.

I've done that Karl but am getting odd results.

I've got the Life Stage Iif statement and the Age in Months statement in two
columns in the query, along with data from the base Table, but it's returning
lots of results instead of a result for each record i.e. 2. Each record is
appearing lots of times with varying results and lots of errors.

I'm bound to be doing something simple wrong as I'm not as familiar with 07
as with 03....

Any suggestions you can think of? Shame I can't do you a print screen.

Lexy

Open your query in design view and add the look-up table above the grid.
Then in a blank position of the Field row insert the calculated field I
posted.

Stage: IIF([Age] Between [LowAge] and [HighAge], [AgeStage], "Error")

Thanks Karl

Being thick here. I understand the need for a look-up table for the
differing Life Stages but I'm not sure where to use it.

I'm assuming that the Iif statement is used in expression builder in the
field for Life Stage on my form, but can you explain what I'm putting into
the query please and how the field/query know to refer to the table?

Thanks.

:

Create a translation table with three fields --
AgeStage LowAge HighAge
A 1 4.99999
B 5 9.99999
C 10 19.99999
etc

Stage: IIF([Age] Between [LowAge] and [HighAge], [AgeStage], "Error")


:

Hi

My query calculates the age in years and also years and months (2 separate
fields) based on a Date of Birth that's input by the user into the base Table.

I'd like a new field in the query to then automatically reflect the life
stage (there are 7 different life stages) based on what the current age is.

Can you advise how I might do this please?

 

[Lexy]

Here 'tis Karl...

SELECT T_Main.ID, T_Main.[Tag ID], T_Main.[Date of Birth], T_Main.Sex,
DateDiff("m",[Date Of Birth],Date()) AS AgeinMonths, IIf([AgeinMonths]
Between [LowAge] And [HighAge],[LifeStage],"Error") AS Stage
FROM T_Main, [T_Life Stage];

lexy

Open your query in design view, click on SQL icon, highlight all, copy, and
paste in a post.
I'll lok at it.

I've done that Karl but am getting odd results.

I've got the Life Stage Iif statement and the Age in Months statement in two
columns in the query, along with data from the base Table, but it's returning
lots of results instead of a result for each record i.e. 2. Each record is
appearing lots of times with varying results and lots of errors.

I'm bound to be doing something simple wrong as I'm not as familiar with 07
as with 03....

Any suggestions you can think of? Shame I can't do you a print screen.

Lexy

Open your query in design view and add the look-up table above the grid.
Then in a blank position of the Field row insert the calculated field I
posted.

Stage: IIF([Age] Between [LowAge] and [HighAge], [AgeStage], "Error")

:

Thanks Karl

Being thick here. I understand the need for a look-up table for the
differing Life Stages but I'm not sure where to use it.

I'm assuming that the Iif statement is used in expression builder in the
field for Life Stage on my form, but can you explain what I'm putting into
the query please and how the field/query know to refer to the table?

Thanks.

:

Create a translation table with three fields --
AgeStage LowAge HighAge
A 1 4.99999
B 5 9.99999
C 10 19.99999
etc

Stage: IIF([Age] Between [LowAge] and [HighAge], [AgeStage], "Error")


:

Hi

My query calculates the age in years and also years and months (2 separate
fields) based on a Date of Birth that's input by the user into the base Table.

I'd like a new field in the query to then automatically reflect the life
stage (there are 7 different life stages) based on what the current age is.

Can you advise how I might do this please?

 

[KARL DEWEY]

Try this --
SELECT T_Main.ID, T_Main.[Tag ID], T_Main.[Date of Birth], T_Main.Sex,
DateDiff("m",[Date Of Birth],Date()) AS AgeinMonths, IIf(DateDiff("m",[Date
Of Birth],Date()) Between [LowAge] And [HighAge],[LifeStage],"Error") AS Stage
FROM T_Main, [T_Life Stage];

Here 'tis Karl...

SELECT T_Main.ID, T_Main.[Tag ID], T_Main.[Date of Birth], T_Main.Sex,
DateDiff("m",[Date Of Birth],Date()) AS AgeinMonths, IIf([AgeinMonths]
Between [LowAge] And [HighAge],[LifeStage],"Error") AS Stage
FROM T_Main, [T_Life Stage];

lexy

Open your query in design view, click on SQL icon, highlight all, copy, and
paste in a post.
I'll lok at it.

I've done that Karl but am getting odd results.

I've got the Life Stage Iif statement and the Age in Months statement in two
columns in the query, along with data from the base Table, but it's returning
lots of results instead of a result for each record i.e. 2. Each record is
appearing lots of times with varying results and lots of errors.

I'm bound to be doing something simple wrong as I'm not as familiar with 07
as with 03....

Any suggestions you can think of? Shame I can't do you a print screen.

Lexy



:

Open your query in design view and add the look-up table above the grid.
Then in a blank position of the Field row insert the calculated field I
posted.

Stage: IIF([Age] Between [LowAge] and [HighAge], [AgeStage], "Error")

:

Thanks Karl

Being thick here. I understand the need for a look-up table for the
differing Life Stages but I'm not sure where to use it.

I'm assuming that the Iif statement is used in expression builder in the
field for Life Stage on my form, but can you explain what I'm putting into
the query please and how the field/query know to refer to the table?

Thanks.

:

Create a translation table with three fields --
AgeStage LowAge HighAge
A 1 4.99999
B 5 9.99999
C 10 19.99999
etc

Stage: IIF([Age] Between [LowAge] and [HighAge], [AgeStage], "Error")


:

Hi

My query calculates the age in years and also years and months (2 separate
fields) based on a Date of Birth that's input by the user into the base Table.

I'd like a new field in the query to then automatically reflect the life
stage (there are 7 different life stages) based on what the current age is.

Can you advise how I might do this please?

 

[Lexy]

Hi Karl

I've pasted the SQL below in and removed the previous, but when running the
query I am still getting 7 rows for each of the two records.

lexy

Try this --
SELECT T_Main.ID, T_Main.[Tag ID], T_Main.[Date of Birth], T_Main.Sex,
DateDiff("m",[Date Of Birth],Date()) AS AgeinMonths, IIf(DateDiff("m",[Date
Of Birth],Date()) Between [LowAge] And [HighAge],[LifeStage],"Error") AS Stage
FROM T_Main, [T_Life Stage];

Here 'tis Karl...

SELECT T_Main.ID, T_Main.[Tag ID], T_Main.[Date of Birth], T_Main.Sex,
DateDiff("m",[Date Of Birth],Date()) AS AgeinMonths, IIf([AgeinMonths]
Between [LowAge] And [HighAge],[LifeStage],"Error") AS Stage
FROM T_Main, [T_Life Stage];

lexy

Open your query in design view, click on SQL icon, highlight all, copy, and
paste in a post.
I'll lok at it.

:

I've done that Karl but am getting odd results.

I've got the Life Stage Iif statement and the Age in Months statement in two
columns in the query, along with data from the base Table, but it's returning
lots of results instead of a result for each record i.e. 2. Each record is
appearing lots of times with varying results and lots of errors.

I'm bound to be doing something simple wrong as I'm not as familiar with 07
as with 03....

Any suggestions you can think of? Shame I can't do you a print screen.

Lexy



:

Open your query in design view and add the look-up table above the grid.
Then in a blank position of the Field row insert the calculated field I
posted.

Stage: IIF([Age] Between [LowAge] and [HighAge], [AgeStage], "Error")

:

Thanks Karl

Being thick here. I understand the need for a look-up table for the
differing Life Stages but I'm not sure where to use it.

I'm assuming that the Iif statement is used in expression builder in the
field for Life Stage on my form, but can you explain what I'm putting into
the query please and how the field/query know to refer to the table?

Thanks.

:

Create a translation table with three fields --
AgeStage LowAge HighAge
A 1 4.99999
B 5 9.99999
C 10 19.99999
etc

Stage: IIF([Age] Between [LowAge] and [HighAge], [AgeStage], "Error")


:

Hi

My query calculates the age in years and also years and months (2 separate
fields) based on a Date of Birth that's input by the user into the base Table.

I'd like a new field in the query to then automatically reflect the life
stage (there are 7 different life stages) based on what the current age is.

Can you advise how I might do this please?

 

[KARL DEWEY]

I got the same but the reason was that it included "Error" results also.
Use criteria <>"Error" on the Stage field.

SELECT T_Main.ID, T_Main.[Tag ID], T_Main.[Date of Birth], T_Main.Sex,
DateDiff("m",[Date Of Birth],Date()) AS AgeinMonths, IIf(DateDiff("m",[Date
Of Birth],Date()) Between [LowAge] And [HighAge],[LifeStage],"Error") AS Stage
FROM T_Main, [T_Life Stage]
WHERE (((IIf(DateDiff("m",[Date Of Birth],Date()) Between [LowAge] And
[HighAge],[LifeStage],"Error"))<>"Error"));

Hi Karl

I've pasted the SQL below in and removed the previous, but when running the
query I am still getting 7 rows for each of the two records.

lexy

Try this --
SELECT T_Main.ID, T_Main.[Tag ID], T_Main.[Date of Birth], T_Main.Sex,
DateDiff("m",[Date Of Birth],Date()) AS AgeinMonths, IIf(DateDiff("m",[Date
Of Birth],Date()) Between [LowAge] And [HighAge],[LifeStage],"Error") AS Stage
FROM T_Main, [T_Life Stage];

Here 'tis Karl...

SELECT T_Main.ID, T_Main.[Tag ID], T_Main.[Date of Birth], T_Main.Sex,
DateDiff("m",[Date Of Birth],Date()) AS AgeinMonths, IIf([AgeinMonths]
Between [LowAge] And [HighAge],[LifeStage],"Error") AS Stage
FROM T_Main, [T_Life Stage];

lexy

:

Open your query in design view, click on SQL icon, highlight all, copy, and
paste in a post.
I'll lok at it.

:

I've done that Karl but am getting odd results.

I've got the Life Stage Iif statement and the Age in Months statement in two
columns in the query, along with data from the base Table, but it's returning
lots of results instead of a result for each record i.e. 2. Each record is
appearing lots of times with varying results and lots of errors.

I'm bound to be doing something simple wrong as I'm not as familiar with 07
as with 03....

Any suggestions you can think of? Shame I can't do you a print screen.

Lexy



:

Open your query in design view and add the look-up table above the grid.
Then in a blank position of the Field row insert the calculated field I
posted.

Stage: IIF([Age] Between [LowAge] and [HighAge], [AgeStage], "Error")

:

Thanks Karl

Being thick here. I understand the need for a look-up table for the
differing Life Stages but I'm not sure where to use it.

I'm assuming that the Iif statement is used in expression builder in the
field for Life Stage on my form, but can you explain what I'm putting into
the query please and how the field/query know to refer to the table?

Thanks.

:

Create a translation table with three fields --
AgeStage LowAge HighAge
A 1 4.99999
B 5 9.99999
C 10 19.99999
etc

Stage: IIF([Age] Between [LowAge] and [HighAge], [AgeStage], "Error")


:

Hi

My query calculates the age in years and also years and months (2 separate
fields) based on a Date of Birth that's input by the user into the base Table.

I'd like a new field in the query to then automatically reflect the life
stage (there are 7 different life stages) based on what the current age is.

Can you advise how I might do this please?