I have similar to this table with many columns and thousand of rows (patients)
- as you can see each patient have 6 tests(test1 to test6) with different 6 result.
- all other columns have the same result.
- I need to switch the results from vertical to horizontal so I the table can show one record for each patient. like table2
I hope this can be done in Access 2016 and also if possible in excel also.
I think this can be done only by code, if any one posted the solution please explain the steps how to paste the code in access (I never made a code)
thank you and hope this can be resolved.
BEFORE:
TABLE1
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]FILE NO
[/TD]
[TD]RCVD DATE
[/TD]
[TD]COUNTRY
[/TD]
[TD]ID_NO
[/TD]
[TD]PID_NO
[/TD]
[TD]TEST
[/TD]
[TD]RESULT
[/TD]
[TD]REPORT DATE
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]05/01/2018
[/TD]
[TD]USA
[/TD]
[TD]1234567890
[/TD]
[TD]ABCDEFGH
[/TD]
[TD]TEST1
[/TD]
[TD]NOT TESTED
[/TD]
[TD]05/25/2018
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]05/01/2018
[/TD]
[TD]USA
[/TD]
[TD]1234567890
[/TD]
[TD]ABCDEFGH
[/TD]
[TD]TEST2
[/TD]
[TD]NOT TESED
[/TD]
[TD]05/25/2018
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]05/01/2018
[/TD]
[TD]USA
[/TD]
[TD]1234567890
[/TD]
[TD]ABCDEFGH
[/TD]
[TD]TEST3
[/TD]
[TD]POSITIVE
[/TD]
[TD]05/25/2018
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]05/01/2018
[/TD]
[TD]USA
[/TD]
[TD]1234567890
[/TD]
[TD]ABCDEFGH
[/TD]
[TD]FINAL RESULT
[/TD]
[TD]POSTTIVE
[/TD]
[TD]05/25/2018
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]05/01/2018
[/TD]
[TD]USA
[/TD]
[TD]1234567890
[/TD]
[TD]ABCDEFGH
[/TD]
[TD]TEST4
[/TD]
[TD]NOT TESTED
[/TD]
[TD]05/25/2018
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]05/01/2018
[/TD]
[TD]USA
[/TD]
[TD]1234567890
[/TD]
[TD]ABCDEFGH
[/TD]
[TD]TEST5
[/TD]
[TD]NOT TESTED
[/TD]
[TD]05/25/2018
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]05/01/2018
[/TD]
[TD]USA
[/TD]
[TD]1234567890
[/TD]
[TD]ABCDEFGH
[/TD]
[TD]TEST6
[/TD]
[TD]PENDING
[/TD]
[TD]05/25/2018
[/TD]
[/TR]
</tbody>[/TABLE]
AFTER:
TABLE2
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]FILE NO
[/TD]
[TD]RCVD DATE
[/TD]
[TD]COUNTRY
[/TD]
[TD]ID_NO
[/TD]
[TD]PID_NO
[/TD]
[TD]TEST1
[/TD]
[TD]TEST2
[/TD]
[TD]TEST3
[/TD]
[TD]TEST4
[/TD]
[TD]TEST5
[/TD]
[TD]TEST6
[/TD]
[TD]FINAL RESULT
[/TD]
[TD]REPORT DATE
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]05/01/2018
[/TD]
[TD]USA
[/TD]
[TD]1234567890
[/TD]
[TD]ABCDEFGH
[/TD]
[TD]NOT TESTED
[/TD]
[TD]NOT TESTED
[/TD]
[TD]POSITIVE
[/TD]
[TD]NOT TESTED
[/TD]
[TD]NOT TESTED
[/TD]
[TD]PENDING
[/TD]
[TD]POSITIVE
[/TD]
[TD]05/25/2018
[/TD]
[/TR]
</tbody>[/TABLE]
- as you can see each patient have 6 tests(test1 to test6) with different 6 result.
- all other columns have the same result.
- I need to switch the results from vertical to horizontal so I the table can show one record for each patient. like table2
I hope this can be done in Access 2016 and also if possible in excel also.
I think this can be done only by code, if any one posted the solution please explain the steps how to paste the code in access (I never made a code)
thank you and hope this can be resolved.
BEFORE:
TABLE1
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]FILE NO
[/TD]
[TD]RCVD DATE
[/TD]
[TD]COUNTRY
[/TD]
[TD]ID_NO
[/TD]
[TD]PID_NO
[/TD]
[TD]TEST
[/TD]
[TD]RESULT
[/TD]
[TD]REPORT DATE
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]05/01/2018
[/TD]
[TD]USA
[/TD]
[TD]1234567890
[/TD]
[TD]ABCDEFGH
[/TD]
[TD]TEST1
[/TD]
[TD]NOT TESTED
[/TD]
[TD]05/25/2018
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]05/01/2018
[/TD]
[TD]USA
[/TD]
[TD]1234567890
[/TD]
[TD]ABCDEFGH
[/TD]
[TD]TEST2
[/TD]
[TD]NOT TESED
[/TD]
[TD]05/25/2018
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]05/01/2018
[/TD]
[TD]USA
[/TD]
[TD]1234567890
[/TD]
[TD]ABCDEFGH
[/TD]
[TD]TEST3
[/TD]
[TD]POSITIVE
[/TD]
[TD]05/25/2018
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]05/01/2018
[/TD]
[TD]USA
[/TD]
[TD]1234567890
[/TD]
[TD]ABCDEFGH
[/TD]
[TD]FINAL RESULT
[/TD]
[TD]POSTTIVE
[/TD]
[TD]05/25/2018
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]05/01/2018
[/TD]
[TD]USA
[/TD]
[TD]1234567890
[/TD]
[TD]ABCDEFGH
[/TD]
[TD]TEST4
[/TD]
[TD]NOT TESTED
[/TD]
[TD]05/25/2018
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]05/01/2018
[/TD]
[TD]USA
[/TD]
[TD]1234567890
[/TD]
[TD]ABCDEFGH
[/TD]
[TD]TEST5
[/TD]
[TD]NOT TESTED
[/TD]
[TD]05/25/2018
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]05/01/2018
[/TD]
[TD]USA
[/TD]
[TD]1234567890
[/TD]
[TD]ABCDEFGH
[/TD]
[TD]TEST6
[/TD]
[TD]PENDING
[/TD]
[TD]05/25/2018
[/TD]
[/TR]
</tbody>[/TABLE]
AFTER:
TABLE2
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]FILE NO
[/TD]
[TD]RCVD DATE
[/TD]
[TD]COUNTRY
[/TD]
[TD]ID_NO
[/TD]
[TD]PID_NO
[/TD]
[TD]TEST1
[/TD]
[TD]TEST2
[/TD]
[TD]TEST3
[/TD]
[TD]TEST4
[/TD]
[TD]TEST5
[/TD]
[TD]TEST6
[/TD]
[TD]FINAL RESULT
[/TD]
[TD]REPORT DATE
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]05/01/2018
[/TD]
[TD]USA
[/TD]
[TD]1234567890
[/TD]
[TD]ABCDEFGH
[/TD]
[TD]NOT TESTED
[/TD]
[TD]NOT TESTED
[/TD]
[TD]POSITIVE
[/TD]
[TD]NOT TESTED
[/TD]
[TD]NOT TESTED
[/TD]
[TD]PENDING
[/TD]
[TD]POSITIVE
[/TD]
[TD]05/25/2018
[/TD]
[/TR]
</tbody>[/TABLE]