Searching for parent in child-parent table

[takatam]

Hi,

I have a table (TableRelations) which contains a list of parent-child
relationships of documents, figuratively speaking. Thereby a 'child'
is a document which is referenced to by the 'parent' document. A
parent can have several child documents (max 5), and at the same time
every parent can also be (not a must) a child document.

I would like to build a form where I can select a specific document-ID
and then two lists/tables are populated: One showing all child
documents, and one showing all parent documents of the selected
document.

Showing the list with the children is not difficult, a simple extract
from the table.

For the list of parent documents I would need to parse the part of the
table which contains the child documents, extract which documents
actually act as parent to the document in question, then remember,
list and sort the obtained list of parents.

How do I build this query? Or do I need to build multiple queries? Or
should I take a completely different approach?

Any ideas with this regard are appreciated.

Thanks for helping a newbie,
T

 

[KARL DEWEY]

It would be a big help if you posted your table structure - field names with
datatype.
Also sample data will help.

 

[takatam]

Hi Karl,

Here's a sample TableRelations content, with the document in the first
column and the respective children in the following 3 columns:

Doc Ch1 Ch2 Ch3
A01 A02 A03 A05
A02 A03 A05
A03
A04 A01
A05 A03
A06 A01 A03 A04

The first line shows the column headers. Circular references should
not exist. The data type is text for all fields.

What I would like to have is a page with the following information
(A01 being a drop-down list based on the first column of above table):
_________________________________________
Selected document: A01

Children:
A02
A03
A05

Parents:
A04
A06
_________________________________________

As you'll notice, all documents which have A01 as child are listed
under parent. As another example, the document A03 has no children but
has A01, A02, A05, A06 as parent.This information should be available
such that I can then use it as a reference to get other information on
the documents, if needed (e.g. doc name, age...). This one I'm not
sure yet.

Thanks a lot for your help,
Tom

 

[KARL DEWEY]

What you have is a spreadsheet instead of a relational database. To go from
here you need to use a union query to re-organized the data.
SELECT DOC, Ch1 AS Child
FROM YourTable
SELECT UNION ALL DOC, Ch2 AS Child
FROM YourTable
SELECT UNION ALL DOC, Ch3 AS Child
FROM YourTable;
To get this ---
Doc Child
A01 A02
A01 A03
A01 A05
A02 A03
A02 A05
Then create your report.

Better would be to use this table structure --
DocID - autonumber - primay key
DocNumber - text
Title - text
ParentID - number - long integer - foreign key - related to DocID

In the relationship window add the table twice (Access adds a sufix of '_1'
to the second instance). Click on DocID of the first table and drag to the
ParentID of the second. Select Enforce Referential Integerity and Cascade
Update Related Fields.

When a document is entered you indicate it's parent if it has one. It can
be left blank and entered later.

 

[takatam]

Hi Karl,

I'm working my way there, so far everything is going well, no major
obstacles. I'll come back if I hit one

Just so I can learn something:
- Should the UNION ALL not be placed between the SELECT statements?
- Why did you chose UNION ALL instead of UNION?

The reason why it is part of a database is because each document has
several properties (~10) which are stored in a separate table. And
each document has multiple status, which again is in a separate table,
which involves some calculations. More tables could follow later. And
in the end I would like to pull together all information regarding one
document onto one page. This part is the tricky one, the rest I just
need to pick one row each.

Thanks for your help,
Tom

 

[KARL DEWEY]

Why did you chose UNION ALL instead of UNION?
The ALL lets duplicates come through.
You are using tooo many tables. Do some reading on database normalization.

Calculations should not be stored as a rule as parts of the equation change
and then the results are then out of date. The onlyy reason to store
calculation would be for historical purposes and even then maybe storing the
factors that change with time (exchange rate) with a date will allow
re-creation of the calculation.