Results 1 to 4 of 4
  1. #1
    Nanuaraq is offline Advanced Beginner
    Windows 7 32bit Access 2013 32bit
    Join Date
    Mar 2017
    Posts
    77

    Post Is it possible to create a string in a calculated field?

    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?

  2. #2
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I then let the user type in combinations of TransactionNumber and CompanyName in TblDestinations.
    You DON'T allow users to edit table data, so this seems to be the crux of your problem. It reads like the tables are normalized (though I confess I glossed over the treatise to try to get to the problem part) which means you're storing FK's in related tables as you should. Using a form based on a query (if there are multiple tables involved) allows you to present meaningful information while adding/editing data based on the actual relationships. This is why I advocate building a working query first, then the form that needs it, not the other way around.

  3. #3
    Nanuaraq is offline Advanced Beginner
    Windows 7 32bit Access 2013 32bit
    Join Date
    Mar 2017
    Posts
    77
    Quote Originally Posted by Micron View Post
    You DON'T allow users to edit table data, so this seems to be the crux of your problem.
    Well if you're done chastizing, I wasn't planning on. And how can this have any connection to the problems I experience?

    Quote Originally Posted by Micron View Post
    It reads like the tables are normalized (though I confess I glossed over the treatise to try to get to the problem part) which means you're storing FK's in related tables as you should.
    What's an FK?

    Quote Originally Posted by Micron View Post
    Using a form based on a query (if there are multiple tables involved) allows you to present meaningful information while adding/editing data based on the actual relationships. This is why I advocate building a working query first, then the form that needs it, not the other way around.
    I was kind of hoping for help on doing the query.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Well if you're done chastizing,
    I don't chastise, I educate whenever possible. Sorry if you took it that way; I try to be concise and direct due to the number of posts I contribute to. If my style is offensive to you, I don't have to respond if you prefer.
    how can this have any connection to the problems I experience?
    Because using a form (and not allowing edits in tables) lets you include any data. This includes data that is necessary but not always descriptive (as indicated in your first post), as well as any descriptive labeling or related data terms. So if your user would see 3 instead of a meaningful term because the intent is to edit data in tables, the form takes care of that problem if it's properly designed.

    FK = Foreign Key; PK = Primary Key
    If you didn't know that, chances are you are not up to speed on db normalization, in which case creating properly functioning queries might be a problem. If normalization is not a concept you're familiar with, you really should study that first.
    Normalization Parts I, II, III, IV, and V
    http://rogersaccessblog.blogspot.ca/...on-part-i.html
    and/or
    http://holowczak.com/database-normalization/
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 10
    Last Post: 06-10-2015, 08:16 AM
  2. Replies: 5
    Last Post: 04-04-2014, 03:33 PM
  3. Calculated field to change a string to a number
    By Cyberwombat in forum Database Design
    Replies: 2
    Last Post: 01-09-2014, 02:48 PM
  4. Replies: 3
    Last Post: 05-26-2013, 08:57 AM
  5. Create a query With a Calculated Field
    By Content1 in forum Queries
    Replies: 2
    Last Post: 01-05-2013, 12:46 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums