Help normalizing & combining fields?

[sarajini]

Hi All,

I am looking for advice on normalizing a table imported from Excel.

The Excel table lists information on cases that our clients have. Each case can have 1-4 situations. Each situation has three components: a problem, a method, and an outcome. Right now, the Excel table has an individual row for each case. The row also lists info for each problem that the case may have.
It looks like this:

Case#|ProbA|MethodA|OutcomeA|ProbB|MethodB|Outcome B
9000 11A D A 5C D A
9001 4A C B


However, this is quite unwieldy, and breaks normalization rules. I would like to create a junction table of cases and problems that would resemble the following:

Case # | Problem | Method | Outcome
9000 11A D A
9000 5C D A
9001 4A C B


What would be the best way to go about doing this? Would this be through a union query? Maybe the union query would have this idea (please excuse my lack of knowledge of SQL)….

SELECT cases.ProblemA, cases.MethodA, cases.OutcomeA 
UNION cases.ProblemB, cases.MethodB, cases.OutcomeB 
UNION cases.ProblemC, cases.MethodC, cases.OutcomeC
UNION cases.ProblemD, cases.MethodD, cases.OutcomeD;

Thank you so much!!!
sarajini

 

[LarryDeLaruelle]

Sarajini:

Sounds like you are on the right track:

tblCases: Case# as Primary Key and other data specific to the case.

tblSituations: Some ID as Primary Key, Case# as foreign key (link to tblCases), Problem field, Method field, Outcome field.

Use a Select query, linked on Case# to combine the data from tblCases with the data from tblSituations.

HTH

Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[sarajini]

Hi Larry,
Sounds good!
My big problem, however, is in separating the info into tblCases and tblSituations.
I don’t know how to start dividing the Situation info (ProblemA, MethodA, OutcomeA, ProblemB, MethodB, OutcomeB, Problem C, etc).
The information should end up in a nice, neat tblSituation, with five fields:
Primary Key | Problem | Method | Outcome | Case# |

When I tried to combine the fields for Situations, I first tried the union statement I copied in my first post. Not surprisingly, it didn’t work; I got this error message:

Invalid SQL statement: expected statement ‘Delete’, ‘Select,’ ‘insert’ or ‘update.’

How should I start making the tblSituation?
Thanks again!
sarajini

 

[LarryDeLaruelle]

Sarajini:

What format are the data in now?

Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[sarajini]

Hi Larry,

The original datasheet is in Excel. However, I've already exported a copy of it to Access (where it is in table format).

In the Excel sheet, the Header line contains all these fields:

Case#, ProbA, MethodA, OutcomeA, ProbB, MethodB, OutcomeB, ProbC, MethodC, OutcomeC, ProbD, MethodD, OutcomeD

Ick.

They are all in text format (even Case#).

hope that's useful!
thanks!!!
Sarah

 

[LarryDeLaruelle]

Sarajini:

Clearer now.

Sounds like the master table will contain only a field for case# (?).

I would use action queries to create the tables. Set up a Make Table query based on the data above and include only the field Case#; use table name tblCases (or whatever is appropriate).

Set up a Make Table query based on the original table and include only the fields Case#, ProbA, MethodA, OutcomeA; use table name tblSituations (or whatever is appropriate).

Set up an Append query based on the original table and include only the fields Case#, ProbB, MethodB, OutcomeB; append this to tblSituations. Repeat this for C and D.

I would also look at the data carefully to see if you can use any filters to eliminate situations (A, B, C, D) where there are no values. For example using Is Not Null in ProbA to eliminate any records that do not have entries (I'm assuming that if there is no entry in the problem column, there will, likewise, not be entries for Method and Outcome.

Is this getting you closer?

Larry De Laruelle
ldelaruelle@familychildrenscenter.org