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

[TeamTraveler]

First off, thank you for taking the time to review my issue and respond. Advance apologies if I didn't characterize my question properly.

I have a travel business where I work with college sports teams to locate lodging accommodations for their away events and recently developed a DB in Access 2010 to help me track and run my business. When evaluating a specific college team's needs for travel I look at their schedule, identify other colleges they travel to regularly to play, and note the distance to those colleges. So, in this case I have two tables - one to track all colleges and the second to track distances between colleges. I have a form where I input college information and on this form I also have a subform where I enter the distances to other colleges. Here is where I could use some help.

For example, College A is my client and they play regularly in conference at College B 300 miles away. On my second table I'll have one row of information where College A (Field 1) to College B (Field 2) is 300 miles (Field 3). If College B becomes my client and they play at College A, their distance is still 300 miles, but I have to add another row of data in my table to make College B my client and College A the away venue. It is redundant.

I have a combo box at the top of my form which drives both the input for the college and populates the subform with college information from the distance table. So, how can I get my combo box to look at both Field 1 and Field 2, reference Field 2 when the college is the client and trace the distance to the college in Field 1? Would I use a macro or VBA code?

Thank you once again for all your help.

Matt

[June7]

So you want to avoid entering the additional record that shows the reverse travel?

A UNION query could probably be used as the RowSource SQL.

SELECT Field1 AS StartLoc, Field2 AS DestLoc, Distance FROM tablename
UNION SELECT Field2, Field1, Distance FROM tablename;

[TeamTraveler]

June,

Thank you so much for the quick response and the helpful information. I'm going to see if I can integrate this into what I'm doing and follow up once done.

[TeamTraveler]

Thanks once again for the thought. I may have been too tired last night, but I attempted to implement the union model you proposed but was unsuccessful. However, in working through this, I began to realize that perhaps duplicates wouldn't be so bad in this case as a team can usually only have so many destination venues. This led me to my next thought.

What about invoking a query to save the reverse situation to my distance table when I close a record.

In other words, if I have this:

ClientUniversity VenueUniversity Distance (mi)
College A College B 300.0
College A College C 175.0
College A College D 230.0

When I save the record, I would expect to see this in my distance table:

Client Venue Distance (mi)
College A College B 300.0
College A College C 175.0
College A College D 230.0
College B College A 300.0
College C College A 175.0
College D College A 230.0

Am I thinking about this wrong?

My next question, and perhaps this is the wrong forum category, building the query logic to achieve this. I've been an Excel / Lotus 123 user for 25 years and am having difficulty drafting the logic to solve this. Thanks in advance once again. I do truly appreciate the time in assistance.

[ItsMe]

...Am I thinking about this wrong?...

You might be. Let me ask you this, "Do you know what a Primary Key is and how to create a JOIN in a query?" Relational databases are particular as to how you organize the data within. You have to approach Business Rules from the RDBMS perspective, not the User's perspective.

[June7]

Yes, code can do that.

What is purpose of 'Distance' table? What is the table that stores info about each event?

[TeamTraveler]

Thank you for the response. Honestly, I don't have the most amount of familiarity with relational databases, but to answer you, yes, I do know what a primary key is and creating a join in a query. In this case, I have a tables of universities, a UniversityID set as the primary key and the ClientUniversity field as the name of the university. I have other fields in the University table like address, city, state, etc. Also, since I draw on client and venue I could have also just simply given the field name UniversityName, but I didn't.

Then I have my distance table. The distance between client and venue university create one unique record.

From a relational standpoint, I have joins in my distance table pulling both the client university and the venue university from the university table.

[TeamTraveler]

Yes, code can do that.

What is purpose of 'Distance' table? What is the table that stores info about each event?

The purpose of the distance table is merely to track how far a client university (or prospective client university) is from where they play, the venue university. If they are travelling 50 miles to a venue, they aren't going to need overnight accommodations; they'll simply return to their home university that night. With the many hundreds of smaller universities, I need a way to track and determine easily the proximity to other universities where they play. It is more of a service to me than anything and hopefully a demonstration to them that I've performed some diligence on them to gauge where I think they'll need accommodations before I reach out by phone or email.

The table storing the "event" in this case would be the university table. Again I input all my college information into a form with a subform tied in where the client university become the university I am working on. The subform is where I track my venues and distance from the client.

If I haven't clarified, please let me know and thanks again.

[ItsMe]

I think it is a good idea to have a distance table. If It was a DB I was building, I may or may not store the distance in a table. I would, most likely, have VBA open Google maps and calculate the distance on the fly. Since it is not likely you would take this approach, use a distance table. You can manually update distances, as the need arises.

I have put together a sample DB. It does not address the distance table thing. Perhaps you can take a look at the sample, reverse engineer it, and ask yourself how to go about the distance table. Then, with your distance table in hand, ask yourself the questions in post #1 here.

I am gambling you will have new ideas/questions after looking at relationships from another person's perspective. With these new ideas, your distance table may look different and you may have new questions.

.

