change result of field based on another field

  • Thread starter Thread starter Lexy
  • Start date Start date
L

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?
 
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")
 
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
 
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.

KARL DEWEY said:
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")


Lexy said:
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?
Click to expand...
Click to expand...
 
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")

Lexy said:
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.

KARL DEWEY said:
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")


Lexy said:
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?
Click to expand...
Click to expand...
Click to expand...
 
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



KARL DEWEY said:
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")

Lexy said:
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.

KARL DEWEY said:
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?
Click to expand...
Click to expand...
Click to expand...
 
Open your query in design view, click on SQL icon, highlight all, copy, and
paste in a post.
I'll lok at it.

Lexy said:
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



KARL DEWEY said:
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")

Lexy said:
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?
Click to expand...
Click to expand...
Click to expand...
 
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

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

Lexy said:
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



KARL DEWEY said:
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?
Click to expand...
Click to expand...
Click to expand...
 
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];


Lexy said:
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

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

Lexy said:
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?
Click to expand...
Click to expand...
Click to expand...
 
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

KARL DEWEY said:
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];


Lexy said:
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

KARL DEWEY said:
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?
Click to expand...
Click to expand...
Click to expand...
 
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"));


Lexy said:
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

KARL DEWEY said:
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];


Lexy said:
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?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top