Query - select common data from one column and display in several columns

Hello,


I have a (big) table which is not normalized, but for i need at the
moment i think
that's no problem. Indeed, what i would like to do is to select the
name field and
the note. The problem is that i want to display the note in 2
different columns.
the first columns will show the number (count) of time that a certain
note (e.g note=4)
appears for a certain name and in the other column the same thing but
for a different note value.

so each column noteX will display the number of time that the note
with the value X appears for each name


for example, to the following table:


Name | note | job | city | id |
----------------------------------------
john | 4 | jb1 | hamb | 1 |
john | 5 | jb2 | hamb | 2 |
john | 5 | jb3 | hamb | 3 |
john | 5 | jb4 | hamb | 4 |
Mark | 4 | jb1 | mun | 5 |
Mark | 4 | jb2 | mun | 6 |
Mark | 4 | jb5 | mun | 7 |
Mark | 5 | jb1 | mun | 8 |
peter | 5 | jb3 | berl | 9 |
peter | 5 | jb5 | berl | 10 |
frank | 4 | jb6 | v.form | 11 |
frank | 5 | jb3 | v.form | 12 |
frank | 5 | jb2 | v.form | 13 |

the result should be:

Name | note5 | note4 |
-------------------------
john | 3 | 1 |
Mark | 1 | 3 |
peter | 2 | 0 |
frank | 2 | 1 |


How should be the right SQL command to show the data i want?


Rui Dias
rldias@xxxxxxxxx



Thanks a lot
.

Relevant Pages

  • Re: Opening this will run the following SQL command when starting a new document in Word
    ... template, ... I believe the fix in that case was to display the Mail Merge toolbar, ... what is trying to open a SQL command when I start Word? ...
    (microsoft.public.word.application.errors)
  • Re: User search form by Firstname
    ... In your SQL command, you can check out the LIKE command, that should do the ... > I know how to extract the information using LDAP queries but what I ... > the users whose name start with Da and display it in dropdown list ..ie ... > David Hoy ...
    (microsoft.public.scripting.vbscript)
  • Re: Opening this will run the following SQL command when starting a new document in Word
    ... See "Opening This Will Run the Following SQL Command" Message When You Open ... I believe the fix in that case was to display the Mail Merge toolbar, ... Suzanne S. Barnhill ...
    (microsoft.public.word.application.errors)
  • Re: 2 groups on a single rpt?
    ... Brian Bischof ... > Is there a way to display 2 different groups of the same sql command ... > that one is not a subgroup of the other, ...
    (microsoft.public.vb.crystal)
  • SQL Statement
    ... I am trying to write an SQL command that will display a ... with table A having multiple copies of data held in column ... Nick ...
    (microsoft.public.access.queries)