Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2011
    Posts
    2

    Unanswered: Output first row for several identical fields

    I have an access database table containing several thousand records which I want to view only the first occurrence of data based on several fields. In the data below, the name is unique and the combination of Level 1, Level 2, and Level 3 are a unique combination,

    name Level 1 Level 2 Level 3
    A1 1 3.1.12 1
    A3 1 3.1.12 2
    A2 1 3.1.3 1
    B4 1 3.1.5 1
    B1 1 3.1.5 2
    B3 1 3.1.5 3
    C5 1 3.1.5 4
    C3 1 3.1.7 1
    C2 1 3.1.7 2
    D4 2 3.1.5 1
    D2 2 3.1.5 2
    D1 2 3.1.6 1
    D5 2 3.2.5 1
    E3 2 3.2.5 2
    E1 2 3.2.5 3
    E2 2 3.2.6 1
    E5 2 3.2.6 2

    I need to output a list from this data that will give me only one record based on Level 1 and Level 2, for example the output based on the records above would be

    name Level 1 Level 2 Level 3
    A1 1 3.1.12 1
    A2 1 3.1.3 1
    B4 1 3.1.5 1
    C3 1 3.1.7 1
    D4 2 3.1.5 1
    D1 2 3.1.6 1
    D5 2 3.2.5 1
    E2 2 3.2.6 1

    The output could be a query, report or a new table as long as I get one record for each of Level 1 and Level 2 combined.

    Any help or ideas would be much appreciated.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Try:
    Code:
    SELECT First(SomeTable.name) AS FirstOfname, 
           SomeTable.[Level 1], 
           SomeTable.[Level 2], 
           First(SomeTable.[Level 3]) AS [FirstOfLevel 3]
    FROM SomeTable
    GROUP BY SomeTable.[Level 1], SomeTable.[Level 2];
    Have a nice day!

  3. #3
    Join Date
    Oct 2011
    Posts
    2
    Thank You Sinndho,
    That work and provided exactly what I was looking for.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •