Double Link between tables or use alias?

[flepkows]

I have two tables setup in an access database that will allow recursive nesting. One table is called Parts, and essentially, everything is a part, then the second table is called packages and it contains three fields. PackageID, ParentID(PartID of parent part from Part table), and ChildID(PartID of child part from Part table). This design allows me to infinitely nest parts inside of one another. Here is my problem, I am creating a crystal reports that shows all the parts that have two or more children parts. I am able to display the children parts ok, but along with them I want to be able to show additional data about them found in the Parts table. So I need to take the ChildID and use it and find this value in the Parts table and get the additional values i want to display with the children parts. This seems to be tough, it almost seems like I either need two links between the tables, one between PartID and childID and one between PartID and parentID, or do I add an alias to the Parts table and split the double links between two tables. Thanks in advance, and by the way I am using the push method with ADO.NET datasets and have gotten a lot of query engine errors messing around trying to find the answer to my problem, I think these problems arise due to differences between the dataset schema file and the link setup in the report itself. Any help would be greatly appreciated.

 

[fredp1]

Hi,

Can't be specific for ADO.Net, but with general Crystal,
You would have a alias table for the Part Table.
This is how I would go about it.
Add you Package and Part Table. Line the PartId between Package and Part tables. Change the alias on the Part table to call it PartParent.
Add the Part Table in again and link it to ChildId between the package and Part Table. Leave the Alias as PART.
Now add another Package table. It will have a alias of Package_1. Like it to the origial Package table.
Now add another Part table and link it to the ChildId of the PACKAGE_1 table. This 3rd Part table will now have a alais of Part_1.

This was the alias for the Package and the Part table have a similar naming convention.

I have a artical at home about Recursive Relationships and SQL Server that might be down your ally.
Its on

http://www.sqlmag.com

(subscriber only. Just do a search for Recursive Relationships) I'll find it for you if you are intereted.

Fred

 

[fredp1]

Actually,
You can download the code from sqlmag.com but not the artical.
The Artical Id is 42520.

Fred

 

[mrudolph]

Just another idea, once you get your table linking/alias stuff figured out, Crystal does have Hierarchial grouping capabilities that you might want to look into since you are dealing with data that has Parent/Child relationships.