Results 1 to 8 of 8
  1. #1
    RoNiN is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2021
    Posts
    2

    Add a text column together while grouping across row

    Hi Team!


    I'm trying to find a way to get this result, and haven't been able to find a solution as of yet. Crosstab doesn't seem to work as I'm not adding any fields together.

    My Data is this:
    Session ID PS
    766146 1H
    766146 20S
    756104 11H
    762639 11M
    768985 4H
    768985 19S
    729471 7H
    729471 11H
    729471 12M
    I'm looking to get it grouped by 1 row for Session ID and the PS text field added together with a dash inbetween:
    Session ID PS
    766146 1H-20S
    756104 11H
    762639 11M
    768985 4H-19S
    729471 7H-11H-12M
    Any thoughts on how to accomplish this?

  2. #2
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    It can be displayed quite easily in a Report by using the GroupBy on Session ID
    Attached Thumbnails Attached Thumbnails sessions.JPG  
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You could use any aggregate function to complete the query and ignore that if you used a second (SELECT) query and ignore the calculated field. However the number and names of the ct fields will be volatile so you can't simply have a calculated field in your select like [1H] & "-" & [20S] because it doesn't exist for most records and there's no guarantee that it will in the future either. If you have a limited number of possibilities you can define them so as to make these fields static and then include them all in one query calculated field. Null results would be ignored. There are some query wizards here (IMO) who might find this simple but that's not my strength. I always seem to see the code tree in the forest of possibilities. If there are a lot of PS values, then I think code would be more practical than complex calculations if fields that you have to maintain every time you have to deal with a new PS value that's been introduced.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    RoNiN is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2021
    Posts
    2
    Thank you for your response, but report view won't allow me to further use the data as in a table/query. Also, I need the PS data to be in the same row as the Session ID and not have multiple column data descending.

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I dunno. Those report results don't look concatenated to me (1H-20S).
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Use a concatenation function like theDBGuy's SimpleCSV (http://www.accessmvp.com/thedbguy/co...itle=simplecsv) or Allen Browne (http://allenbrowne.com/func-concat.html) or JoinFromArray on my site.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I see the first link as being unsuitable because it's supposed to work with a single field, but I didn't test so might be wrong about how I interpret the explanation there. The second is based on a one to many join and this table isn't, so a second query will be needed to retrieve only the distinct values of SessionID (SELECT DISTINCT...) as long as the only fields in that query are SessionID and PS.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I think this should work:

    "Select Distinct SessionID, PS_Combined:SimpleCSV("Select [PS] from tblSession Where [SessionID]= " & [SessionID],"-") FROM tblSession;"

    Note: replace tblSession with the name of the table and note that if the SessionID is a text you need to enclose it in single quotes.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

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

Similar Threads

  1. Replies: 5
    Last Post: 02-26-2021, 02:54 PM
  2. concatenate text column by grouping?
    By aero in forum Queries
    Replies: 2
    Last Post: 07-27-2016, 03:11 PM
  3. Replies: 5
    Last Post: 04-07-2015, 02:20 PM
  4. Replies: 11
    Last Post: 12-09-2013, 06:33 PM
  5. Replies: 9
    Last Post: 01-28-2011, 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