title: SQL In Access, Transforming a data table into a structured data table?
body: <p>I'm using Access forms and tables for the purposes of our project. We have an Access form template that sends data to a table (Defaults) when something is changed, added or deleted on the form. The form stores the value in the "DefaultValue" and updates the table with it. Basically, it sends data to the table, updates it on-the-spot (if needed) and retrieves it afterwards to the "DefaultValue" so we can work in our forms with it.</p>
  
  <p>That form data goes into a table [Defaults], which has 3 columns : <em>FormName, ControlName and DefaultVal</em>. Each value in a form gets categorized in the table by its [FormName] and [ControlName], both are primary key. <em>We use the same templated form with different form names multiple times. The form field names in the template are the same. [DefaultVal] has a Memo/LongText column format.</em></p>
  
  <blockquote>
    <p>Example: A row in "Defaults" reads <code>Form_Sample|Text224|Test</code>. This means there is a form named "Form_Sample" with a field named "Text224" with the DefaultValue "Test".</p>
  </blockquote>
  
  <p>So my question is, I want to pull the data from [Defaults].[DefaultVal] to the table "Analysis". The information needs to go into 7 columns (lets call them column1,2,3,4,etc due to classified company information). To define what data from column "DefaultVal" goes into one of the 7 columns in Analysis, the [ControlName] column needs to be used. Since form field names in our different forms are the same, further categorization is needed so that they don't overwrite. In order to define if the [DefaultVal] is unique, first [FormName] must be used then [ControlName] needs to be added. </p>
  
  <blockquote>
    <p>Example: If you have <code>Text224|Apple</code> from one form and <code>Text224|Orange</code> from another, they would overwrite due to the same [ControlName]. However, if we use two primary keys and have <code>Form1|Text224|Apple</code> and <code>Form2|Text224|Orange</code>, they would be unique. This is how our table defines if the value in [DefaultVal] should be overwritten or a new one should be created. The values in [DefaultVal] will change constantly and can be the same so I cannot pull the data based on that column.</p>
  </blockquote>
  
  <p>I need specific values in [DefaultVal] to be on the same row based on the [FormName] and in different columns based on the [ControlName]. <em>The table "Analysis" must be able to update information from "Defaults" and add new such.</em></p>
  
  <p>This is how I would picture the Query:</p>
  
  <blockquote>
    <p><strong>General Functions:</strong> The query can identify if there are new rows in [Defaults] and add them according to the below logic. The query can identify if some of the values in [Defaults] have changes and updates them accordingly.</p>
    
    <blockquote>
      <p><strong>Data:</strong> Text1, Text2, Text3, Text4, Text5, Text6 and Text7 exist in [Defaults] as [ControlName]. They all have some value in [DefaultVal]. They are all part of the form "Sample" [FormName]. So their [FormName]|[ControlName]|[DefaultVal] would look like this in the columns <code>Sample|Text1|Value</code>; <code>Sample|Text2|Value2</code> etc;;; There is also another set of Text1-7 [ControlName]s, but with a different [FormName]. The values in [DefaultValue] can be the same or different.</p>
      
      <blockquote>
        <p><strong>Rule:</strong> The query knows that the [DefaultVal] value of "Text1" as a [ControlName] needs to go into [Column1] from the table [Analysis]. Text2's value goes to [Column2], Text3 into [Column3] etc.</p>
        
        <blockquote>
          <p><strong>Logic:</strong> The query takes both instances of Text1's values and wants to put them in [Column1] of [Analysis]. It seems that they have a different [FormName] so it puts them on different rows. Then it takes both instances of Text2's values, it sees that they have a different [FormName] so it wants to put them on different rows. It sees that both [FormName]s exist in Analysis and puts them on the corresponding rows.</p>
        </blockquote>
      </blockquote>
    </blockquote>
  </blockquote>
  
  <p>I'm really sorry if this is too long, I'm guessing it is, but I don't know how to explain it otherwise. I have some basic understanding of SQL queries, but this goes way beyond my understanding and I don't even know what commands to use and where.</p>
  
  <p>Can someone please help? =/</p>
  
  <pre><code>INSERT INTO Analysis ([Control Name], [Control (Sub)-Section], [Control Objective], [Control Owner], [Branch Location], [Review Period], [Operating Effectiveness], [Conclusion and Recommendations])
  SELECT Defaults.DefaultVal, Analysis.*
  FROM Defaults as Def, Analysis as An
  UNION ALL
  On An.[Control Name] = Def.DefaultVal, An.[Control (Sub)-Section] = Def.DefaultVal, An.[Control Objective] = Def.DefaultVal, An.[Control Owner] = Def.DefaultVal, An.[Branch Location] = Def.DefaultVal, An.[Review Period] = Def.DefaultVal, An.[Operating Effectiveness] = Def.DefaultVal,  An.[Conclusion and Recommendations] = Def.DefaultVal
  PIVOT Def.FormName
  WHERE NOT EXISTS 
  (
  'SELECT * 
  FROM Defaults as Def
  WHERE Def.ControlName = "Text684", Def.ControlName = "Text688", Def.ControlName = "Text580", Def.ControlName = "Text473",  Def.ControlName = "Label548", Def.ControlName = "Label545", Def.ControlName = "Text925", Def.ControlName = "Text929"
  PIVOT FormName'
  )
  ELSE
  UPDATE Analysis
  SET [Control Name], [Control (Sub)-Section], [Control Objective], [Control Owner], [Branch Location[, [Review Period], [Operating Effectiveness], [Conclusion and Recommendations] = 
  (
  'SELECT DefaultVal
  FROM Defaults
  WHERE Def.ControlName = "Text684", Def.ControlName = "Text688", Def.ControlName = "Text580", Def.ControlName = "Text473",  Def.ControlName = "Label548", Def.ControlName = "Label545", Def.ControlName = "Text925", Def.ControlName = "Text929"
  PIVOT FormName'
  )
  ;
  </code></pre>