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

dankar

Board Regular
Joined
Mar 23, 2016
Messages
71
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>
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
1,743
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.
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,527
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:

Forum statistics

Threads
1,078,515
Messages
5,340,863
Members
399,396
Latest member
PBE

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top