Re: Query sorting

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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