Designing Query for child/Parent relationship

Status
This thread has been Locked and is not open to further replies. The original thread starter may use the Report button to request it be reopened but anyone else with a similar issue should start a New Thread. Watch our Welcome Guide to learn how to use this site.

SolutionWeb

Thread Starter
Joined
Aug 2, 2004
Messages
33
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,
 

OBP

Joined
Mar 8, 2005
Messages
19,896
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.
 

SolutionWeb

Thread Starter
Joined
Aug 2, 2004
Messages
33
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..
 

OBP

Joined
Mar 8, 2005
Messages
19,896
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

SolutionWeb

Thread Starter
Joined
Aug 2, 2004
Messages
33
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
 

OBP

Joined
Mar 8, 2005
Messages
19,896
Why isn't the ChildID in table B unique?
It can probably be done in VBA, can youpost a copy of the database?
 
Status
This thread has been Locked and is not open to further replies. The original thread starter may use the Report button to request it be reopened but anyone else with a similar issue should start a New Thread. Watch our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top