Results 1 to 2 of 2
2002-03-11, 13:41 #1
- Join Date
- Mar 2001
- Michigan, USA
- 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.
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?
2002-03-12, 02:41 #2
- Join Date
- Dec 2000
- Sacramento, California, USA
- 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