[TeamTraveler]

I think it is a good idea to have a distance table. If It was a DB I was building, I may or may not store the distance in a table. I would, most likely, have VBA open Google maps and calculate the distance on the fly. Since it is not likely you would take this approach, use a distance table. You can manually update distances, as the need arises.

I have put together a sample DB. It does not address the distance table thing. Perhaps you can take a look at the sample, reverse engineer it, and ask yourself how to go about the distance table. Then, with your distance table in hand, ask yourself the questions in post #1 here.

I am gambling you will have new ideas/questions after looking at relationships from another person's perspective. With these new ideas, your distance table may look different and you may have new questions.

.

Thank you for your time in responding and the sample DB. I'll take a look at it right now. I will also take a look at the Google maps VBA you mentioned; it certainly would eliminate the need for a table. Lastly, you're right. I probably will have new questions and new ways to improve things. I appreciate your help.

[June7]

A table to lookup distances between two locations does make sense. Then I expect there is a table for 'events' and assume there will be repeat clients.

Now, do you want the distance stored with each 'event' record or do a lookup 'on the fly' for distance from the lookup table? You already stated a desire not to have both directions in the lookup table. This can be accommodated.

[TeamTraveler]

I agree it does make sense.

As far as integration, it would probably make more sense to have the distance stored from a previous lookup. Universities don't change locations that often . That way only the only time that would be spent in retrieval would be new records; I don't have any clue how long this process takes in real time BTW. Now, with this said, the other consideration I have though is that I would still have to maintain some kind of table to pull in certain venues for each client university, otherwise I'm going to wind up looking individually through a drop-down list with upwards of 150-200 or more universities - not all of which the client may play at.

As far as the distance table goes, after looking at this other sample University db, it did jar some other thoughts for me. How hard or feasible would it be to run an append query to the distance table when I save the university record. In my mind the append query would quite simply copy the entire table of client fields to venue, copy the venue fields to client, copy the distance, then remove the duplicate entries. What I've just said sounds simple, but it is practical and doable? If doable, can someone please suggest the query structure?

Thanks.

[ItsMe]

...In my mind the append query would quite simply copy the entire table of client fields to venue, copy the venue fields to client, copy the distance, then remove the duplicate entries...

This goes against the Rules of Normalization. As a rule, you do not want to duplicate data. How long would it take? Maybe less than a second. You do want to design your DB to perform well. However, the reason not to duplicate data is for ease of management. You want to easily understand what and where your data is. You want to easily construct queries that retrieve concise data. You want to be able to easily construct queries to update and append records.

If you look at the sample DB, there is a table dedicated to Universities. I imagine this table will contain records for universities that may never be a client or a venue. If it is a client, add a record to your tblClients and include the PK value form tblUniversities in the FK field of tblClients. If it is a venue, add a record to tblVenues and include the PK value form tblUniversities in the FK field of tblVenues (there is not a tblVenues in the sample).

You can see this same technique of normalization in the sample DB's tblContacts. After you ponder that, consider how an event, like a game, might be logged in a table called tblEvents.

It is difficult not to think of things from the User's perspective. Users employ applications. You need to create a Relational Database before you can develop an application that has a User Interface. Relational Databases depend on normalization.

[June7]

There is technique to limit combobox list to only those venues associated with a client. This is called dependent or cascading comboboxes (or listboxes).

The Distances table should be a lookup table. It only gets a new record when a client/venue combination does not already exist. Should never be duplicates. The NotInList event of combobox/listbox is used for adding new records to lookup table 'on the fly' during data entry.

I don't think need a table for clients and a table for venues - just a table for Universities.

[TeamTraveler]

If it is a client, add a record to your tblClients and include the PK value form tblUniversities in the FK field of tblClients. If it is a venue, add a record to tblVenues and include the PK value form tblUniversities in the FK field of tblVenues (there is not a tblVenues in the sample).

I looked again further at the University sample DB. Thanks once again for supplying it. True, not every venue university will be a client, but I surely intend on contacting them just the same, especially if they are in conferences. Coaches, especially those in conference, know each other and talk regularly. It has never hurt me to name drop a coach at a competing team in conference. To your point though, when I started drafting the first version of my db, I did exactly this. I set up a table for my clients separate from the universities. When I did this though, I then found myself with situations where indeed I had a college that was both a client and a venue. At that point I was doing redundant work, entering the same information across both tables.

I probably need to clarify the "event". Everyone keeps mentioning the "event" and I suppose in my case, this can mean several things.

The "event" - where they separate out from being clients and venues is when I construct a bid, where a coach has asked me to begin looking at accommodations near the venue university where they are playing. But I'm not going to take the time to set up the bid until the coach tells me he wants me to do business together. The distance thing I would possibly consider an event because it is the research that helps me uncover a precursor to a potential bid.

June, I understand what you're saying about the combobox list, but the reason why I went with a subform off the main form is to be able to add on the fly a new university venue that they might not have played at in a prior season without me going back into another table or form to add then update the selection.