Re-organisation / movement of data from one field to other in a Table.

[2122002]

Dear Expert,

I have a table which was delivered by a customer and is being messed up. What I want to do is to move/shift data/ROW/Column from one to another within the same Table.

Data in the table was initially converted from EXCEL to MS-Access and column heading is inside the recordsets.

Does anyone has VBA Scripts or solution to this or sometime similar solution???

Below is an example of how the table are wrongly organised:

Tablename: GeneralPractitioner

-------------------------------------------
Row1 |Johnson |20,MC Road | 40 |
------|---------------|-----------|--------|
Row2 |GPName | GPAddress | Age |
------|---------------|-----------|--------|
Row3 | |0795654536 |38 |
------|---------------|-----------|--------|
Row2 |GPContactPhone | GPAddress |Sex |
------|---------------|-----------|--------|
Row3 |John | |Male |
------|---------------|-----------|--------|
Row4 |Sex |Specialist |BloodGP |
------|---------------|-----------|--------|
Row5 |Dalton |James |O+ |
------|---------------|-----------|--------|
Row6 |Daylin |Tayo |O+ |
--------------------------------------------
Row7 |GPContactPhone | GPAddress |Sex |
------|---------------|-----------|--------|
Row8 |GPName | GPAddress | Age |
--------------------------------------------

Now, I want the table to be re-organise and should look like below assuming I have more than 15,000 records

----------|-----------|---|----------------|------|------
GPName | GPAddress |Age| GPContactPhone |BldGP |Sex
Dalton |20,MC Road |38 | 0795654536 |O+ | Male
John |20,MC Road |40 | 06978954536 |O+ | Male
----------------------------------------------------------

Please Help!.


Dan

 

[Remou]

Has this table any key field? If not, you will run into problems very quickly when the table goes out of sequence. (It would probably have been better to get the table as a text file). After that, it is simply a matter of stepping through each row in a table and checking whether it is a field row or a data row, if it is a field row, step next and perform an append or update to a new table according to the fields in the row.