title: MS Access: filling in missing values and adding numbers
body: <p>I wasn't sure what a proper title would be, I hope this works.</p>
  
  <p>I'm having trouble converting rows to columns with a certain table, and found possible solutions online. However, I feel like I need to edit this table to enable me to actually convert the rows into columns.</p>
  
  <p>The table looks a little bit like this:</p>
  
  <pre><code>ID1 patientID patientname study_date echo_var
  
   1   1234      Bob         01-01-2014 15
   2   1234                  01-01-2000 13
   3   1234      Bob         08-08-2012 14
   4   1234      Bob         13-02-2005 13
   5   6625      Louie       12-11-2010 25
   6   6625                  08-10-1998 20
   7   6625      Louie       19-04-2006 21
  </code></pre>
  
  <p>This is information automatically harvested from an electronic patient system and normally contains a lot more variables per row, but I'm only interested in one (echo-var) right now. All the rows represent specific patient visits. Because of changes in the electronic system the tool I use can't properly extract all data from older records, which is why some of the names aren't filled in properly. Seeing as their patientID is unique for every patient I can fill the names in manually, but there's about 6000 records so I was hoping there would be a more elegant manner of doing so.</p>
  
  <p>This is not much of a bother though, the major thing is converting the rows into columns (see below). So I found a solution online for my problem, but I feel like I am hindered because the separate datapoints per patient aren't numbered uniquely for every patient, as below. I want every seperate datapoint to be numbered for each patient, so the first visit, chronologically, is numbered 1 for each patient individually, and the second is 2, etc.</p>
  
  <pre><code> ID1 patientID patientname study_date echo_var echo_number
  
   1   1234      Bob         01-01-2014 15        4
   2   1234      Bob         01-01-2000 13        1
   3   1234      Bob         08-08-2012 14        3
   4   1234      Bob         13-02-2005 13        2
   5   6625      Louie       12-11-2010 25        3
   6   6625      Louie       08-10-1998 20        1
   7   6625      Louie       19-04-2006 21        2
  </code></pre>
  
  <p>If I have this I think I know how to convert the rows to columns, which is what I ultimately want, like below.</p>
  
  <pre><code>patientID echo_number1 study_date1 echo_var1 echo_number2 study_date2 echo_var2
  1234      1            01-01-2000  13        2            13-02-2005  13
  6625      1            08-10-1998  20        2            19-04-2006  21
  </code></pre>
  
  <p>There would be rows for date no. 3 and its respective measurement and so forth.
  I guess the echo_number(x) variables aren't crucial, but the dates and respective measurement would be.</p>
  
  <p>Does anyone know the proper way to go for this? As I've mentioned, I've tried searching for these particular problems but I'm having trouble finding a solution. If anything's unclear please let me know. If anyone knows how how to go from the first table to the last (without the steps in between), that would work as well.</p>
  
  <p>Many thanks in advance</p>
  
  <hr>
  
  <p>Using a table similar to the second one above, I've tried the following queries:</p>
  
  <pre><code>SELECT 
      [patientID],
      "echo_number" &amp; [echo_number] &amp; "_" &amp; "echo_var" AS [ValueID],
      [echo_var] AS [ValueValue]
  FROM [TABLENAME]
  UNION ALL SELECT 
      [patientID],
      "echo_number" &amp; [echo_number] &amp; "_" &amp; "study_date" AS [ValueID],
      [study_date] AS [ValueValue]
  FROM [TABLENAME];
  </code></pre>
  
  <p>Followed by:</p>
  
  <pre><code>TRANSFORM Sum(NEWQUERY.ValueValue) AS SumOfValueValue
  SELECT NEWQUERY.patientID
  FROM NEWQUERY
  GROUP BY NEWQUERY.patientID
  PIVOT NEWQUERY.ValueID
  </code></pre>