Complicated Grouping? Query

[spitzmuller]

Alright, this might be a tricky one:

I have an Access database that stores people and what they do during their stay at the university. I tried to create some kind of curriculum for everybody. To do that one can choose from about a dozen "events" that can happen during the university carrer, e.g. 'enroll at uni' or 'take sabattical' etc. With me so far?

These events are connected to a certain status that the person obtains when that event is added to his/her curriculum, e.g. 'studying', 'in sabattical'

In other words, there are the following tables

1) persons
2) persons2events (many to many link table with date when the event was added)
3) events
4) events2statusses (many to many link table that defines which event is associated with which status)
5) statusses

Now I want to create a query that shows the persons together with their status on a given date. I hope I expressed myself clearly... not really easy to explain.

Sounds simple but I haven't figured it out yet. If anyone could point me in the right direction.

Help is greatly appreciated. Thanks in advance.

 

[PHV]

I don't understand the many-to-many relationship between events and statusses.
Another question: doe's an event have a lifetime ? (something like: in sabattical for one year)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[spitzmuller]

1) I just wanted the design to be flexible, i.e. let the database user decide which event will yield which status (the statusses are predefined, though)
2) An event (in my understanding) is something that happens at one point in time. If you take a sabattical for one year, there would be the events 'begin sabattical' and 'end sabattical' one year later.

Hope I make myself clear enough. Thanks!