Re: Help with this query

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 a query that selects the top 5 names where ID = A

However, this query returns 5 rows.  I need a query that instead
returns the result as 5 columns.  I think the name of what I want is a
crosstab query, I just am unsure how to accomplish what I want... Any
help would 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 a query.  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 new query in 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 another query, 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 this query and 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.
.

Relevant Pages

  • Re: Views
    ... > If I create a view consist of this query "Select SumFrom Table1". ... >Table1" SQL statement to the reporting tools? ... Executing SELECT ... ...
    (microsoft.public.sqlserver.mseq)
  • Re: Help with this query
    ... FROM Table1 ... View/SQL and paste the above SQL statement into it. ... I need to include in query1, a where clause that will select all the ... I can add this statement, but when I do, the query returns a result ...
    (comp.databases.ms-access)
  • Re: How to add to a new field based on information in existing field?
    ... a query to join the two tables. ... For example, Table1 includes fields TestID ... Table2 includes fields Description and DescriptionFr. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Non-updateable query issue
    ... You have an unnormalized table (of course, as it came from a flat spreadsheet instead of a relational source), and is it has 100 columns. ... joined to the master, showing all the records, the query is not updateable. ... > Say Fred deletes record 79 from Table1, and then Betty changes the> phone ... > Allen Browne - Microsoft MVP. ...
    (microsoft.public.access.queries)
  • Re: Non-updateable query issue
    ... Microsoft Access MVP ... When putting together that query, ... "Duane Hookom" wrote: ... Say Fred deletes record 79 from Table1, ...
    (microsoft.public.access.queries)