Following this thread:
https://www.accessforums.net/showthread.php?t=65191
I ran into another problem, though this is more to do with user-friendliness:
In short, I need to store data about a number of Transactions, that each harbor one or more Companies as destinations, that each harbor one or more Transports. Each Transport harbor a TransportNo and a Weight of the transported goods.
I identify transactions by TransactionNumber in TblTransactions.
I identify Companies by CompanyName in TblCompanies.
I identify a Destination by creating a table TblDestinations with a bulletin of TransactionNumber and a bulletin of CompanyName. I then let the user type in combinations of TransactionNumber and CompanyName in TblDestinations.
I now need to relate each of the combinations (Destinations) in TblDestinations to a number of Transports going to that Destination. I therefore create TblTransports.
In TblTransports, i create a bulletin of the Id from TblDestinations. I create a numeric column to number each Transport and a numeric column to inform of the Weight of each transport.
This way, I can keep track of how much Weight is going to what Destination on what TransactionNumber.
Like this:
TblDestinations.Id ... TransportNo ... Weight (in tonnes)
3 ......................... 2 .................. 57
But here's the problem: Using the Id of TblDestinations is not user-friendly as the user cannot go around remembering what the number 3 means. I need the user to have the information presented in the bulletin when entering the information in TblTransports, somewhat like this:
TransactionNumber|Destination ... TransportNo ... Weight (in tonnes)
GL0098 |S&W ............ 2 ................... 57
I have tried to make the bulletin show the information of more than one of the columns from TblDestinations. But it seems Access chooses to show the Id of the table where the information from TblDestinations is bulletined from, rather than the information from TblDestinations itself.
I get this:
TblDestinationsId|TransactionNumber|Destination ... TransportNo ... Weight (in tonnes)
3 |1 |1 .................................................. ......... 2 .................. 57
which really isn't helpful, as the user cannot find out what 3|1|1 means.
Another go: I have tried to create TblTransports in a way that harbors both a bulletin of TransactionNumber from TblTransactions and a bulletin of Companies from TblCompanies. This way, the user can see the data and type in relevant information, like this:
TransactionNumber ... CompanyName ... TransportNo ... Weight(in tonnes)
GL0098 ................... S&W ................. 2 ................... 57
This looks better, but the user may happen to type in a combination of TransactionNumber and CompanyName that does not appear on TblDestinations. This should not be.
I need the user to see the combination of TransactionNumber and CompanyName when typing in a Transport.
How to go about this?