Results 1 to 13 of 13
  1. #1
    Uranium Lounger CWBillow's Avatar
    Join Date
    Jul 2002
    Location
    Las Vegas, NV USA
    Posts
    6,371
    Thanks
    78
    Thanked 12 Times in 11 Posts

    Setting Autonumber (Access 2000)

    I set up a query as shown in graphic...

    *All* I wanted to do is to set the Autonumber field of IDMaster so that the initial number set would be 10001. The help for queries said to set a (solitary) number field to one less (10000) than you wanted the numbers to start, and then append to the actual table through an append query (attached)... It runs, but it doesn't set the number to 10001 in the IDMaster table.

    What the heck is wrong here?

    Chuck Billow
    -------------------------------------------------
    "Good judgment comes from experience, and experience - well, that comes from poor judgment."

    ~ A(lan) A(lexander) Milne (1882-1956)- "House at Pooh Corner"

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Setting Autonumber (Access 2000)

    Is there only one record in this table?
    What is in the ID field in the TEMP_ID table?

    What is the highest ID in the master table before and after you run the query?

  3. #3
    Uranium Lounger CWBillow's Avatar
    Join Date
    Jul 2002
    Location
    Las Vegas, NV USA
    Posts
    6,371
    Thanks
    78
    Thanked 12 Times in 11 Posts

    Re: Setting Autonumber (Access 2000)

    patt:

    The source table (temp_id) and the dest. table (IDMaster) have a matching field, ID. Both are autonumber fields.

    There is no data in IDMaster, and only the one record with one field, 10000, in TempID. I want, according to "help" to append TempID to MasterID.

    This is all to get the records in IDMAster with a first record ID of 10001. "Append to IDMaster so that then the next record will be the desired 10001.

    ??

    Chuck
    -------------------------------------------------
    "Good judgment comes from experience, and experience - well, that comes from poor judgment."

    ~ A(lan) A(lexander) Milne (1882-1956)- "House at Pooh Corner"

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Setting Autonumber (Access 2000)

    What is the highest ID in the master table after you run the query?

    Also see <post#=399274>post 399274</post#>

  5. #5
    Uranium Lounger CWBillow's Avatar
    Join Date
    Jul 2002
    Location
    Las Vegas, NV USA
    Posts
    6,371
    Thanks
    78
    Thanked 12 Times in 11 Posts

    Re: Setting Autonumber (Access 2000)

    patt:

    I keep seeing "0 records added". So even after, the master has no records, and the one to append has the one, 10000.

    Chuck
    -------------------------------------------------
    "Good judgment comes from experience, and experience - well, that comes from poor judgment."

    ~ A(lan) A(lexander) Milne (1882-1956)- "House at Pooh Corner"

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Setting Autonumber (Access 2000)

    Check out the post I showed on the previous response.

    Would you zip and send the database containing only the tables and query concerned.

  7. #7
    Uranium Lounger CWBillow's Avatar
    Join Date
    Jul 2002
    Location
    Las Vegas, NV USA
    Posts
    6,371
    Thanks
    78
    Thanked 12 Times in 11 Posts

    Re: Setting Autonumber (Access 2000)

    Patt:

    If I'm reading that post right, it doesn't really apply, as it refers to compacting repairing etc...

    In the attached, there are just the two tables, and the one query.

    Chuck
    -------------------------------------------------
    "Good judgment comes from experience, and experience - well, that comes from poor judgment."

    ~ A(lan) A(lexander) Milne (1882-1956)- "House at Pooh Corner"

  8. #8
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Schenectady, New York, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Setting Autonumber (Access 2000)

    Chuck,

    Try this.

    Chuck,
    Clear your database and then compact it.

    Use the following query.

    INSERT INTO idMaster( id )
    SELECT 10000 AS Expr1;
    Don
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18> <img src=/S/flags/NewYork.gif border=0 alt=NewYork width=30 height=18> "Life on Earth is expensive, but at least it includes a free trip around the Sun."

  9. #9
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Setting Autonumber (Access 2000)

    Change your append query to:

    INSERT INTO IDMaster ( ID )
    SELECT TEMP_ID.ID
    FROM TEMP_ID

  10. #10
    Uranium Lounger CWBillow's Avatar
    Join Date
    Jul 2002
    Location
    Las Vegas, NV USA
    Posts
    6,371
    Thanks
    78
    Thanked 12 Times in 11 Posts

    Re: Setting Autonumber (Access 2000)

    Don / Pat:

    First Don: that worked great. I had "some" exposure to SQL some years back... Looks like I should definitely resurrect my studies.

    Pat: What's the diff between the two? Just semantics?

    Chuck
    -------------------------------------------------
    "Good judgment comes from experience, and experience - well, that comes from poor judgment."

    ~ A(lan) A(lexander) Milne (1882-1956)- "House at Pooh Corner"

  11. #11
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Setting Autonumber (Access 2000)

    Are you asking what is the difference between Don's and mine, or between your query and mine.

    If you are asking what is the difference between Don's and mine, semantics just about sums it up.

    The difference between your query and mine is that you should not join the temp table to the master in the query.

  12. #12
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Schenectady, New York, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Setting Autonumber (Access 2000)

    Chuck,

    Here is what I did.
    Create New Query
    Close the show table window.
    Type 10000 in the first field
    Chage the query to an Append
    Selected the table idMaste
    placed 'ID' into the Append to.
    Don
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18> <img src=/S/flags/NewYork.gif border=0 alt=NewYork width=30 height=18> "Life on Earth is expensive, but at least it includes a free trip around the Sun."

  13. #13
    Uranium Lounger CWBillow's Avatar
    Join Date
    Jul 2002
    Location
    Las Vegas, NV USA
    Posts
    6,371
    Thanks
    78
    Thanked 12 Times in 11 Posts

    Re: Setting Autonumber (Access 2000)

    Pat / Don:

    Thanks to both of you... I might just have gotten onto the right track...

    Regards,
    Chuck Billow

    We make a living by what we get, we make a life by what we give.

    - Winston Churchill
    -------------------------------------------------
    "Good judgment comes from experience, and experience - well, that comes from poor judgment."

    ~ A(lan) A(lexander) Milne (1882-1956)- "House at Pooh Corner"

Posting Permissions

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