I'm new to Access (and databases in general) and have the following
problem....
TABLE_A is basically just a list of events occurring on a system. It
has fields;
"EVENT_ID" (auto number),
"EVENT_TIME",
"EVENT_DESCRIPTION".
TABLE_B is a list of activities that are undertaken in reaction to the
events of TABLE_A. It has fields;
"ACTIVITY_ID" (auto number)
"ACTIVITY_TIME",
"ACTIVITY_DESCRIPTION",
"SOURCE_EVENT".
"EVENT_ID" in TABLE_A is linked with "SOURCE_EVENT" in TABLE_B,
allowing me to build a query that shows me all the activities relating
to a certain event. So far, so good.
The problem is that an event in TABLE_A can be a "child" of another
event in TABLE_A. Each event can only have one parent, however it's
parent may also have a parent, ad infinitum. Also, each event may have
several children. Ideally, I would like to add one extra field to
TABLE_A such that if an event has a parent, it can be identified. We
then have fields;
"EVENT_ID" (auto number),
"EVENT_TIME",
"EVENT_DESCRIPTION",
"EVENT_PARENT".
NOW FOR THE QUESTION!!!!!!
If I wanted to show all the activities relating to an event and its
children, I could write some VBA code to mash through TABLE_A,
determine all the descendents of the event (if any) and then make a
query. But, is there a way to do this using the normal MS Access query
GUI? I'm happy to re-organise my tables, or add a new table if
necessary, but I don't want them to become too esoteric.
Cheers
Rich