Results 1 to 4 of 4
  1. #1
    doci4a is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2011
    Posts
    20

    How to increment values through a query

    Hello guys,

    I would like to try out something but I don't know if it is possible.
    Here's my idea.

    I have an EMPLOYEES table with quite a few fields, such as NAME, AGE, ADDRESS, etc etc, and an EMPLOYEE_NUMBER, which is created automatically (it increments by one for the next employee in the DB) and it is used as a primary key.

    All the employees also have an EMPLOYEE_ID (also unique for every single one of them).



    The thing is that this DB is not up to date, so I have to import the new employees from another DB and eventually update the existing profiles.

    The problem is that in source database, I have the unique EMPLOYEE_ID but I don't have the EMPLOYEE_NUMBER.

    So the question is, how do I import the new employees, generating a new EMPLOYEE_NUMBER for every single one of them?

    Can this be done in the query?

    (As far as I can see, updating is pretty easy, I have an INNER_JOIN on both tables and I update the existing fields, but adding a new record is not that simple for me)

    Thank you for your time, be sure you help is much appreciated

  2. #2
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Is the EMPLOYEE_NUMBER field in your EMPLOYEES table an autonumber datatype?

    If so, Access will automatically add the autonumber when you append the new records. So you will have to develop a query that identifies the employees that need to be added. You can then change that query to an append query and run it. The new employees should be added to your table with the EMPLOYEE_NUMBER field autoincremented. I would recommend that you run a compact and repair on the database before running the append query. Additionally, it would be good to make a backup of the database in case the append does not go as planned.

  3. #3
    doci4a is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2011
    Posts
    20
    Hello.

    Thank You for your answer.

    Aaaand NO, it's not an AUTO_NUMBER. It's just a number...

    I can eventually try to change it to an AUTO_NUMBER and test this solution...cuz I don't think there would be another way around this...

  4. #4
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    My first reaction is that you will need some Visual Basic for Application (VBA) code to add the EMPLOYEE_NUMBER. I would probably append the records leaving the EMPLOYEE_NUMBER field as null for now. Then I would run code that will pull the records where the EMPLOYEE_NUMBER is null and update each sequentially with a number (that increments). I'm not sure how you would want these new EMPLOYEE_NUMBERs ordered.

    You many have to temporarily make the EMPLOYEE_NUMBER field not the primary key to allow the null values.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 11-12-2010, 12:10 PM
  2. Increment a value on button click
    By michaeljohnh in forum Programming
    Replies: 9
    Last Post: 08-25-2010, 10:01 AM
  3. Auto Increment by 1 Letter
    By Cinquefoil22 in forum Database Design
    Replies: 4
    Last Post: 07-02-2010, 10:35 AM
  4. How to Increment A Number Field
    By Advanced in forum Programming
    Replies: 3
    Last Post: 01-27-2010, 02:36 PM
  5. Replies: 2
    Last Post: 12-08-2009, 01:19 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums