Tech Support Guy banner
Status
Not open for further replies.
1 - 6 of 6 Posts

·
Registered
Joined
·
33 Posts
Discussion Starter · #1 ·
Hello,

I am trying to create a child/Parent relationship and find all children that are belong to the parent in one table using Access. I.e. Car parts that belong to a car and nuts that belong to a car but are used to assemble other components of the car. Is there any solution for this Query? Is it possible to recursively search the table till all the children are found including their parents? Is sort of like designing the Tree, in C++. Please any suggestion would be strongly appreciated.
Thanks,
 

·
Registered
Joined
·
19,932 Posts
What links the children to their respective Parents in the Table(s)?
Is the data in more than one table?
Do you want to be able to enter a part number and find all associated parts?
I worked in the auto industry for 33 years and designed quite a few databases so I need to know how your data is arranged and precisely what you want to do.
 

·
Registered
Joined
·
33 Posts
Discussion Starter · #3 ·
Thanks OBP,

In fact I have one table Called "Table A" that contains the child/parent relationship with two columns ChildID and ParentID. So to answer your first question regarding relationship in a table, for example in Table A row 5, a child in that row might have for example three parents i.e. 54,3,1. so there is a possibility that one child has many parents. Also I have another table Called "Table B" that contains among many other things the ChildID.
I need to find out that the ChildID from Table B has how many parents by looking at Table A and if it is possible print out the all the parents on that child in a report. Ie. ChildID=6 in Table B has three parents in Table A 6 3,2,1 and 5 4,3,1 and 32,1 so on..
To answer your second question: No I don’t need to find the part using inputs, but what I want to do is to find out that a child has how many parents and possibly print the child/parents relationship on a report.
Does all this make sense!!??
Thanks for your help..
 

·
Registered
Joined
·
19,932 Posts
In the query wizard select the Table B and then select the ChildID field. Run the Query, it should find all the ChildIDs. On the Main Menu select Tools>Relationships and add tables A and B. On Table B click on the ChildID field and drag it over to table A ChildID. In the relationship presented by Access click on the "Enforce Referential Integrity" check box.
For this to work there must be no "duplicate" ChildIDs in Table B and the ChildID must be a key field. Save the relationship.
Return to the Query and in design mode add table "A" to the query. Access should now automatically create the "one to many" relationship set up in "Relationships".
From Table A add the ChildID and ParentID fields to the Query. In the "criteria" row of Table B's ChildID add [Enter ID]. When you run the query now Access will ask you for the ChildID you want. Enter the ID and the Query should now find all of the records for that ID along with the ParentID.
 

Attachments

·
Registered
Joined
·
33 Posts
Discussion Starter · #5 ·
It is an excellent solution but the ChildID is not unique, so I can not use it as a primary key. The other thing that I was thinking of was recursive Query that would keep reading one record from first row and compare that against other row, if that record is repeated than there would be a relationship between the rows the child record has a parent, this is all done on table A and after sort of designing the Tree, a parent with many childerns, than it would be very easy to establish the relationship of the table B. Is it possible to do this in Visual Basic?

Thanks
 
1 - 6 of 6 Posts
Status
Not open for further replies.
Top