ACCESS FEED: RE: [MS_AccessPros] From Multiple Columns (mult fields) to a single Column or field

Minggu, 24 Juni 2012

RE: [MS_AccessPros] From Multiple Columns (mult fields) to a single Column or field

Eric-

You're thinking like a spreadsheet. You need a separate table for the ICD9
codes. And you should probably have a separate table for patients. Like this:

tblPatients:

PatientID, PatName, PatAddress, etc.

tblPatientVisits:

VisitID, PatientID (points to tblPatients), VisDate, VisLOS, etc.

tblPatientVisitICD9:

VisitID (points to tblPatientVisits), PatientID (points to tblPatients),
ICD9Code, Comments, etc.

tlkpICD9:

ICD9Code, ICD9Description

Counting ICD9 codes in tblPatientVisitICD9 should be easy.

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
<http://www.viescas.com/> http://www.viescas.com/

(Paris, France)

From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Barry White
Sent: Sunday, June 24, 2012 1:32 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] From Multiple Columns (mult fields) to a single Column
or field

Hello all,

I have the following fields,

Name
Address
Patient Count
LOS (length of stay)
TGR (total gross revenue)
ICD9 code (disease, illness, injury code)
ICD9 description (text description of the code)

The ICD9 code and description can have 30 levels, so ICD9 code 1, ICD9 code 2,
ICD9 code 3, etc, meaning 30 columns or fields.

I have three issues or items I wish to accomplish, and they are,

1. All I want is one field for the code, one for the description, and maybe if I
was going to keep the levels intact, create a new "ICD9 level" field.
So instead of 60 columns, 30 for the code, 30 for the description, I would be
down to 3.

2. The reason I want just the 3 columns is that I want to do counts on the ICD9
codes, within each and across all patients, as well as, if I choose, each level.
There are literally, 11,000 distinct codes, so counting these across 30 columns
rather than 1 makes it nearly impossible.

3. How do I do this count mentioned in #2? Do I have to specify each of the
11,000 individual codes somewhere?

How do I go about achieving this?

Thank you,

Eric Lutz

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]