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

Tree Structure Query / Report - MS Access

P: 1
Sir,
I have a table named "RELATIONS_MASTER". 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
Feb 14 '08 #1
Share this Question
Share on Google+
1 Reply


FishVal
Expert 2.5K+
P: 2,653
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
    Expand|Select|Wrap|Line Numbers
    1. Field; Type; IndexInfo
    2. StudentID; AutoNumber; PK
    3. Family; String; FK
    4. Name; String
    5. University; String; FK
    6. Mark; Numeric
    7. 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
Feb 14 '08 #2

Post your reply

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