Results 1 to 2 of 2
  1. #1
    Star Lounger
    Join Date
    Mar 2001
    Location
    Michigan, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    lookup not pulling info (Access 97)

    Can anyone tell me what I'm doing wrong? I have a simple database that consists of three tables. A relationship that I'm trying to create between two of these tables is causing me some problems. One of the tables is a list of part components. This table contains a list of components, the cell where the components are manufactured and the time required to build the components.

    The other table contains a list of finished parts. This table contains the finished part number and description as well as the components used to build the part. Each finished part may consist of up to 6 components. I created a lookup in the Finished_Part table to allow the user to select the components used in the finished part. That lookup seems to work with no problem. What I can't seem to be able to do is to get the correct build time in association with the component. I think the problem is caused by only having one Build_Time column in my component list; but having multiple components used in each finished part. What seems to be happening is that the lookup is finding the Build_Time for the first component only, then using that same Build_Time for all of the other components.

    Here's an example of what my Finished Part table looks like.
    column1: Finished_Part
    column2: Finished_Part_Description
    column3: Component1 (user selects from drop down list from Component table)
    column4: Qty_Used_Comp1 (user entered quantity)
    column5: Build_Time (should be component 1 build time from Component table)
    column6: Component2 (user selects from drop down list from Component table)
    column7: Qty_Used_Comp2 (user entered quantity)
    column8: Build_Time (should be component 2 build time from Component table)

    I've tried to build a query that would bring in the build time for each component but that won't work either. Do you have any idea of what I'm doing wrong or how I should be handling this lookup?

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: lookup not pulling info (Access 97)

    Instead of a table for finished parts that contains the components, you need a separate table that joins the finished part to each component that went into it. That way, you can have as many components as you need (and no empty components) and you can have a Build_Time for each component in the finished part. The Part_Component table (or whatever you want to call it) would contain the PK for the Finished part table as a foreign key, with a separate record for each component that went into that part.
    Charlotte

Posting Permissions

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