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

  • Re: Select query numbering results
    ... The problem with using a crosstab query for a report is that you must ensure it always returns the same number of columns. ... Your main report would be based on a query that only returned one row for each RO Number, RO Date, VIN Number. ... If I had the sequence number I could write the data to a table and then construct a cross tab query where the across was on the sequence number and the part numbers would simply be data. ...
    (microsoft.public.access.gettingstarted)
  • Re: Select query numbering results
    ... My use of the term report ... If you really feel that you must use a ranking query to ... only returned one row for each RO Number, RO Date, VIN Number. ... Another option is to use a query that uses Duane Hookom's Concatenate ...
    (microsoft.public.access.gettingstarted)
  • Re: Append query adds way too many entries into table, instead of just
    ... Try the alternative syntax for an insert query. ... INSERT INTO T_PlateHistory (Plate, Vin, Assigned) ... I have an append query that pulls a VIN, a Plate number, and today's date and enters it into a history table. ...
    (microsoft.public.access.queries)
  • Re: SQL Pivot in Access
    ... VIN (unique) ... averages Sold Price and calls it avg_sold_amt ... Start by using the Crosstab Query Wizard in Access to build your initial ...
    (microsoft.public.access.queries)
  • Re: how do i split an xml file using c++ (and the sax parser)
    ... In your example it's just splitting an input stream into parts with ... empty line separator. ... > thanks, vin ...
    (comp.text.xml)