Results 1 to 2 of 2
  1. #1
    kagoodwin13 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    181

    How to handle changed product number?

    I'm getting to the near end of my database project. It stores rates for logistics for the past 10 years. I've run into a major problem though - over the years, the same product has changed ID numbers. This causes a problem when I try to marry it up with a table storing shipment volume by product ID number - the tables will only link if the product number matches what is in the volume table, and searches for the new product number get dropped.

    Further complicating the matter, sometimes the newer product number was used previously on a different product. There was no "assigned" number for a product - whatever number was available was used.

    Is there a generally-accepted way to deal with this? I'd hate to lose the ability to marry product info to shipment volume.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    51,123
    You want to capture data from the last 10 years into this new db?

    Are these product ID numbers strictly for internal use, not derived from some outside source such as suppliers?

    The ID number is manually assigned to product record? This ID is not uniquely associated with a specific product as it can be re-used. You cannot use it for pk/fk linking and searching. Will need a unique ID for that. An autonumber field can serve. That or fix your data and standardize the product numbering to not allow duplicate use of the ID. If product must be given a new ID then that will mean another record in products table and the old product record 'deactivated' and not made available for selection in comboboxes, etc.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Count Number of People Per Product
    By Lorlai in forum Queries
    Replies: 3
    Last Post: 06-29-2011, 06:15 PM
  2. Best way to handle this Web Database
    By tucker1003 in forum Database Design
    Replies: 8
    Last Post: 03-18-2011, 12:14 PM
  3. 'handle' command
    By B Mellars in forum Access
    Replies: 6
    Last Post: 12-14-2010, 01:23 PM
  4. What is the best way to handle photos?
    By TundraMonkey in forum Database Design
    Replies: 2
    Last Post: 08-12-2009, 10:52 PM
  5. Replies: 7
    Last Post: 05-29-2009, 04:27 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