Table Relationship Question

[brharrii]

Is it possible to have a table that has a relationship with itself or is it better to create a second table?

I have a table:

tblProductInfo
- ItemNumber (PK)
- Description
- Material

The way our production works, every step in the production process creates a completely new product. This is not something I have control over, just something I have to work with. So from Raw material to finished product, I may have anywhere from 3-7 step products in between that each represent another phaze in the production. Each step needs to have its own record.

Step one would be considered the Raw matieral. The last step is a finished good, and everything in between is considered wip (work in progres). So essentially I need to create records for each step and indicate on each record which product we are using to build the next product. Ideally I'd like for the field [material] to simply refer to other records on the same table to determine which previous product we are building off of.

This sounds a lot more convoluted when I type it out, I'm sorry for that, Hopefully it got the point across though Thank you for any assistance you can offer.

Bruce

 

[plog]

You could use the same table and include a field named like "ParentItem" which hold an ID in tblProductInfo that the product is based off of.

Suppose you made ball bearings in 4 steps, it would look like this in your table:

ItemNumber, Description, Material, ParentItem
1, Hunk Of Metal, Steel, NULL
2, Metal Cube, Steel, 1
3, Metal Sphere, Steel, 2
4, Raw Ball Bearing, Steel, 3
5, Polished Ball Bearing, Steel, 4

 

[brharrii]

awesome, this is exactly what I am wanting to do. So the problem I ran into was that when I tried to create the relationship between the table and itself using the lookup wizard, it returned only one result: #NAME?

I'm obviously doing something wrong, but I'm not sure what it is.

 

[plog]

I'm not entirely sure how the relationship thingy works. I do know that in a query you would bring in two instances of your table. The first would be called 'tblProductInfo' the second would have a number appended to it and be named something like this: 'tblProductInfo_1'.

You would link ItemNumber in tblProductInfo to ParentItem in tblProductInfo_1. Then make the join a LEFT JOIN from tblProductInfo to tblProductInfo_1, meaning you would show all from tblProductInfo and only those matching in tblProductInfo_1. That would properly link those tables.