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:
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;