Re: Query sorting

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 DEWEY wrote:
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";


"alex" wrote:

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.


"alex" wrote:

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?





.

Relevant Pages