Tree Structure Query / Report - MS Access

[yemvee]

Sir,
I have a table named "RELATIONS_MAST ER". Content of the table is below.

CODE | PARENT | CHILD | LEVEL |
--------------------------------------------------------------
A01 | Red Bull | Red Eye | 01
B01 | Blue River | Blue Gun | 01
A11 | Red Eye | Red Sand | 02
A21 | Red Sand | Red Rock| 03
B11 | Blue Gun | Blue Bell | 02
B21 | Blue Bell | Blue Net | 03
B31 | Blue Net | Blue June | 04
......


I need a Query / report in the following form

A01 - Red Bull
|_ A11 - Red Eye
|_ A21 - Red Sand
|_ A 31 - Red Rock ...........
|_ .....

B01 - Blue River
|_ B11 - Blue Gun
|_ B 21 - Blue Bell
|_ B31 - Blue Net
|_ B41 - Blue June


Any body can help me! Urgent Please...

Regards,

Vivek

[FishVal]

Hi, Vivek.

I have several things to pay your attention to:

• first of all please specify whether a child may have one, fixed number or infinite parents
• [Level] field is not really needed to support the tree, moreover it is a duplicate information which may increase performance working as cache but needs to be synchronized with possible changes in tree structure
• Do you have a root node of the tree (a single node which has no parents)?
• It is rather simple to output the tree to a text file, spreadsheet or Word document. Making a query/report may be somewhat difficult especially if number of parents for child is not limited to one.
• And the last but not the least. Post your table(s) metadata.
  Here is an example of how to post table MetaData :
  Table Name=tblStudent
  

  [i]Field; Type; IndexInfo[/i]
  StudentID; AutoNumber; PK
  Family; String; FK
  Name; String
  University; String; FK
  Mark; Numeric
  LastAttendance; Date/Time

  It is not clear from what you've posted so far whether [Parent] and [Child] fields are FKs of another table or not.

Regards,
Fish