How to query value of several column fields from a lookup table

[Metcare]

Hi ,

I am creating a query that involves looking up the description of 5 fields
from a table. I have a table consisting of records with these fields:

Patient Name, Diagnosis1, Diagnosis2, Diagnosis3, Diagnosis4, Diagnosis5

However, the diagnosis fileds are codes. I have a table that I can lookup
the description of the codes, table contains diasgnosis codes and
description.
How do I create a query to display the description of all the 5 codes
instead of the codes for all the patients in the table.
Appreciate your help. Thanks in advance.

 

[Jerry Whittle]

You start by redesigning your table. Any time that you find yourself putting
the same kind of data across, like a spreadsheet, you are making a mistake in
database design. Let me ask you this: what happens to all your forms,
queries, and reports when someone says that you need to start tracking
Diagnosis6? They all need to be rebuilt.

What your table should look like is:

PatientName DiagnosisCode
Jim Jones A
Jim Jones B

Actually I take that back as a Patient could change there name and also have
more than one visit.

You should have a Patient table with fields all about the Patient such as
FirstName, LastName, Address, City, State, Zip. It should also have a
PatientID primary key field to uniquely identify that record.

Next you should have a PatientVisit table with fields like the VisitID,
PatientID, VisitDate, Doctor.

Then you have a Diagnosis table with fields like the following:

VisitID, DiagnosisCode, and anything else related to the Diagnosis.

Then you can join all the tables together, including the Codes table, in a
query. If you use a form for the Diagnosis, and you should use forms for
this, you could use a combo box to select codes from the Codes table.

 

[Steve]

The underlying problem you have is the design of your table. You have a
one-to-many relationship between Patient and Diagnosis. Your tables should
look like:
TblPatient
PatientID
FirstName
LastName
etc

TblDiagnosis
DiagnosisID
Diagnosis

TblPatientDiagnosis
PatientDiagnosisID
PatientID
DiagnosisID
DiagnosisDate

Your query needs to include all three tables with the appropriate fields you
want to return in the query. Your query then will return what you want.

Steve
(e-mail address removed)

 

[KARL DEWEY]

Your table structure is wrong. You attempting to use Access like a
spreadsheet.
It should be like this --
tblPatient --
PatientID - Autonumber - primary key
LName - text
FName - text
MI - text
etc. -

tblDiagnosis --
DiagnosisID - Autonumber - primary key
PatientID - Number - long integer - foreign key
DiagnosisDate - DateTime
Diagnosis - text or number depending on your code table.

Your query would look like this --

SELECT FName, MI, LName, Description, DiagnosisDate, PatientID
FROM (tblPatient INNER JOIN tblDiagnosis ON tblPatient.PatientID =
tblDiagnosis.PatientID) INNER JOIN tblCodes ON tblDiagnosis.Diagnosis =
tblCodes.Codes
WHERE DiagnosisDate Between CVDate([Enter start]) AND CVDate([Enter end])
ORDER BY DiagnosisDate, Description;

Now to get your present data into a useable form you need a union query.
qryDiagnosisUnion --
SELECT [Patient Name], Diagnosis1 AS Diagnosis
FROM YourTable
WHERE Diagnosis1 Is Not Null
UNION ALL SELECT [Patient Name], Diagnosis2 AS Diagnosis
FROM YourTable
WHERE Diagnosis2 Is Not Null
UNION ALL SELECT [Patient Name], Diagnosis3 AS Diagnosis
FROM YourTable
WHERE Diagnosis3 Is Not Null
UNION ALL SELECT [Patient Name], Diagnosis4 AS Diagnosis
FROM YourTable
WHERE Diagnosis4 Is Not Null
UNION ALL SELECT [Patient Name], Diagnosis5 AS Diagnosis
FROM YourTable
WHERE Diagnosis5 Is Not Null;

SELECT [Patient Name], Description
FROM qryDiagnosisUnion INNER JOIN tblCodes ON qryDiagnosisUnion.Diagnosis =
tblCodes.Codes
ORDER BY [Patient Name], Description;

 

[Metcare]

Thank you all for the immediate reply.
Actually , the original file is an excel file we get from our insurance
company , yes, it is a spreadsheet. I tried importing it into Access so we
can link it to our claims table in Access so as to generate reports.
Thanks again for the insights.

 

[Steve]

Maybe once you import the Excel data into Access, you could use code to
append new patients into TblPatient and their data into TblPatientDiagnosis.
For existing patients, you would only meed to import their data into
TblPatientDiagnosis. I have done this in a few applications and it automates
the reporting process. I provide help with Access, Excel and Word
applications for a modest fee. I could set this up for you in your existing
database. If you are interested, contact me.

Steve
(e-mail address removed)

 

[KARL DEWEY]

I provide help with Access, Excel and Word applications for a modest fee.
THIS FORUM IS FOR FREE --- FREE -- HELP TO THE USERS AND NOT A PLACE TO
HOCK YOUR DUBIOUS SKILLS.

Be that as it may, Metcare you can link the file instead of importing. Then
use the union query to normalize the data structure.

 

[John Spencer]

IF you have to live with that structure then one way to solve your problem is
to add the table with the dx and description to your query 5 times.

Now drag from Diagnosis1 to one copy of the table. Double-click on the join
line and select all records from the PatientName table and matching records
from the Dx table.

Repeat that for the other Diagnosis fields pointing to a different copy each
time. Now you can get the Dx description for each Diagnosis1 to 5 field by
using the appropriate table.

You are better off using a union query to normalize the data structure. See
other responses to your posting.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[John... Visio MVP]

I pretend to provide help with Access, Excel and Word applications for an
immodest fee. I could set you up for you in your existing database. If you
are interested, do not contact me.

Steve

Stevie is our own personal pet troll who is the only one who does not
understand the concept of FREE peer to peer support!
He offers questionable results at unreasonable prices.

These newsgroups are provided by Microsoft for FREE peer to peer support.
There are many highly qualified individuals who gladly help for free. Stevie
is not one of them, but he is the only one who just does not get the idea of
"FREE" support. He offers questionable results at unreasonable prices. If he
was any good, the "thousands" of people he claims to have helped would be
flooding him with work, but there appears to be a continuous drought and he
needs to constantly grovel for work.

John... Visio MVP