Results 1 to 5 of 5

Creating a Query - Concatenating and transforming

  1. #1
    CP611 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    33

    Creating a Query - Concatenating and transforming

    I am trying to write a formula within my make table query (I'm a complete novice so gradually learning!). In this one particular field I would like to be able to combine data from 3 different fields in my input file. In doing so I need to also concatenate and change the wording. I've attached a file to explain what I mean (I've changed the actual data for data protection reasons).



    So if in field1 it said Example1, Example 4 I would want to query to change this to 1A,1R. However, if in field1 it said Example1,Example2 AND in Field2 it said Example2,Example 6, Example7 AND in field 3 it said Example2 I would want to query to turn it into 1A,1S,2S,2C,2D,3S.

    So there are a lot of variations and I've tried using an IIF statement but there are just too many variables.

    Any ideas?Access Example.txt

    Thanks!

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    7,738
    Example 4 only has 1 R.

    concat using: field1 & field2

  3. #3
    CP611 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    33
    Hi,

    Sorry I don't think I was particularly clear. I've attached another example with some more understandable data. In my input file there are 3 columns: SatEveRole, SunEveRole and MonEveRole. Each of these can either be filled in or not. I then want to be able to merge all of these into one field with a query. However, to fit in with another database, there is a character limit. So we are changing happy to help to H, Arena to A, Cyclist to C and Massage to M. Then in front of these letter will be 1, 2 or 3 depending whether they came from SatEve Role (1), SunEveRole, (2), MonEveRole (3).

    So with the new attachment it would come out like:
    1H,1A,1C
    2C,2M,3H
    1A,2C,3H

    I hope that makes things a little clearer?

    Thanks
    Attached Files Attached Files

  4. #4
    mike60smart is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    188
    Hi

    Can you upload a zipped copy of the actual database?

  5. #5
    CP611 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    33
    Hi,

    Unfortunately due to data protection I can't upload the database

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

Similar Threads

  1. Transforming data
    By keith2511 in forum Access
    Replies: 2
    Last Post: 09-18-2018, 01:33 AM
  2. Concatenating in Query and Truncating Export
    By electromarket in forum Queries
    Replies: 7
    Last Post: 07-03-2013, 07:30 AM
  3. Concatenating Criteria for a Query - Help!
    By Cavman in forum Programming
    Replies: 4
    Last Post: 01-04-2013, 07:23 AM
  4. Replies: 4
    Last Post: 10-14-2012, 03:34 AM
  5. Concatenating from a table or query.
    By stephen c in forum Programming
    Replies: 3
    Last Post: 07-13-2009, 08:14 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
  •  
Tech Forums: Microsoft Office Forums