Results 1 to 9 of 9

How to add AutoNumber field into a query

This is a discussion on How to add AutoNumber field into a query within the Microsoft Access forums, part of the Question Forums category; I have read 15 articles but still can't figure. I have a simple query, non of the fields has unique ...

  1. #1
    New Member
    Join Date
    Feb 2008
    Posts
    20

    Default How to add AutoNumber field into a query

    I have read 15 articles but still can't figure. I have a simple query, non of the fields has unique values, and I am trying to add a field with AUtoNumber. Any help? THank you! Pavel

  2. #2
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    27,252

    Default Re: How to add AutoNumber field into a query

    There are instructions on how to do that here:
    http://www.groupacg.com/AqryTip.htm#AUTONUM
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    New Member
    Join Date
    Feb 2008
    Posts
    20

    Default Re: How to add AutoNumber field into a query

    This link can't be used because I don't have unique field in the query.

  4. #4
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    27,252

    Default Re: How to add AutoNumber field into a query

    Note a key phrase in that link:

    Critical to creating the auto number sub-query is that the table or query you are using as a source must contain a unique index and this field must be included in your auto number query.
    If you do not have one, you may be out of luck (I don't know if there is any other way to do it without this).
    Why can't you add one?

    Or maybe you can explain exactly what you are hoping to accomplish by doing this. What is your end goal?
    There may be a different way to get there.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #5
    New Member
    Join Date
    Feb 2008
    Posts
    20

    Default Re: How to add AutoNumber field into a query

    Thank you. I have this (very simplified):
    field1:fraction of % , field2: Total $ .
    I have two rows:
    1st row reads: 55% (field1), 100$ (field2)
    2nd row reads: 45% (field1), 100$ (field2)
    When I run field1 * field 2 I get an allocation of 55$ and 45$ in each row.
    I want to add an additional autonumber field so I would assign 1 to the first row and 2 to the second row in a separate column (this will give me a unique row identifier)

  6. #6
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    27,252

    Default Re: How to add AutoNumber field into a query

    Why not just an Autonumber field to your table?

    Then you will have a unique identifier which will then allow you to use that code to create query autonumber (I assume that you want it to begin with 1 each time).
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  7. #7
    New Member
    Join Date
    Feb 2008
    Posts
    20

    Default Re: How to add AutoNumber field into a query

    I unfortunately can not add any fields to that table. THat is why I need to use query to sort of re-create the table but with this additional unique autonumber field.

  8. #8
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    27,252

    Default Re: How to add AutoNumber field into a query

    I am afraid you may be caught in a classic "catch-22", you need the query to return a unique counter, but creating a unique counter appears to require a unique field in your query.

    There is a way around that. You can create a Make Table Query to create a temporary table which contains all of the fields in your original table along with an AutoNumber field. You can then run this query you are trying to create off of this new temporary table (and make use of your new autonumber).
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  9. #9
    New Member
    Join Date
    Nov 2009
    Posts
    1

    Default Re: How to add AutoNumber field into a query

    Quote Originally Posted by pafkam View Post
    I have read 15 articles but still can't figure. I have a simple query, non of the fields has unique values, and I am trying to add a field with AUtoNumber. Any help? THank you! Pavel
    Hi,

    Pls use create table statement as below :

    create table my_table (my_column counter not null)

    The above statement creates tables named my_table with 1 column my_column which is of type Autonumber (counter)

    Thanks
    Dhirendra

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com