sqlms-accessquery-designer

Sql query or Microsoft-access query design to present data in the following manner


I am trying to design a query in Microsoft-access which should present the data in the following manner:

Car Make             Black                             White                          Red
              Total  2-door  4-door           Total 2-door  4-door            Total 2-door  4-door  
    ---------------------------------------------------------------------------------------------------


Honda          4        2      2               3      1      2                  4      3      1
Toyota         3        1      2               5      3      2                  6      1      5     
Ford           2        0      2               0      0      0                  1      0      1

In Ms-Access query designer, I cant add more than one field which has a different criteria (for.eg white vs black). If I try to, it gives me nothing in the datasheet view (as if it tried to find a common car which is both white and black). Please tell me a sql query that I can use instead.

EDIT 1

Car Table:
 -CarMake "Short text"
 -Color "Short text"
 -Door "Short text" (2-door or 4-door)

EDIT 2

This is what I was talking about. How to add more fields in here with different criteria for.eg white: enter image description here


Solution

  • Two suggestions -

    First, you could concatenate color & style into one variable and use that in the crosstab query - but you won't get the subtotals for colors.

    Second, you could use iif statements in each column to define exactly what you want. Column 1 would be sum(iif(color="black",value,0)). Column 2 would be sum(iif(color="black" and model="2-door",value,0)). And so on. Not as simple as the 1st option, but you'll get exactly the columns you need.

        SELECT Car.CarMake, Sum(IIf([color]="black",1,0)) AS BlackTotal, Sum(IIf([color]="black" And [door]="2-door",1,0)) AS Black_2D
    FROM Car
    GROUP BY Car.CarMake;