reconstruct table that was normalized into several tables

[CuriousMark]

I imported a table into my database and normalized it into several tables.
Now I can't figure out how to reconstruct the original table with queries.
The data relate to procedures done on patients. At a given "encounter" a
patient can have one or more procedures done. So the original table looked
like this, with a Yes/null for each of the three procedures (if the procedure
was not done during the encounter, the cell was left blank):

Date LastName FirstName ProcA ProcB ProcC
1/1/08 Smith John Yes
1/2/08 Jones Steven Yes
1/3/08 Adams John Yes Yes

I normalized the data into four tables like this:

tblPatients
PatientID (pk)
LastName
FirstName

tblEncounters
EncounterID (pk)
PatientID (fk from tblPatients)
EncounterDate

tblEncounterProcedure
EncounterProcedureID (pk)
EncounterID (fk from tblEncounters)
ProcedureID (fk from tblProcedures)

tblProcedures
ProcedureID (pk)
ProcedureName (ProcedureA, ProcedureB or ProcedureC)

How do I write a query to "recreate" the original table, with columns for
each of the three procedures with a "yes" or null value in them? The queries
that I try either give me a cartesian product, or won't include an encounter
if any of the three procedures was not done at that encounter.

How do I get there from the normalized tables? And, should I have left well
enough alone?

 

[Marshall Barton]

I imported a table into my database and normalized it into several tables.
Now I can't figure out how to reconstruct the original table with queries.
The data relate to procedures done on patients. At a given "encounter" a
patient can have one or more procedures done. So the original table looked
like this, with a Yes/null for each of the three procedures (if the procedure
was not done during the encounter, the cell was left blank):

Date LastName FirstName ProcA ProcB ProcC
1/1/08 Smith John Yes
1/2/08 Jones Steven Yes
1/3/08 Adams John Yes Yes

I normalized the data into four tables like this:

tblPatients
PatientID (pk)
LastName
FirstName

tblEncounters
EncounterID (pk)
PatientID (fk from tblPatients)
EncounterDate

tblEncounterProcedure
EncounterProcedureID (pk)
EncounterID (fk from tblEncounters)
ProcedureID (fk from tblProcedures)

tblProcedures
ProcedureID (pk)
ProcedureName (ProcedureA, ProcedureB or ProcedureC)

How do I write a query to "recreate" the original table, with columns for
each of the three procedures with a "yes" or null value in them? The queries
that I try either give me a cartesian product, or won't include an encounter
if any of the three procedures was not done at that encounter.

How do I get there from the normalized tables? And, should I have left well
enough alone?

You did the right thing.

Create a query to join the tables and select the appropriate
fields from each table. Once you have that working, use it
as the basis of a crosstab query.

 

[CuriousMark]

Thanks. I didn't think of using a crosstab query.

 

[CuriousMark]

Thanks. I didn't think of using a crosstab query. I created the
tblEncounterProcedure intersection table because each encounter is associated
with 1 to 3 procedures. Am I missing something?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You should always Normalize your tables. Actually, you've over-done it,
you do not need the table tblEncounterProcedure. What you've done is
called attribute splitting (putting columns in more than one table, that
only need to be in one table). The table tblEncounters could hold the
data for tblEncounterProcedures, unless all Encounters do not include a
Procedure:

tblEncounters
EncounterID (pk)
PatientID (fk from tblPatients)
ProcedureID (fk from tblProcedures)
EncounterDate

Also, the correct PK for that table is the PatientID, ProcedureID and
EncounterDate, not the EncounterID (which I'll guess is an AutoNumber).

Use a Cross-Tab query to get the results you want:

PARAMETERS [Start Date] Date, [End Date] Date;
TRANSFORM FIRST(IIf(E.ProcedureID IS NOT NULL,"Yes","")) As TheValue
SELECT E.EncounterDate, PA.LastName, PA.FirstName
FROM (tblPatients As PA INNER JOIN tblEncounters As E ON PA.PatientID =
E.PatientID) INNER JOIN tblProcedures AS P ON E.ProcedureID =
P.ProcedureID
WHERE E.EncounterDate BETWEEN [Start Date] And [End Date]
GROUP BY E.EncounterDate, PA.LastName, PA.FirstName
PIVOT P.ProcedureName IN ("ProcedureA", "ProcedureB", "ProcedureC")

The IIf() function in the TRANSFORM clause will put a Yes or a blank
space under each procedure name.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSUG7xIechKqOuFEgEQIJHgCdGHW75S7oaZHZaU1/KIe1B4x8qnIAoIEy
gGkoTEraQRiENnyDeuHgKRb1
=+5Sp
-----END PGP SIGNATURE-----

I imported a table into my database and normalized it into several tables.
Now I can't figure out how to reconstruct the original table with queries.
The data relate to procedures done on patients. At a given "encounter" a
patient can have one or more procedures done. So the original table looked
like this, with a Yes/null for each of the three procedures (if the procedure
was not done during the encounter, the cell was left blank):

Date LastName FirstName ProcA ProcB ProcC
1/1/08 Smith John Yes
1/2/08 Jones Steven Yes
1/3/08 Adams John Yes Yes

I normalized the data into four tables like this:

tblPatients
PatientID (pk)
LastName
FirstName

tblEncounters
EncounterID (pk)
PatientID (fk from tblPatients)
EncounterDate

tblEncounterProcedure
EncounterProcedureID (pk)
EncounterID (fk from tblEncounters)
ProcedureID (fk from tblProcedures)

tblProcedures
ProcedureID (pk)
ProcedureName (ProcedureA, ProcedureB or ProcedureC)

How do I write a query to "recreate" the original table, with columns for
each of the three procedures with a "yes" or null value in them? The queries
that I try either give me a cartesian product, or won't include an encounter
if any of the three procedures was not done at that encounter.

How do I get there from the normalized tables? And, should I have left well
enough alone?