tgifgemini 12 Junior Poster

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.