Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2007
    Posts
    16

    Unanswered: Simple Table - Complex Data Manipulation

    My first post - hope it's not too much:

    I have an Excel table with three columns: Part number, Description and price. Here are the challenges and what I need to do:
    • First, some of the items are universal across multiple main products. They are replacement parts for appliances. In other words, one part number might be uses on 5 different models, but it's the same part number and called the same thing in it's description. I have the table broken up by " sub-sections, eg; there are sub-headers at intervals where each model the item pertains to... it looks something like this:


    Model "A"
    # Descr Price
    123 Widget for extrusion $23.00
    WP277 Brass Nut $ 1.00

    Model "16"
    # Descr Price
    123 Widget for extrusion $23.00
    AG133 Wing Nut $ 2.00

    So, there are duplicates that have to be purged but, before I make them all unique records, I want to first re-label the descriptions to reflect what models of appliances they are applicable to, then extract those records to a separate table. I need all of the "Universal" items in one table, "Model 'A' " specific items in one table, "Model '16' " in another, etc.

    Is there a quick and easy way to manipulate the descriptions, or do I need to manually edit the descriptions and then eliminate the duplicates?

    Do I need to create separate worksheets to separate the Items by model or is there a way to have the data imported into different tables from one spreadsheet?

    It's been 10 years since I have been in school, and I don't work with VB or even Access on a regular basis --- basically, I am a novice. Any help would be appreciated.
    I'm Old, I'm Grumpy, and I have no clue what I'm doing

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    What you have is a many-to-many relationship. It describes how models relate to parts. For example, one model has may parts, and one part is used for many models (not all parts but if there is one part then it works as a many to many). In order to properly create that type of relationship you will need three tables in Access.

    One table will be for models, one for parts and one for model parts. The tables might look like:

    tblModels
    ------------
    ModelID
    ModelDescription


    tblParts
    -------------
    PartID
    PartDescription
    PartPrice


    tblModelPart
    --------------
    ModelID
    PartID


    I think it would be a mistake to have different descriptions for the same part. How would you then inventory that part? If you have one entry in the Part table changing the price becomes easier (one update, not several). That is one of the keys to relational databases (no repeated data, if you need to update, you should only have to update once).

    As far as setting this data up in Access from an Excel spreadsheet, it sounds like it will be some manual manipulation. You should be able to get all of your parts into the parts table by linked to a spreadsheet that has several worksheets(one for each model) and adding the parts to the table. BUt the models seem like that have to be manual and the model parts will have to be manual.

Posting Permissions

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