Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2012
    Posts
    1

    Unanswered: Doing a massive 27-in-1 merger

    My brain is toast.

    Looking for some quick ideas on how to do the following.

    I have a table that say has 1-20,000 rows.

    The table itself format can not be change read-only.

    With our frontend the end user inputs data and is assigned key ID numbers from Autonumber field when they delete a transaction it also deletes the autonumber but skips ahead to the next number.

    I need to pull the missing numbers
    Then for each missing numbers I need to insert a custom row?

    The goal is to pull the table with the deleted values and put back our custom rows in place so the numbers are correct when we export the table.

    example:
    Row1
    1 Apple truck fun
    2 Apple truck fun
    3 Apple truck fun

    When they delete a mistake the numbering looks like this

    1 Apple truck fun
    3 Apple truck fun

    The autonumber goes to next line but drops the number from the list.

    I need to pull the list then put custom data in.

    1 Apple truck fun
    2 Custom Data -Deleted row - Ref101
    3 Apple truck fun


    Got it?

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Numbers table.

    Create a table with an integer column. Populate the table with all whole numbers between one and at least the maximum id value you ever expect to see.

    Now left join your numbers table to your target. You'll get a "null" value wherever the target doesn't have a matching id. You can use nz() or whatever else you like to change the text returned.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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