multiple fields with different criterias in query designer

[hrghauri]

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

CarMake                             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


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

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).




This query will be the source of my report. If possible, please tell me the equivalent sql statement that I can use instead. Thank You.

[Bulzie]

Try using a Crosstab query, might be what you need. You can turn that into a report.

Honda 4 2 2 3 1 2 4 3 1 < what do these number represent, is 4 the number of 2door Black Hondas?

[hrghauri]

I have updated my post. Now youll be able to understand what the numbers represent.

[Bulzie]

Maybe try to Group on Make, Color and and Door then do the count on Door? Also look at CrossTab Query, it takes the data and makes it look like a table like you have shown.