switching specific coulmns from vertical to horizantal to get one record !

dankar

Board Regular
Joined
Mar 23, 2016
Messages
113
Office Version
  1. 365
Platform
  1. Windows
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]
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
research crosstab query. If you need to base a report on a crosstab, you may need to worry about making the output columns fixed because the nature of a ct query is such that the number of field (columns) depends on the number of records that make up the column headers.
If the 1st image represents your actual table, your design is not sufficiently normalized (unnecessarily repeating data). If it represents a query, then maybe OK.
 
Upvote 0
PSEUDO SQL:

Code:
--create table [NewTable] with fields:
--	FileNo, [RCVD Date], Country, 
--	ID_No, PID_NO, 
--	Test1, Test2, Test3, Test4, Test5, Test6, [Final Result], 
--	[Report Date]


insert into [NewTable] 
select distinct 
	FileNo, [RCVD Date], Country, ID_No, PID_NO, [Report Date]
from 
	[OldTable]
	
Update n
set Test1 = Result
from 
	NewTable
	inner join OldTable
	on NewTable.FileNo = OldTable.FileNo
where
	OldTable.Test = 'Test1'

Update n
set Test2 = Result
from 
	NewTable
	inner join OldTable
	on NewTable.FileNo = OldTable.FileNo
where
	OldTable.Test = 'Test2'

Update n
set Test3 = Result
from 
	NewTable
	inner join OldTable
	on NewTable.FileNo = OldTable.FileNo
where
	OldTable.Test = 'Test3'
	
Update n
set Test4 = Result
from 
	NewTable
	inner join OldTable
	on NewTable.FileNo = OldTable.FileNo
where
	OldTable.Test = 'Test4'

Update n
set Test5 = Result
from 
	NewTable
	inner join OldTable
	on NewTable.FileNo = OldTable.FileNo
where
	OldTable.Test = 'Test5'

Update n
set Test6 = Result
from 
	NewTable
	inner join OldTable
	on NewTable.FileNo = OldTable.FileNo
where
	OldTable.Test = 'Test6'
	
Update n
set [Final Result] = Result
from 
	NewTable
	inner join OldTable
	on NewTable.FileNo = OldTable.FileNo
where
	OldTable.Test = 'Final Result'


The above assumes the insert query will produce distinct records, of which I would be skeptical without knowing more about your data ... so you may need to adapt the query plan or otherwise clean your data (it is, as Micron said, not looking normalized and therefore it is possible it will be more difficult to get correct results from it). In particular, it is entirely unclear what the primary keys are in that table, if any.


Note that if the purpose is just to view the data, a report, with a subreport, will probably do the job too.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,849
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top