Question about displaying related data

[Jason Feinberg]

Hi Everyone,

I have a pretty simple database - a table for Project Info, one for Expenses, a Media Contacts Master List, and then a Media Contacts Per Project table. Schema below:

Full Size Image: http://www.ontargetmediagroup.com/img/DB_Schema.jpg

I would like to be able to create a new record in the Media Contacts Per Project table, select the project from a drop down list, then select a website name from a drop down list and have the relevant info automatically fill in on the layout.

Because the SiteID is the key that links the tables, I can easily get this relationship to work by using a SiteID drop down list. I pick the ID, and the website info gets filled in. However, this does me no good as I have hundreds of sites and cannot remmeber which SiteID is to which website.

So, what I want to do is this - pick a website by NAME and not SiteID - do I have to change my relationships between the tables, or is it a Layout issue that I cannot figure out?

THANKS!

Jason

[Jason Feinberg]

Okay - so by creating a field in the PER PROJECT table called Website Name and making that the related field to the Master List, it all works, but the problem is this - the SiteID is now not linked as a common field.. It seems a little sloppy to have the website name be the link in the relationship.. And now the SiteID field is not being used.. So my question is how can I STORE the SiteID as the link, but use the Website Name as the DISPLAY link - as in, I can choose that from a drop down list and the SiteIDs are linked?

I hope that makes sense. Those of you on here that answer these questions are truly amazing, being able to piece together all of our attempts at explaining our situation...

Thanks again,

Jason

[comment]

You can pick a website by SiteID AND SiteName - if you define your valulist to show the SiteName field along the SiteID. To show the selected site's name AFTER the selection, place the RELATED SiteName field on the layout (with no entry allowed). You can even place it on top of the SiteID field.