Multiple column headings

[robert3oketcho]

Hi every one, am very new here and a typical novice at access. I have a data set with like this:

ID    LDate      Result  Test
m1    03-Aug-15    12    Potassium
m1    19-Aug-15    15    Potassium
m1    03-Aug-15    37    Urea
m1    31-Aug-15    25    Urea
m10   12-Aug-15    21    Potassium
m10   28-Aug-15    15    Potassium
m10   09-Sep-15    33    Potassium
m10   12-Aug-15    46    Urea
m10   09-Sep-15    25    Urea
m11   13-Aug-15    22    Potassium
m11   29-Aug-15    10    Potassium
m11   10-Sep-15    34    Potassium
m11   13-Aug-15    47    Urea
m11   10-Sep-15    39    Urea

I would like to get a crosstab with something like this

ID    Potassium1    Potassium2    Potassium3    Urea1    Urea2    Urea3
m1        12            15            37          25    
m10       21            15            33          46       25

Any help is highy appreciated, I have tried to search but cant get it through.

Robert

[jimatqsi]

When you create your cross-tab query, ID will be the row heading, Test will be the column heading and Result will be the Value.

You can do this quite easily with the query editor. The "trick", other than to know the above, is to right-click in the top half of the editor. When the context menu appears, choose Query Type and then in the submenu pick Crosstab Query. That will give you the appropriate prompts in the lower half of the query editor.

Jim

[NeoPa]

Other than checking for earlier records, a process that would require two extra queries be run for each record in the input table, there is nothing in your data that identifies which record refers to #1, #2 or #3 of each of Potassium or Urea.

That makes what you want to do very complicated. Unless you have more info about how the data is related of course.

PS. If you can get Jim's suggestion to work for you then ignore this. I do very little with cross-tabs myself.