switching specific coulmns from vertical to horizantal to get one record !
Results 1 to 4 of 4

Thread: switching specific coulmns from vertical to horizantal to get one record !
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Mar 2016
    Posts
    71
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

    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

  2. #2
    Board Regular
    Join Date
    Jun 2015
    Location
    Ontario, Canada
    Posts
    1,676
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: switching specific coulmns from vertical to horizantal to get one record !

    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.
    Tips for posting problems:
    1) "doesn't work" doesn't help. Post error message text/numbers and/or state what's happening.
    2) if posting code or sql, use code tags; specify on which code line errors occur, if applicable
    3) try to be specific; assume we know nothing about your issue - because we don't!

    Make all suggested changes in copies of your database or to its objects.

    "1 out of 1010 people understand binary. The other 1001 don't."

  3. #3
    Board Regular
    Join Date
    Mar 2016
    Posts
    71
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: switching specific coulmns from vertical to horizantal to get one record !

    It's a query from view tables

  4. #4
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,445
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: switching specific coulmns from vertical to horizantal to get one record !

    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 by xenou; Jul 9th, 2019 at 09:27 AM.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •