Gentlemen,
I have two badly designed tables - I didn't design these table, they where passed on to me. The tables are so cumbersome I have to create a new table, to be able to use their data.
Please see attachments to have a clear idea of the table design flaws:
The tables are: "TblProgrammersHrs"(In this table, ProjectID is the Primary Key - No duplicates) and "TblPersonnel",
and the "NewTable" (in this table, ProjID is the Primary key - no duplicates).
The TblProgrammersHrs table is designed in such a way that the same row contains the 'ProjLeader', his 'programmers' and their respective hours...All in the same row - Please see attachments.
My objective is to create a new table with input from the above two tables.
Note: In the new table, each programmer must occupy a separate row, unlike the 'TblProgrammersHrs' where all programmers are on the same row(record).
Also, in the new table, the 'positions' field should contain the either the index/positions of the programmers in 'TblProgrammersHrs' table.
Problem: What code or how can I populate the new table so that each programmer occupies a separate row?
Below is the SQL to extract the data from both tables. Then I will use the extracted data to populate the new table, but each programmer must be in individual row.
Select *
From TblProgrammersHrs, TblPersonnel
Where TblPersonnel.Name = TblProgrammersHrs.ProjLeader;
I hope my explanationn is not too complicated.
Any direction will be greatly appreciated.
Thanks.
tgif