Search code examples
ms-accesscomposite-keycompound-key

Can I create a third "value" on another table from the combined keys of a Bridge Table?

I'm an Access noob, and I'm trying to figure out how to create a third 'Value' from the two primary keys on a bridge table. Is that even possible? I'm likely not explaining this properly.

For my example, I have the primary Keys of ProvCode and CountryCode from separate tables that I've put into a bridge table because I want the combined values of those keys to populate a field called TravelLocation on another table...

This is what I've got so far:

enter image description here

Any suggestions? Or am I trying to do something that is impossible? I've been trying to figure out if a Compound key would do it, but I don't know how to go about creating one as opposed to a composite key.

I've created the Bridge table to bring the two values I need together, but I can't figure out how to get those two from the BridgeTable to my TravelLocation table.

Solution

  • Don't Repeat Yourself is considered best practice so calculate the codes whenever you need them. Also, I suggest some different table constructs like trips and including many to many relationships.

    Terrible example but hopefully instructive:

    Consider that customers who make many trips are probably the most important and that many customers could go on the same trip. So it makes sense that Customers and trips should be modeled as a many to many relationship. Further trips can in theory involve many locations. TripsLocales should be named TripsResorts and is the junction table for that many to many relationship. Customer Address information behaves the same as resort and is just left out.

    enter image description here

    
'starting with the resorts table
-----------------------------------------------------------------------------------------------------------------------------------
|      ResortID      |      ResortTypeID      |     ResortName     |     CountryID      |  ProvinceStateID   |     ResortCode     |
-----------------------------------------------------------------------------------------------------------------------------------
|                  1 |                      1 | Riza Resort        |                  1 |                  1 |                    |
-----------------------------------------------------------------------------------------------------------------------------------
|                  2 |                      2 | starbase hilton    |                  2 |                  2 |                    |
-----------------------------------------------------------------------------------------------------------------------------------
|                  3 |                      1 | kronos temple      |                  3 |                  3 |                    |
-----------------------------------------------------------------------------------------------------------------------------------
|                  4 |                      1 | vulcan temple      |                  4 |                  4 |                    |
-----------------------------------------------------------------------------------------------------------------------------------

    enter image description here

    'it is often easier to split a job into pieces.  first calculate the codes for each country and province  
'calculate your code your way; I was trying for cooler codes

SELECT Countries.CountryID, ProvincesStates.ProvStateID, [Countries].[CountryName] & [ProvincesStates].[ProvName] AS code
FROM Countries INNER JOIN ProvincesStates ON Countries.CountryID = ProvincesStates.CountryID;

'gives:

----------------------------------------------------------------------
|     CountryID      |    ProvStateID     |           code           |
----------------------------------------------------------------------
|                  1 |                  1 | Rizapalace               |
----------------------------------------------------------------------
|                  2 |                  2 | Earthawesome             |
----------------------------------------------------------------------
|                  3 |                  3 | Kronostime temple        |
----------------------------------------------------------------------
|                  4 |                  4 | Vulcantemple             |
----------------------------------------------------------------------

    enter image description here

    'then show a new table just like the resorts table but with the codes
SELECT Resorts.ResortCode
FROM Resorts INNER JOIN resortcodes ON (Resorts.CountryID = resortcodes.CountryID) AND (Resorts.ProvinceStateID = resortcodes.ProvStateID);

'you can go ahead and update the Resorts table if you want
'after selecting the update tab as shown Update:
UPDATE Resorts INNER JOIN resortcodes ON (Resorts.CountryID = resortcodes.CountryID) AND (Resorts.ProvinceStateID = resortcodes.ProvStateID) SET Resorts.ResortCode = [resortcodes].[code];

'gives: 

-----------------------------------------------------------------------------------------------------------------------------------
|      ResortID      |      ResortTypeID      |     ResortName     |     CountryID      |  ProvinceStateID   |     ResortCode     |
-----------------------------------------------------------------------------------------------------------------------------------
|                  1 |                      1 | Riza Resort        |                  1 |                  1 | Rizapalace         |
-----------------------------------------------------------------------------------------------------------------------------------
|                  2 |                      2 | starbase hilton    |                  2 |                  2 | Earthawesome       |
-----------------------------------------------------------------------------------------------------------------------------------
|                  3 |                      1 | kronos temple      |                  3 |                  3 | Kronostime temple  |
-----------------------------------------------------------------------------------------------------------------------------------
|                  4 |                      1 | vulcan temple      |                  4 |                  4 | Vulcantemple       |
-----------------------------------------------------------------------------------------------------------------------------------

    as a next step trip cost could be deleted from trips and staycost could be added to TripLocales. Then when you need TripCost you calculate it. Then as a next step replace staycost with roomrate...

    edit: There is an unnecessary direct link between countries.countryid and resort.countryid from part of the answer that was left on the cutting room floor. it was a demonstration of both what you could get away with in terms non normalization and more importantly a demonstration of how changes in table structure don't change the view of the data that you need, but just change the sql you write to convert the normalized tables into that view. The couple of times I've struggled with this issue it was always resolved based on which way resulted in sql I could write.