By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,948 Members | 664 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,948 IT Pros & Developers. It's quick & easy.

Complex report (tree in report)

P: 1
I am not sure if acces is suitable for the task i want to use it.

So i am asking is it posible to draw tree in report.

To make evrything more simple i will tell task i need to do.
I need some way to show family tree in report.

I imagine than one entry (record) will consist of one value and link (name )to other value.

So when i choose to have report of first value. I would like to have first value
than all childs of it. Than all childs of those.

If it is imposibel maybe i can automaticly create new table where everything ordered in another way, which is more simpy to reflect in report.
Oct 29 '06 #1
Share this Question
Share on Google+
1 Reply


MMcCarthy
Expert Mod 10K+
P: 14,534
I am not sure if acces is suitable for the task i want to use it.

So i am asking is it posible to draw tree in report.

To make evrything more simple i will tell task i need to do.
I need some way to show family tree in report.

I imagine than one entry (record) will consist of one value and link (name )to other value.

So when i choose to have report of first value. I would like to have first value
than all childs of it. Than all childs of those.

If it is imposibel maybe i can automaticly create new table where everything ordered in another way, which is more simpy to reflect in report.

You need to create a table of family members with a foreign key reference in the table to the primary key of the same table that defaults to 0 (This is the ultimate parent.

e.g tblFamily

Expand|Select|Wrap|Line Numbers
  1.  
  2. FamilyID     FirstName   SurName   ChildOfID  SpouseOfID
  3.   1             John       Doe           0          3
  4.   2             Mary       Doe           1          0
  5.   3             Anne       Doe           0          1
  6.  
  7.  
You could then run a query like...

SELECT F1.FamilyID, F1.FirstName & " " & F1.Surname As Parent, IIf(F2.SpouseOfID=F1.FamilyID, F2.FirstName & " " & F2.Surname, "N/A") As Spouse, IIf(F3.FamilyID=F1.FamilyID, F3.FirstName & " " & F3.Surname, "N/A") As Child
FROM tblFamily As F1 LEFT JOIN tblFamily As F2 LEFT JOIN tblFamily As F3
WHERE F1.ChildOfID=0;
Oct 29 '06 #2

Post your reply

Sign in to post your reply or Sign up for a free account.