Normalize multiple fields into one 1

[jmhicsupt]

I have a database that has the following:

Manager Supervisor Secretary
Joe Doe Susan Doe John Doe

Where Manager, Supervisor and Secretary are three separate fields.

I would like to change to so that I have a "Title" and "EEName" field:

Manager Joe Doe
Supervisor Susan Doe
Secretary John Doe

I need this because I need to have a report that groups by EEName. I'm thinking normalization, but I don't know where to begin. Can anyone help?

Thanks in advance.

 

[fneily]

If the secretary reports to only one supervisor, you can have a table that looks like this:

EmployeeID FirstName LastName SupervisorID

So if you wanted to see everyone's supervisor, you would have a self-join query - you would use the table twice in the query manually joining SupervisorID from the first instance of the table to EmployeeID of the second instance. You would bring down the LastName from the first table and the Lastname of the second table. Result is list of everyone's supervisor.

You mentioned the word normalization. Very important to understand. That's why you would have two fields for name.

 

[PHV]

You may consider an UNION query:
SELECT 'Manager' AS Title, Manager AS EEName FROM yourTable WHERE Manager Is Not Null
UNION SELECT 'Supervisor', Supervisor FROM yourTable WHERE Supervisor Is Not Null
UNION SELECT 'Secretary', Secretary FROM yourTable WHERE Secretary Is Not Null

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ChanceD2]

jmhicsupt,

fneily makes a valid point. We need more information. Take your time at setting up the tables as you will save yourself a lot of heartache in the future.

Here is what I suggest. Tell us more.... tell us a lot more.
How far is this project along? (done, in concept??)
How many tables do you have so far?
What does each table have?
What other types of reports are you looking for?



C-D2

 

[TheAceMan1]

and perhaps have a look at the following links. If you come out understanding them . . . a new door into Access should open . . .

Fundamentals of Relational Database Design

Normalizing Tables

Table Relationships

See Ya! . . . . . .