filtering from multiple match results

[malfet]

Hello everyone,

I'm having my first go at building a relational database, and I've having a bit of trouble wrapping my head around it.

I have two tables, one called people and one called events.

The people table has several fields, including a unique ID.

The events table has the following four fields: date (a standard timestamp), eventType (one of six predefined values), personInvolved (the same unique ID from the people table), and note (an arbitrary text string).

I have a layout that shows records from the people table, and I'd like to be able to show, for each person and for each of the six eventTypes, a textbox that will print the most recent note for that person in that category (ie, I'd like to end up with six new text-boxes per person, plucked from the events table where the person IDs match, where eventType is a constant built into the layout, and where date is the most recent of whatever remains).

I've created a relationship between uniqueID from the people table and personInvolved from the events table, but I haven't been able to figure out how to filter the numerous records in which personInvolved matches my uniqueID down to the one that I'm looking for. I've browsed through these boards and found numerous things that seem like the might be used in solving this problem (portals, calculation fields, scripts, etc.), but

I haven't been able to get any of them to work thus far. Any suggestions about the best way to do this?

Thanks in advance,

Peter

P.S. If this table architecture isn't the best way to do what I want to do here, I'd be interested in hearing why not for my own edification. Unfortunately, I'm not really in a position to change it, so I'm going to have to work with what I've got.

[BobWeaver]

Your structure sounds fine. You just need to create several compound relationships to get the different data. See the attached example file.

Events.fp7.zip

[malfet]

This is great. I really appreciate you taking the time to do this. After dissecting your example database, I think I understand this stuff considerably better.

-Peter