0

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

Haris Ghauri
  • 547
  • 2
  • 7
  • 27
  • For your example, to find a car that is both white and black use the `In` operator instead. Replace `[Car].[Color]="Black"` with `In("Black", "White")`. This will return all cars that are black and white. – random_answer_guy Aug 04 '16 at 13:47
  • That's what I do not want. There is no car like that. I want white cars to be shown in a completely different column. They MUST not be black. – Haris Ghauri Aug 04 '16 at 13:48

1 Answers1

1

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;
Don George
  • 1,328
  • 1
  • 11
  • 18
  • Wouldn't it be Count instead of sum? What is the last thing inside your bracket 0? Also could you write a sql statement will look like? – Haris Ghauri Aug 04 '16 at 13:36
  • It could be count instead of sum, if that's what your analysis calls for. In that case, I might use sum(iif(color="black",1,0)). The ",0" at the end is the value that gets used if the iif condition fails - I prefer to specify 0 instead of let it default to null, but it shouldn';t be necessary. If you set this up in query designer, you can see the SQL code. – Don George Aug 04 '16 at 14:08
  • I added sample code for the 1st two columns to the answer. – Don George Aug 04 '16 at 14:45
  • Thank you so much. It worked. I can add the subsequent columns myself. However, just explain what are the number arguments 1,0 inside brackets? And where should I read about these arguments if I want to enhance my sql knowledge – Haris Ghauri Aug 04 '16 at 15:20
  • 1
    Look up access iif statements. The access syntax is iff(criteria, true-result, false-result). If the criteria is true, the 1st result gets used; if the criteria is false, the second result gets used. The alternate syntax is iif(criteria, true-result) which returns the result if the criteria is true, or null if the criteria is false. So my formulas return a 1 for the things you want to count, a 0 for the things you don't want to count and then add up the totals. – Don George Aug 04 '16 at 15:45