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

    Unanswered: How reate a unique record by group

    can anybody help ??

    What I need to do is to create a unique record id for each group in the table.

    here is table example:

    Lname FName
    A john
    A bill
    A Kim
    B Mike
    B David

    I need to have table look like this:

    GroupID Lname Fname
    1 A John
    2 A Bill
    3 A Kim
    1 B Mike
    2 B David


    any help with this would be appreciated.

    Thanks,

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Dou you really want to add such a column to the table? In most cases a GROUP BY query should be enough.
    Have a nice day!

  3. #3
    Join Date
    Mar 2011
    Posts
    2
    reason I like to have group ID so that I can group them in column.

    example:

    Lname 1FName 2Fname 3Fname ect..

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. There is no possibility in Access to define a table with a computed column based on a user's defined function (that you would need to achieve your goal). So you would have to "manually" call this function every time a record is added, deleted or modified in the table. By manually I mean that "something" (an event procedure on forms, a startup function, etc.) would have to be called. This would eventually yield data corruption or incoherences : sooner or later there will be a case where the function should have been called and will not be (Murphy's Law, etc.).

    2. The purpose of the GROUP BY clause of the SQL language is precisely to group rows of data according to the contents of one or several columns and return the resulting data set.

    3. The function mentionned in 1 would probably use some SQL with a GROUP BY clause to update the table and insert the Group Record Ids, either directly using an UPDATE SQL statement or by using a loop in a RecordSet.
    Have a nice day!

Posting Permissions

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