Cross referencing two separate fields in a table for use in a combo box in a form

[ItsMe]

...The distance thing I would possibly consider an event because it is the research that helps me uncover a precursor to a potential bid...

I think you are onto something here. If you look at things from the Database side, you will want to define your entities. An entity will deserve its own table. You can define some entities by considering some sort of event or action that would create a new record, in other words, create a need to log this event or action. The term event is being used loosely to help you discover entities. Consider the Contacts table in the example. Contacts are not really an event. Yet, Contacts are identified as an Entity and deserve their own table.

Within the sample DB, there is a table which lists Universities. I used data from the internet to populate this table. I did not consider the relevance of a given university prior to adding said university to the table. Simply, it met the prerequisite of being a University. So, it was added.

As you define additional entities, you find additional uses for tblUniversities' PK field. The sample DB has a table for Clients. It has a Foreign Key field that can be populated with the value from the PK field in tblUniversties. There is not any need for adding all the different fields contained within tblUniversities to the tblClients. Duplicating these fields will go against the Rules of Normalization. Having said that, if the full description of the Universities was contained within tblClients AND there was not a need to have a SEPERATE table for Venues, you would not violate Normalization.



If you create a table for Venues, model it after tblClients. I have thought about this a little. It is possible to have your Clients and Venues combined within a single table. However, it is a simpler solution if there are separate tables for these entities. I believe the need for separate tables becomes more evident when considering additional entities that use fields already contained within tblUniversities, e.g. adding a tblDistance.

[TeamTraveler]

If possible, I would like to pick up where we left off last week on this. With June7 looking at my DB and some of the things ItsMe said - all of you for whom I am grateful for your responses BTW - I have done some retweaking and am starting to see some of the items identified above coming to light.

Okay. So, in a case where my ClientID and VenueID are BOTH pulling from my Universities table for use in a form, how would I broach this one? If I apply two joins to the same field in my university table, it seems to allow it UNTIL I try to pull the university name into two different fields and build criteria to associate the university with either the ClientID or VenueID - which I haven't figured out how to do syntax-wise.

I'd really like to avoid going the two-table route if I can, especially if the Client table would be identical to the Venue table.

Thanks again.

[June7]

There is no need to include the Universities table in the form RecordSource.

A query for report would include the Universities table twice. In the query table/query window will look like: Universities, Universities_1. Join instances of Universities table to ClientID and VenueID fields.

[TeamTraveler]

That worked fabulously! Thank you again!!