Haris Ghauri Haris Ghauri - 8 months ago 54
SQL Question

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.


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


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


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
GROUP BY Car.CarMake;