Combo Grouping

[Alexandre Saillant]

Hello,

I have 2 database, One with vehicule data and another with All the
option each vehicule has. I want to build a report that will show me
the most popular GROUP of option.

SO,

Table 1:
CarID Color Engine etc..
1111 Reg V6
2222 Green V6
3333 Blue V6
etc. ... ...

Table2:
CarID Option
1111 A
1111 B
1111 C
2222 A
2222 C
3333 B
3333 C

So I want to build a query that will group my data like this:

OptionCombo NBcars
AB 10
AC 5
ABC 8
BC 12

etc.

How the hell is this possible? note that option A and B must be the
same that B and A!!! So mergin all option has text one on back of the
other wont work.


help on this please!

 

[Michel Walsh]

Hi,


A possible solution, assuming you already know all the possible options,
and that there are less than 31 of them, and none is duplicated, in table2,
for a given carID:

SELECT SUM(Option='A' + 2*( Option='B' + 2* (Option = 'C' + 2*
(Option='D') )) As Options

FROM myTable1 INNER JOIN myTable2
ON table1.CarID = table2.CarID

GROUP BY table1.CarID



Note that Options return an integer accordingly to the presence or absence
of given options:


Options A B C D
0 0 0 0 0
-1 -1 0 0 0
-2 0 -1 0 0
-3 -1 -1 0 0
-4 0 0 -1 0
-5 -1 0 -1 0
-6 0 -1 -1 0
-7 -1 -1 -1 0
-8 0 0 0 -1
-9 -1 0 0 -1
....
-15 -1 -1 -1 -1

With the previous query as a saved query, qt1, you can then write:


SELECT Options, COUNT(*)
FROM qt1
GROUP BY Options
ORDER BY COUNT(*) DESC




Hoping it may help,
Vanderghast, Access MVP