Re: Help with this query

On Feb 15, 12:05 pm, rhaazy <rha...@xxxxxxxxx> wrote:
On Feb 15, 9:15 am, Salad <o...@xxxxxxxxxxx> wrote:





rhaazy wrote:
On Feb 13, 12:35 pm, rhaazy <rha...@xxxxxxxxx> wrote:

I have a table that looks like this:

Key               Name                 ID
1                    Joe                     A
2                    Sam                    A
3                    Bill                      A
4                    Bob                    B
5                    Pat                     B
.....

I need aquerythat selects the top 5 names where ID = A

However, thisqueryreturns 5 rows.  I need aquerythat instead
returns the result as 5 columns.  I think the name of what I want is a
crosstabquery, I just am unsure how to accomplish what I want... Any
helpwould be greatly appreciated.

Let my clarify a bit more.  What I want as a result is:

          Name1                    Name2
Name3                  Name4              Name5
IDA       Joe                         Sam
Bill                        etc..                    etc..

Let's see if this works.  I created a table, Table1, with the fields Key
(autonum, not used if sorting by name), ID (text..a-z), and FirstName (text)

I thought, why not sort the names as well?  So I created aquery.  I
also wanted to find out how many names I had.  So I created a column CH,
for ColumnHeader, that will be in the cross tab, Name1....Namex.  In the
query, CH gets the count of names that are less than the current name
for the same ID.  Thus if we had 2 names for ID A of George and Larry,
George would be "Name 1", Larry "Name 2".

SELECT Table1.ID, Table1.FirstName, "Name " & DCount("ID","Table1","ID =
'" & [ID] & "' And FirstName < '" & [FirstName] & "'")+1 AS CH
FROM Table1
ORDER BY Table1.ID, Table1.FirstName;

Basically, open up a newqueryin design mode, from the menu select
View/SQL and paste the above SQL statement into it.  Change all
references to Table1 to your table name and change any field names to
the field names in your table.

The hard part is over.  Now create anotherquery, Select View/SQL, and
drop the following into it.  Let's say you saved the above SQL as Query1.

TRANSFORM Min(Query1.FirstName) AS [The Value]
SELECT Query1.ID
FROM Query1
GROUP BY Query1.ID
PIVOT Query1.CH;

Now save thisqueryand run it.

If you don't want the names sorted, remove the sort on firstname and you
will need to to change CH so that it gets a number value by the
autonumber field instead.  Ex:
        "Name " & DCount("ID","Table1","ID = '" & [ID] & "' And  Key <
[Key])+1) AS CH

Hope this helps.

Kickass Bluegrasshttp://www.youtube.com/watch?v=-cdWYt1nhWE-Hide quoted text -

- Show quoted text -

This was perfect, thank you very much sir.- Hide quoted text -

- Show quoted text -

Ok what you gave is exactly what I wanted, but now let me throw a new
wrench into the machine.

There is another field in table1 called role.
I need to include in query1, a where clause that will select all the
names where role <> 'value'
I can add this statement, but when I do, the query returns a result
set like this:
bill Name1
sam Name2
joe Name3
bob Name5

Notice it skips 4. Because 4 is where the role equals the value i am
filtering out... How can I modify what you have given me to fix this
problem?
.

Relevant Pages