Results 1 to 5 of 5
  1. #1
    Tycho4511 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    4

    Reading data from one column and transforming that into abbreviation for a new column

    I am basically new to Access and have had fun teaching myself over the last week.



    I am trying to set up a table where the data from one column is read and then transformed for each entry into an abbreviated 3 letter code. For example, I have a column named “Image Source” and a field with the entry of “Shutterstock” I would like another field to automatically generate our internal code associated with Shutterstock (SHS). [Another example: “Flickr” generates “FLR”)

    I tried creating a query using IFF statements nested with each possible image source and return the particular code. This worked but there was a cap on the number of nested IFF statements so it seems like there is a better solution. (Also once I generated the query that returned the codes based on the source I did not know how to get that result into my main table).

    It might be important to know that the source data (column called "Image Source") is a lookup field. I did this so I could start typing the source and it would autocomplete the rest.

    Any suggestions or help would be appreciated. Thanks!

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Typically, the way to do this would be to create a lookup table that has your names and abbreviations.
    Then simply join this lookup table to your original table in a query, and return the abbreviation field.

    Note: You typically would do it in this manner, and NOT store the abbreviation as a field on the table level. In a well designed database, the fields in a table should NOT be dependent on one another. This is part of data normalization.
    General rule of thumb is that anything that can be easily calculated in a query should be done there, and not stored in the underlying table. Storing them at the table level can compromise the dynamic nature of the data and undermine data integrity (what if the abbreviation needed to change at some point? - then, you have a bunch of incorrect data records, unless you go back and fix them all).

  3. #3
    Tycho4511 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    4
    Thanks for the reply, this concept makes senses. Although I am still so early in learning I don't know how to join the lookup table to the original table in a query and return the abbreviation field. I could not find a good tutorial online. Does anyone know of one or could you outline the steps for me? Thanks! (I included a screenshot of the lookup table I created to see if I am on the right track with that part of it)

    Click image for larger version. 

Name:	Image Source Lookup Table.JPG 
Views:	6 
Size:	44.1 KB 
ID:	34437

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Show us the SQL VIEW of query1

    One week of self learning of database concepts and Access syntax is a very brief start.
    I recommend that you watch some youtube tutorials. eg https://www.youtube.com/watch?v=hMzf6u8hWqk

  5. #5
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Although I am still so early in learning I don't know how to join the lookup table to the original table in a query and return the abbreviation field.
    Take a look here: https://www.youtube.com/watch?v=OUazKpUT7TQ
    It shows a demo of how to join two tables in a query. Then it is just a matter of selecting which fields you want the query to return.

    Most any beginning Access book will show you how to do this basic query stuff too.

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

Similar Threads

  1. Replies: 4
    Last Post: 11-29-2017, 10:01 AM
  2. Replies: 1
    Last Post: 11-12-2013, 02:19 AM
  3. Replies: 1
    Last Post: 12-08-2011, 08:03 AM
  4. Replies: 1
    Last Post: 08-18-2011, 08:35 AM
  5. Reading values of a column
    By LAazsx in forum Programming
    Replies: 0
    Last Post: 11-25-2010, 06:05 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