Thanks for your response, Karl. The patient discharge database I manage
(which is purchased annually each calendar year) is huge, with ~125 fields
and almost 3 million records. Below is a summary:
Field Name Data Type Description
oshpd_id Text Hospital I.D. number (assigned by OSHPD)
typcare Text type of care (e.g., acute, rehabilitation, psych/chem dep,
skilled nursing)
agdyadm Number age in days
agyradm Number age in years
sex Text gender (male, female, unknown, blank)
ethncty Text ethnicity (Hispanic vs non-Hispanic)
race Text race (White, African American, Asian/Pacific Islander, American
Indian, etc.)
patzip Text patient zip code
patcnty Text patient county of residence
los Number length of stay (in days); if length of stay < 24 hours, recorded
as "0" length of stay
admtday Text day of admission
admtmth Text month of admission
admtyr Text year of admission
srcsite Text source of admission (e.g., home, residential care or skilled
nursing facility, ambulatory surgery center, etc.)
srclicns Text licensure of site (this hospital, another hospital, not a
hospital)
srcroute Text route of admission (emergency room vs other)
admtype Text type of admission (scheduled, unscheduled, infant, unknown)
disp Text disposition of patient (where patient released - e.g., home, care
facility, etc.)
pay_cat Text payor category code (Medicare, Medi-Cal, Private, Worker's
Comp, Other Government, County Indigent, Self-Pay, etc.)
pay_type Text payor type code (managed care, other)
pay_plan Text payor plan code (health plan licensed under Knox Keene - e.g.,
Kaiser)
charge Currency total charges for services rendered
ecode_p Text primary e-code (external cause of injury) (ICD-9 code manual)
ecodes1-4 Text other e-codes (ICD-9 code manual)
mdc Text major diagnostic category (25 categories containing all possible
principal diagnosis areas)
drg Text diagnosis-related groups (hospital patient groupings to categories
based on diagnostic, therapeutic & demographic characteristics for the
purpose of reimbursement)
diag_p Text principal diagnosis code (chief cause of patient's admission)
cpoa_p Text condition present on admission
proc_p Text principal procedure (procedure performed for definitive treatment)
proc_pdy Number number of patient days from admission to performance of
procedure
odiag1-24 Text other diagnosis codes (condition that exists at the time of
admission)
cpoa1-24 Text whether condition was present on admission (yes, no, uncertain)
oproc1-20 Text days from admission to other procedure
procdy1-20 Number other procedure codes reported according to the ICD-9 code
manual
hplzip Text hospital zip code
hplcnty Text hospital county code
mth_dsch Text month the patient was discharged from the hospital
The problem I am trying to solve using a calculated field is for the numeric
field "los" (length of patient stay in days). If a patient stays less than
24 hours, it is recorded as "0" in the primary (linked) table of the
database, but for our market research purposes, it should be "1." In my
current database (2006), there are a total of 20,637,531 patient days (los),
95,490 of which had a "0" los. I can capture all patient days on a given
query if I perform a "count" of los=0 and "sum" of los>=1.
I hope this makes sense. And thanks for any help you can provide.
--
Lesa Havert
Market Research
UC Davis Health System
KARL DEWEY said:
Post your table structure with field names and datatype along with sample data.
--
KARL DEWEY
Build a little - Test a little
:
definition in formula: LOS = length of stay (number of days - numeric field)
When using the below expression in a calculated field, I get an error
message stating that I tried to execute a query that does not include the
expression as part of an aggregate function.
LOStotal: IIf([OSHPD PDD NP 2006]![los]=0,Count([OSHPD PDD NP
2006]![los]),Sum([OSHPD PDD NP 2006]![los]))
I pulled the bracket detail from my base database table and the functions
from the "built-in functions" list. I used the "expression" category in the
"total" field. I successfully execute the query when configuring the formula
from the query (rather than table) fields, but want to use the table fields
if possible.
Any suggestions you can provide would be much appreciated. Thanks.