Sorry for the weird title. :0
Here's my problem, I am building a specifications database that uses unique item numbers/description to differentiate between items. Part of the exercise is creating a Parent/Child relationship where the Parent Item has multiple Child Items assigned to it. Think in terms of a formula. The product (parent) is called "1" and you need three ingredients (children) to make 1, Items 2, 3 & 4. The table is set up like this:
1 | 2
1 | 3
1 | 4
All items (both parent & child) reside in the same table because they are the same format, each is unique and I need to use them interchangeably, i.e. a parent item number for one formula may become the child in another formula.
This setup works fine, except when I run a query. I need the query to pull the Parent Item #, Child Item # and Child Item Description. However, the query can't differentiate between the two item numbers and always pulls the Parent Item Description. I have split the numbers into two tables, Parent & Child, and got the query to work fine. But I would rather have one table from which I can pull any item number.
Query Design
Query With Unified Item Number Table (The description is for the parent item #)
Query with items split into two tables (The description is for the child item #)
I want the 1st query to look like the 2nd query.