We are building a database of Email addresses which contains several layers of groups.
Emails sent to a group are forwarded to the lower level groups and so on to the final actual recipients.
We can build a table that records this structure either with a pigs ear or with a link table (if some people are included in more than one group)
Now..... We need a query (SQL Server or Access 2003) that will allow us to report on the structure from any level. We need to be able to enter the code for a specific group any where in the heirachy of groups and then be presented with a list of all the bottom end recipients without having the intermediate levels appearing in the list.
So our issues are:
- How to write a query that drills down until it reaches the bottom without knowing how many layers it has to handle.
- How to list all of the bottom level entities without listing the intermediate levels.