Results 1 to 2 of 2

Thread: Table Setup

  1. #1
    Join Date
    Sep 2003
    Atlanta, GA

    Unanswered: Table Setup

    Maybe I've been staring at Access for too long now, but have been stumped for days on the structure setup of my next project. I just have that eerie feeling that I'm missing something.

    In purchasing, I will be importing to a datasheet a list of item numbers that we need. This list is produced by others.
    When received, these item numbers can be either the internal item number (SKU), a vendors item number, or the manufacturer item number. The internal and vendor items also have alternate/substitutable items.

    tblOrders: ID7-OrderNum-ItemNum-QTY-UOM-Description

    Inventory Items, Tables:

    tblSKU: ID1-SKUNum-Description-QTY-UOM
    tblSKUalt: ID9-ID1-SKUNum-Description-QTY-UOM

    tblVendorID: ID2-VendorID-VendorName
    tblVendorItems: ID3-ID2-VendorItemNum-VendorItemDesc-MFGID-Price-LeadTime
    tblVendorAltItems: ID8-ID3-VendorItemNum-VendorItemDesc-MFGID-Price-LeadTime

    tblMFGItems: ID5-ID4-MFDItemNum-MFGItemDesc

    Table to link the items... Should I be breaking this down further?:
    tblLink: ID6-ID1-ID2-ID3-ID4-ID5
    or possible Option#2 (because the remainder can always be linked through query on the MFGID):
    tblVendorLink: ID1-ID2-ID3
    tblMFGLink: ID5-ID4-ID3

    I have that eerie feeling when designing the linked items, and have no idea how I'm going to begin to query the item number on this. Any recommendations?

  2. #2
    Join Date
    Sep 2003
    Atlanta, GA
    Ok, No reply earlier, so maybe I'm not clear on what I'm attempting to do.

    What I'm trying to determine is the best way to "link" item ID's to each other.
    Meaning if I enter my item number (SKU) in an order form, and no vendor has the same number item in the database, I want to be able to assign a permanent link to an existing vendor item number. This way, when I enter in my same SKU later, it will automatically pull up the vendors I have linked.

    My item number might be "ABC123" for a widget, but the same widget is sold by 2 vendors. Vendor A has it listed as "Widget1", and vendor B has it as "Item X".

    What is the best way to go about creating item links of this type?
    Last edited by inzzane; 10-29-11 at 21:19.

Posting Permissions

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