one dropdown showing records from 2 tables

[Steven Cappiello]

I have 3 tables altogether: Prospect, Patient, and Message

Messages (phone messages usually) can come from either prospects or patients.

How should I structure a relationship so that when I create a new message in the message table, the user is presented a dropdown list of all the prospects and patients ?

I was actually wondering that perhaps I don't in fact need a prospect & a patient table, and that I could just have a person table, that had a person_type field so that person could either be a prospect or a patient based on a checkbox.

Any insight anyone can offer would be awesome.

thanks. :

[RalphL]

A single person table sounds like a good idea. The dropdown list isn't. It will get very long unless you do some filtering. I have been using a portal and a filter using John Mark Osborne's Type Ahead method. This gives more infromation than a dropdown list to help select when there are more than one person with the same name.