I used three queries, one of which is a make table.
Alex_55 ---
SELECT T.vin, T.option, (SELECT COUNT(*)
FROM [Alex-2] T1
WHERE T1.Vin = T.Vin
AND T1.Option <= T.Option) AS Rank INTO [Alex-rank]
FROM [Alex-2] AS T
ORDER BY T.vin, T.option;
Alex-rank_Crosstab --
TRANSFORM Max([Alex-rank].option) AS MaxOfoption
SELECT [Alex-rank].vin
FROM [Alex-rank]
GROUP BY [Alex-rank].vin
PIVOT [Alex-rank].Rank IN (1,2,3,4,5,6,7,8,9,10);
Alex-finish --
SELECT [Alex-rank_Crosstab].vin, [1] & [2] & [3] & [4] AS Options
FROM [Alex-rank_Crosstab];
Below is the raw data and the results.
vin option
123 a
123 b
456 a
789 b
789 c
876 a
876 b
123 c
456 a
789 e
789 d
876 a
876 b
vin Options
123 abc
456 a
789 bcde
876 ab
alex said:
Interesting, I try it and modify the >1 to "" because I want to get
the vins with empty ones also. it works fine..... there is only on
problem.
I only and always get the first and last option. So:
a) when there in no option = it works, my field is empty
b) when there is 1 option, = the option repeat itselft twice since it's
the first and last
c) when ther is 2 options= it works fine
d) when there is more thant 2 options = it does not work since it only
list 2 options.
so we get:
vin option
a) 123
b) 234 a a
c) 345 a b
d) 456 a b
insted of:
vin option
a) 123
b) 234 a
c) 345 a b
d) 456 a b c d e
Hell of a problem no?
KARL said:
These two queries might do it for you.
The first query is named [QryAlex-1]
SELECT [alex-1].vin, Count([alex-1].option) AS CountOfoption
FROM [alex-1]
GROUP BY [alex-1].vin
HAVING (((Count([alex-1].option))>1));
TRANSFORM First([option]) & Last([option]) AS Opt
SELECT [alex-1].vin
FROM [alex-1] INNER JOIN [QryAlex-1] ON [alex-1].vin = [QryAlex-1].vin
GROUP BY [alex-1].vin
PIVOT "Options";
:
Thank you, but this is only part of the solution. This report will be
in a dap so I cannot keep in a table format since I would need to lock
the option name in colomn name. The option name changes all the times
and there is a few dozens of them.
you see now, with a table I get this:
Vin: options:
A B C D
123 1 1
456 1
789 1 1
876 1 1
I would need to get this:
Vin OptionGroup
123 AB
456 a
789 BC
876 AB
I dont have to worry about the order of the options anymore since the
table (part one of the solution) will do it. so i can merge them into
on field. This way, my column title in my dap wil always be fine.
From this point all I have to do is a sum query and connect my dap to
it.
So, I need to merge none blank field into a field (in a query). How
could we do this?
Help please.....
Jerry Whittle wrote:
This is about as close as you'll get in Access without jumping through some
major hoops. You'll need to add another field named ID to the query as a
crosstab requires a minimum of three fields to work. You could even get away
with a second OPTION.
TRANSFORM Count(qryVINS.ID) AS CountOfID
SELECT qryVINS.VIN, Count(qryVINS.ID) AS TotalVins
FROM qryVINS
GROUP BY qryVINS.VIN
PIVOT qryVINS.OPTION;
Change all the "qryVINS" with the name of the query and the proper field
names.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
:
I have a query with one to many relationship so I get this when merged
together:
vin option
123 a
123 b
456 a
789 b
789 c
876 a
876 b
I need to summarize this to a table so I can see the most popular group
of options:
a b 2 vins
a 1 vin
b c 1 vin
also note that the options cans be in any order and I could have 10-15
for a single vin sometimes. How do we tackle this problem?
