How do I lookup values for mulitple columns in Access?

[Guest]

I am trying to create a query using Dlookup to find values for multiple
columns ie:
PROCD1
PROCD2
PROCD3
PROCD4
PROCD5
The values I am looking up would come from a table with the fields ICD9 and
NAME.
Values of ICD9 will equal those in the PROCD* fields,
I am new to Access, I can get one column to match thru RIGHT JOIN
However, I need all data items to either come up with a ICD9.Name or a Null
value in each PROCD* field. I can accomplish this in Excel using vlookup. I
would like to accomplish this in Access. Thank you in advance for your help

 

[John Vinson]

I am trying to create a query using Dlookup to find values for multiple
columns ie:
PROCD1
PROCD2
PROCD3
PROCD4
PROCD5

Your table is *incorrectly normalized*. If you have a one to many
relationship from NAME to PROCD, you should have many *records*, not
many *fields*.

The values I am looking up would come from a table with the fields ICD9 and
NAME.
Values of ICD9 will equal those in the PROCD* fields,
I am new to Access, I can get one column to match thru RIGHT JOIN
However, I need all data items to either come up with a ICD9.Name or a Null
value in each PROCD* field. I can accomplish this in Excel using vlookup. I
would like to accomplish this in Access. Thank you in advance for your help

What you'll need to do (if you insist on using this flawed design) is
create a Query joining this table to *five instances* of the name
table. Add the name table to the query grid five times; Access will
alias the additional instances by adding _1, _2, ... , _4 to the table
name. Join PROCD1 to ICD9 in the first instance, PROCD2 to ICD9 in the
second and so on.

Do consider normalizing your structure, though! "Fields are expensive,
records are cheap"!

John W. Vinson[MVP]

 

[Duane Hookom]

In the words of MVP Jeff Boyce, "you are committing spreadsheet". Have you
considered normalizing your table structure to place the procedures in a
related table?

If you don't understand normalization, consider checking out some resource
links at Jeff Conrad's web page
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101

 

[Guest]

Thank you for your response.
I was not exactly sure of what I can do and cannot do with Access. These
fields I specified are tied to a record with about 40 other fields. To give
you some insight, I am dealing with healthcare records and the PROCD*
reference procedure codes for each patient. There are up to 6 procedures for
each patient. Luckily, I dont have to deal with the diagnosis codes There are
up to 10 of these fields.
I thought my DB was normalized enough, given that each patient can have up
to 6 PROCD's.
I was trying to associate a name with only a certain portion these PROCD's.
Why? As a way to easily identify the certain PROCD's. Its easier for me
evaluate a name than a number.
The size of the DB's I am working with are very large, I will experiment
with your suggestion on a sample DB.
I have other issues with the data set before I can put together an analysis
package.
Again, thank you for your help and your time.

Leo

 

[Guest]

Thank you for your response.
I replied to the normalization in another post and this is as normalized as
this data gets.
As for committing spreadsheet. I plead guilty. I have not considered putting
the Procedures in a another related table. I am not sure of what your
suggestion means. Could you eloborate a little bit more?

Thank you

Leo

 

[Duane Hookom]

John Vinson is our resident "normalization" police. I'm sure he will follow
up on the procedure codes.

 

[John Vinson]

Thank you for your response.
I was not exactly sure of what I can do and cannot do with Access. These
fields I specified are tied to a record with about 40 other fields. To give
you some insight, I am dealing with healthcare records and the PROCD*
reference procedure codes for each patient. There are up to 6 procedures for
each patient. Luckily, I dont have to deal with the diagnosis codes There are
up to 10 of these fields.

Then you have a one to many relationship from Records to Procedures.
The current, administratively defined, six-procedure limit is a VERY
week reed upon which to lean for table design.

A *much* better design would be to have a PatientProcedures table with
a PatientID (or whatever the proper primary key of your current table
might be - visitID?) and a ProcID. This would let you add one, or two,
or six - or for some particularly unfortunate patient, *SEVEN* -
procedures, using a Subform. The ProcID in this table could be very
simply and easily linked to the Procedures table to display the name
of the procedure, and/or you could use a Combo Box on the subform to
store the ProcID while displaying the procedure name. The same logic
applies to diagnoses - a separate patient-diagnosis table with as many
rows as are needed for that particular patient.

I thought my DB was normalized enough, given that each patient can have up
to 6 PROCD's.

You have a one to many relationship. Model it as a one to many
relationship and you'll have a very much better time.

I was trying to associate a name with only a certain portion these PROCD's.
Why? As a way to easily identify the certain PROCD's. Its easier for me
evaluate a name than a number.

That's exactly WHY you should normalize - so that you can use the
name, not the number. I hope you're not using the table datasheet for
data entry and editing - it's NOT designed for that purpose!

The size of the DB's I am working with are very large, I will experiment
with your suggestion on a sample DB.

"Very large" is a slippery concept. 10MByte? 200MByte? 1.85GByte? How
large?

I have other issues with the data set before I can put together an analysis
package.
Again, thank you for your help and your time.

John W. Vinson[MVP]

 

[Guest]

Dear Mr. Vinson

Thank you very much for your prompt and thorough reponse. I appreciate the
time you took to answer me. Your response is helpful and I am applying your
responses to my sample data. The help you have given me got me out of the
box. I am now thinking outside the box and for what its worth, I was not
thinking of a "divide and conquer" concept. I wanted to keep the original
data set intact while adding columns I need to complete my tasks. I am in the
beginining stages of developing my review of these patient data bases and
your help pointed my in the right direction. You have questioned (asked)
about the size of the files I am working with and so far the raw data without
queries and supplemental tables are 50 to 70mb. depends on the hospital. At
the local community college, there is an Access projects course. I am looking
to get signed up for that class. I need to accelerate the learning curve.
Again, thank you for time and help.

Leo Jancila

 

[John Vinson]

Dear Mr. Vinson

Thank you very much for your prompt and thorough reponse. I appreciate the
time you took to answer me. Your response is helpful and I am applying your
responses to my sample data. The help you have given me got me out of the
box. I am now thinking outside the box and for what its worth, I was not
thinking of a "divide and conquer" concept. I wanted to keep the original
data set intact while adding columns I need to complete my tasks. I am in the
beginining stages of developing my review of these patient data bases and
your help pointed my in the right direction. You have questioned (asked)
about the size of the files I am working with and so far the raw data without
queries and supplemental tables are 50 to 70mb. depends on the hospital. At
the local community college, there is an Access projects course. I am looking
to get signed up for that class. I need to accelerate the learning curve.
Again, thank you for time and help.

There are some very helpful links for self-training at the Access
Junkie (MVP Jeff Conrad's) site:

http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html

Note that 70Mb is still quite a modest database. Access is limited to
2GB (2048Mb) for any single database so you're still around 3% of the
database's capacity. In practice I'd be careful over 1500Mb but you're
a LONG way from there...

Good luck with the database and with the training, and don't hesitate
to post back if you want to bounce design ideas off us (the
tablesdbdesign newsgroup is another good one though many of us visit
both).

John W. Vinson[MVP]