one table to go to multiple.....

[Eddyoftheyear]

Department_tbl
DepartmentID
Department
EntityID
DivisionID

Division_Tbl
DivisionID
Division

ProviderID
DepartmentID
Name

Account_tbl
providerID
Account number

I recieved a new table with mixed together infromation. Account numbers, department_name. however, the new table has the most accurate infromation. I want to normalize the table and break the infromation as mentioned above in the table structure. Alot of the information in the department table are the same in the new table but the new table has more infromation that I am going to have to append to the deparmtne table. I am not sure what is the best way to do this.

Should I append the departments from the new table to the existing one and since I have the department field is unique, it will bring in the unique ones.....

any suggestions

 

[Golom]

I infer that the "New Table" contains all the information (i.e. fields) that you are showing in your desired table structure.

I also infer that there is an existing Department_tbl. It is unclear if the Division_Tbl and Account_tbl currently exist or they are to be new creations.

With respect to your structure, I'm assuming that DepartmentID is unique and therefore each Department has exactly one DivisionID associated with it. If that is the case them the Division_Tbl seems over specified. It contains both DepartmentID and DivisionID. That would imply the possibility that the combination of the two fields in the Department_Tbl could be different than the combination of those same fields in the Division_Tbl ... not a good thing.

Assuming that you understand and have dealt with that issue, you could use SQL of the form

[red][b]To Add New Departments[/b][/red]
INSERT INTO Department_Tbl ([blue]... Department Table Fields ...[/blue])
SELECT [blue]... Department Table Fields ...[/blue]
From NewTable
Where DepartmentID NOT IN (Select DepartmentID
                           From DepartmentID)


[red][b]To update Existing Department Information[/b][/red]
UPDATE Department_Tbl As D INNER JOIN NewTable AS N
       ON D.DepartmentID = N.DepartmentID 
       SET
       D.Department = N.Department,
       D.EntityID   = N.EntityID,
       D.DivisionID = N.DivisionID,
       [Blue]... etc. ...[/Blue]

A similar approach would be used to modify the Division_Tbl and Account_Tbl.

 

[Eddyoftheyear]

Yes,

The table that has all the information has accounts, departments and divisions but they are flat record. I am trying to break this table to smaller so it can link to divisions and departments.

Each department may have more than one divison.