calculated field - Access 2003

[Lesa Havert]

I want to create a calculated field (or fields) that group(s) ranges of
primary diagnoses (PDx) into 14 separate oncology (cancer) sites. I have
unsuccessfully tried using the "IIf" function to do this (e.g., "IIf [PDx
between #"xxxx" and #"xxxx", then "designated cancer site"], not ["designated
cancer site"]). If you have any suggestions on the syntax/formula I should
be using, it would be much appreciated.

 

[Bob Barrows [MVP]]

I want to create a calculated field (or fields) that group(s) ranges
of primary diagnoses (PDx) into 14 separate oncology (cancer) sites.
I have unsuccessfully tried using the "IIf" function to do this
(e.g., "IIf [PDx between #"xxxx" and #"xxxx", then "designated cancer
site"], not ["designated cancer site"]). If you have any suggestions
on the syntax/formula I should be using, it would be much appreciated.

Show us sample data and desired results.

 

[Lesa Havert]

Thanks for your email. (And I apologize for the cumbersome response below.)
Hope it clarifies/helps.

Cancer by Site

If PDx >= "1400" and <= "1499" or >= "1600" and <= "1619" or = "1950",
"Head/Neck cancer"

If PDx >= "1500" and <= "1599" or >= "1974" and <= "1978", "GI cancer"

If PDx >= "1620" and <= "1659" or = "1951" or >= "1970" and <= "1972",
"Thoracic cancer"

If PDx >= "1700" and <= "1709" or >= "1953" and <= "1955" or = "1985" or >=
"73310" and <= "73319", "Bone cancer"

If PDx >= "1710" and <= "1739" or >= "1760" and <= "1769" or = "1982", "Skin
cancer"

If PDx >= "1740" and <= "1759" or = "19881", "Breast cancer"

If PDx >= "179" and <= "1849" or = "1986", "GYN cancer"

If PDx >= "185" and <= "1879", "Genital Male cancer"

If PDx >= "1880" and <= "1899" or >= "1980" and <= "1981", "Urinary cancer"

If PDx >= "1900" and <= "1909" or = "1952" or >= "1958" and <= "1969" or =
"1973" or >= "1990" and <= "1991" or >= "19882" and <= "19889" or >= "#V100#"
and <= "#V1099#" or = "#V153#" or >= "#V160#" and <= "#V1699#" or = "#V4281#"
or = "#V4282#" or >= "#V5041#" and "#V5049#" or >= "#V542#" and <= "#V54299#"
or >= "#V580#" and <= "#V5819#" or = "#V593#" or = "#V661#" or = "#V662#" or
= "#V667#" or = "#V6701#" or = "#V671#" or = "#V672#" or = "#V711#" or >=
"#V760#" and <= "#V769#", "Other cancer"

If PDx >= "1910" and <= "1929" or >= "1983" and <= "1984", "CNS/Brain cancer"

If PDx >= "193" and <= "1949" or = "1987", "Endocrine cancer"

If PDx >= "2800" and <= "2899" or >= "28241" and <= "28989", "Hematology
cancer"

If PDx >= "20000" and <= "20899", "Leukemia/Lymphoma cancer"

***NOTE***
I would normally use "criteria" filters for this type of application, but
because there are 14 cancer sites, they would appear not to fit into the
maximum (9) separate criteria records in Access 2003, and I need to run it in
one query. (Our Health System I.S. dept. does not yet provide MS Office
2007.) Also, please note that although I have done a considerable amount of
work with MS Access & databases, I am more familiar with the formulas in MS
Excel. This is my first experience with calculated fields.

Please let me know if you have any ?s, and thanks again.

--
Lesa Havert
Market Research
UC Davis Health System

I want to create a calculated field (or fields) that group(s) ranges
of primary diagnoses (PDx) into 14 separate oncology (cancer) sites.
I have unsuccessfully tried using the "IIf" function to do this
(e.g., "IIf [PDx between #"xxxx" and #"xxxx", then "designated cancer
site"], not ["designated cancer site"]). If you have any suggestions
on the syntax/formula I should be using, it would be much appreciated.

Show us sample data and desired results.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

 

[Klatuu]

A better design would be to create a table that has all the codes and the
associated center type for that code in a table. Then join the table in your
query on the code and include the center description in the query output.

Philosophically, code belongs in code and data belongs in tables. Although
you may not think so, codes and requirements change over time. If a
description changes or a new code is added, etc, all you need to do is add or
modify a record in this table.
--
Dave Hargis, Microsoft Access MVP

Thanks for your email. (And I apologize for the cumbersome response below.)
Hope it clarifies/helps.

Cancer by Site

If PDx >= "1400" and <= "1499" or >= "1600" and <= "1619" or = "1950",
"Head/Neck cancer"

If PDx >= "1500" and <= "1599" or >= "1974" and <= "1978", "GI cancer"

If PDx >= "1620" and <= "1659" or = "1951" or >= "1970" and <= "1972",
"Thoracic cancer"

If PDx >= "1700" and <= "1709" or >= "1953" and <= "1955" or = "1985" or >=
"73310" and <= "73319", "Bone cancer"

If PDx >= "1710" and <= "1739" or >= "1760" and <= "1769" or = "1982", "Skin
cancer"

If PDx >= "1740" and <= "1759" or = "19881", "Breast cancer"

If PDx >= "179" and <= "1849" or = "1986", "GYN cancer"

If PDx >= "185" and <= "1879", "Genital Male cancer"

If PDx >= "1880" and <= "1899" or >= "1980" and <= "1981", "Urinary cancer"

If PDx >= "1900" and <= "1909" or = "1952" or >= "1958" and <= "1969" or =
"1973" or >= "1990" and <= "1991" or >= "19882" and <= "19889" or >= "#V100#"
and <= "#V1099#" or = "#V153#" or >= "#V160#" and <= "#V1699#" or = "#V4281#"
or = "#V4282#" or >= "#V5041#" and "#V5049#" or >= "#V542#" and <= "#V54299#"
or >= "#V580#" and <= "#V5819#" or = "#V593#" or = "#V661#" or = "#V662#" or
= "#V667#" or = "#V6701#" or = "#V671#" or = "#V672#" or = "#V711#" or >=
"#V760#" and <= "#V769#", "Other cancer"

If PDx >= "1910" and <= "1929" or >= "1983" and <= "1984", "CNS/Brain cancer"

If PDx >= "193" and <= "1949" or = "1987", "Endocrine cancer"

If PDx >= "2800" and <= "2899" or >= "28241" and <= "28989", "Hematology
cancer"

If PDx >= "20000" and <= "20899", "Leukemia/Lymphoma cancer"

***NOTE***
I would normally use "criteria" filters for this type of application, but
because there are 14 cancer sites, they would appear not to fit into the
maximum (9) separate criteria records in Access 2003, and I need to run it in
one query. (Our Health System I.S. dept. does not yet provide MS Office
2007.) Also, please note that although I have done a considerable amount of
work with MS Access & databases, I am more familiar with the formulas in MS
Excel. This is my first experience with calculated fields.

Please let me know if you have any ?s, and thanks again.

--
Lesa Havert
Market Research
UC Davis Health System

I want to create a calculated field (or fields) that group(s) ranges
of primary diagnoses (PDx) into 14 separate oncology (cancer) sites.
I have unsuccessfully tried using the "IIf" function to do this
(e.g., "IIf [PDx between #"xxxx" and #"xxxx", then "designated cancer
site"], not ["designated cancer site"]). If you have any suggestions
on the syntax/formula I should be using, it would be much appreciated.

Show us sample data and desired results.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

 

[Bob Barrows [MVP]]

Thanks for your email. (And I apologize for the cumbersome response
below.) Hope it clarifies/helps.

This doesn't really help. In fact, it raises more questions (see below).
When I asked for sample data, I meant for you to show me
1. the table name
2. the relevant column names and data types
3. a few rows of data in tabular format
4. The desired results given the input of those sample rows
5. the explanation of those desired results, which you seem to have
given below.

Cancer by Site



If PDx >= "179" and <= "1849" or = "1986", "GYN cancer"

If PDx >= "185" and <= "1879", "Genital Male cancer"

This is contradictory. A PDx of 186 would fall into both these
categories.
You need to straighten this out.

***NOTE***
I would normally use "criteria" filters for this type of application,
but because there are 14 cancer sites, they would appear not to fit
into the maximum (9) separate criteria records in Access 2003, and I
need to run it in one query. (Our Health System I.S. dept. does not
yet provide MS Office 2007.) Also, please note that although I have
done a considerable amount of work with MS Access & databases, I am
more familiar with the formulas in MS Excel. This is my first
experience with calculated fields.

Please let me know if you have any ?s, and thanks again.

This is crazy trying to do this with a formula. You need to create a
lookup table containing site names (call it SiteNames) and the PDx
ranges needed to use those sites. Something like

SiteName LowerPDx UpperPDx
Head/Neck 1400 1499
Head/Neck 1600 1619
Head/Neck 1950 1950
GI 1500 1599
GI 1974 1978
Thoracic 1620 1659
Thoracic 1951 1951
Thoracic 1970 1972
etc

This will allow you to do a query like

select PDx, SiteName
From yourtable t join SiteNames s
ON s.PDx Between s.LowerPDx and s.UpperPDx

You will need to create this query in sql view because design view will
not allow that join criterion

 

[Bob Barrows [MVP]]

If PDx >= "1900" and <= "1909" or = "1952" or >= "1958" and <= "1969"
or = "1973" or >= "1990" and <= "1991" or >= "19882" and <= "19889"
or >= "#V100#" and <= "#V1099#" or = "#V153#" or >= "#V160#" and <=
"#V1699#" or = "#V4281#" or = "#V4282#" or >= "#V5041#" and "#V5049#"
or >= "#V542#" and <= "#V54299#" or >= "#V580#" and <= "#V5819#" or =
"#V593#" or = "#V661#" or = "#V662#" or = "#V667#" or = "#V6701#" or
= "#V671#" or = "#V672#" or = "#V711#" or >= "#V760#" and <=
"#V769#", "Other cancer"

Oh wait. PDx is a Text field?!? Yikes. i thought you were using the
quotes around the numbers unnecessarily.

Yes, I'm with Dave, you need to create a table with all the possible PDx
values and the corresponding SiteNames. My initial suggestion of using
ranges will not work with Text data.

 

[Lesa Havert]

Thank you, Klatuu.

That is what I have been doing, with good consequences. A senior market
researcher in my department suggested I try using a calculated field with
ranges to avoid having to update a table each time a code changes. I will
scrap that idea and check the ICD-9 code book each year for changes to our
designated cancer codes.

Regarding calculated fields: I would like to create one for a numeric field
in my patient database, length of stay (los), as such: If [los] = 0 or 1
(days), then 1 (day), otherwise [los] (# of days). (If a patient is in the
hospital for < 24 hours, it is recorded as "0" days; but for market research
analysis purposes, it is one day.) Any suggestions regarding syntax/formula
would be appreciated. Thanks.

--
Lesa Havert
Market Research
UC Davis Health System

A better design would be to create a table that has all the codes and the
associated center type for that code in a table. Then join the table in your
query on the code and include the center description in the query output.

Philosophically, code belongs in code and data belongs in tables. Although
you may not think so, codes and requirements change over time. If a
description changes or a new code is added, etc, all you need to do is add or
modify a record in this table.
--
Dave Hargis, Microsoft Access MVP

Thanks for your email. (And I apologize for the cumbersome response below.)
Hope it clarifies/helps.

Cancer by Site

If PDx >= "1400" and <= "1499" or >= "1600" and <= "1619" or = "1950",
"Head/Neck cancer"

If PDx >= "1500" and <= "1599" or >= "1974" and <= "1978", "GI cancer"

If PDx >= "1620" and <= "1659" or = "1951" or >= "1970" and <= "1972",
"Thoracic cancer"

If PDx >= "1700" and <= "1709" or >= "1953" and <= "1955" or = "1985" or >=
"73310" and <= "73319", "Bone cancer"

If PDx >= "1710" and <= "1739" or >= "1760" and <= "1769" or = "1982", "Skin
cancer"

If PDx >= "1740" and <= "1759" or = "19881", "Breast cancer"

If PDx >= "179" and <= "1849" or = "1986", "GYN cancer"

If PDx >= "185" and <= "1879", "Genital Male cancer"

If PDx >= "1880" and <= "1899" or >= "1980" and <= "1981", "Urinary cancer"

If PDx >= "1900" and <= "1909" or = "1952" or >= "1958" and <= "1969" or =
"1973" or >= "1990" and <= "1991" or >= "19882" and <= "19889" or >= "#V100#"
and <= "#V1099#" or = "#V153#" or >= "#V160#" and <= "#V1699#" or = "#V4281#"
or = "#V4282#" or >= "#V5041#" and "#V5049#" or >= "#V542#" and <= "#V54299#"
or >= "#V580#" and <= "#V5819#" or = "#V593#" or = "#V661#" or = "#V662#" or
= "#V667#" or = "#V6701#" or = "#V671#" or = "#V672#" or = "#V711#" or >=
"#V760#" and <= "#V769#", "Other cancer"

If PDx >= "1910" and <= "1929" or >= "1983" and <= "1984", "CNS/Brain cancer"

If PDx >= "193" and <= "1949" or = "1987", "Endocrine cancer"

If PDx >= "2800" and <= "2899" or >= "28241" and <= "28989", "Hematology
cancer"

If PDx >= "20000" and <= "20899", "Leukemia/Lymphoma cancer"

***NOTE***
I would normally use "criteria" filters for this type of application, but
because there are 14 cancer sites, they would appear not to fit into the
maximum (9) separate criteria records in Access 2003, and I need to run it in
one query. (Our Health System I.S. dept. does not yet provide MS Office
2007.) Also, please note that although I have done a considerable amount of
work with MS Access & databases, I am more familiar with the formulas in MS
Excel. This is my first experience with calculated fields.

Please let me know if you have any ?s, and thanks again.

--
Lesa Havert
Market Research
UC Davis Health System

Lesa Havert wrote:
I want to create a calculated field (or fields) that group(s) ranges
of primary diagnoses (PDx) into 14 separate oncology (cancer) sites.
I have unsuccessfully tried using the "IIf" function to do this
(e.g., "IIf [PDx between #"xxxx" and #"xxxx", then "designated cancer
site"], not ["designated cancer site"]). If you have any suggestions
on the syntax/formula I should be using, it would be much appreciated.

Show us sample data and desired results.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

 

[Bob Barrows [MVP]]

Thank you, Klatuu.

That is what I have been doing, with good consequences. A senior
market researcher in my department suggested I try using a calculated
field with ranges to avoid having to update a table each time a code
changes. I will scrap that idea and check the ICD-9 code book each
year for changes to our designated cancer codes.

Regarding calculated fields: I would like to create one for a numeric
field
in my patient database, length of stay (los), as such: If [los] = 0
or 1 (days), then 1 (day), otherwise [los] (# of days). (If a
patient is in the hospital for < 24 hours, it is recorded as "0"
days; but for market research analysis purposes, it is one day.) Any
suggestions regarding syntax/formula would be appreciated. Thanks.

The formula would be:

iif([los]<2,1,[los])

 

[John W. Vinson]

Regarding calculated fields: I would like to create one for a numeric field
in my patient database, length of stay (los), as such: If [los] = 0 or 1
(days), then 1 (day), otherwise [los] (# of days). (If a patient is in the
hospital for < 24 hours, it is recorded as "0" days; but for market research
analysis purposes, it is one day.) Any suggestions regarding syntax/formula
would be appreciated. Thanks.

ReportLOS: IIF([LOS] <= 1, 1, [LOS])

should do it for you (assuming you don't have any negative LOS values).

 

[Klatuu]

As to using ranges with text, it could be done with a function called from
the query that uses a Select Case statment, but the table is a much better
solution.