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

#### dankar

##### Board Regular
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
 FILE NO RCVD DATE COUNTRY ID_NO PID_NO TEST RESULT REPORT DATE 1 05/01/2018 USA 1234567890 ABCDEFGH TEST1 NOT TESTED 05/25/2018 1 05/01/2018 USA 1234567890 ABCDEFGH TEST2 NOT TESED 05/25/2018 1 05/01/2018 USA 1234567890 ABCDEFGH TEST3 POSITIVE 05/25/2018 1 05/01/2018 USA 1234567890 ABCDEFGH FINAL RESULT POSTTIVE 05/25/2018 1 05/01/2018 USA 1234567890 ABCDEFGH TEST4 NOT TESTED 05/25/2018 1 05/01/2018 USA 1234567890 ABCDEFGH TEST5 NOT TESTED 05/25/2018 1 05/01/2018 USA 1234567890 ABCDEFGH TEST6 PENDING 05/25/2018

<tbody>
</tbody>

AFTER:

TABLE2
 FILE NO RCVD DATE COUNTRY ID_NO PID_NO TEST1 TEST2 TEST3 TEST4 TEST5 TEST6 FINAL RESULT REPORT DATE 1 05/01/2018 USA 1234567890 ABCDEFGH NOT TESTED NOT TESTED POSITIVE NOT TESTED NOT TESTED PENDING POSITIVE 05/25/2018

<tbody>
</tbody>

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### Micron

##### Well-known Member
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.

#### dankar

##### Board Regular
It's a query from view tables

#### xenou

##### MrExcel MVP, Moderator
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: