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

    Creating a query that shows multiple records from a table in one string

    Following this thread:

    https://www.accessforums.net/showthread.php?t=65154


    I am now trying to add to my Query a column where I can see multiple records from a table in one single cell of the Query.

    Allow me to elaborate:
    I have a table with information of transactions, TblTransactions. In that table, I have a column with records of 'transaction numbers' (for reasons of convenience, I store the numbers as text, since they have a letter part and a number part, and it has proven incenvenient to have letters and numbers in separate columns).

    Like this:
    TransactionNumber


    RG102
    GR435
    PL624


    I then need to store data about one or more operations related to transaction numbers.

    In therefore create table TblOperations with the operations.
    Like this:
    Operation
    R12
    G45
    L4



    I then create at table with information about what transaction numbers have what operations, TblAssignedOperations.

    Like this:
    TransactionNumber ... Operation
    RG102 ..................... R12
    RG102 ..................... G45
    RG102 ..................... L4
    GR435 ..................... R12
    GR435 ..................... L4
    Note that PL624 hold no operations.


    I need a Query that returns this:
    TransactionNumber ... OperationsAssigned
    G102 ....................... R12, G45, L4
    GR435 ..................... R12, L4
    PL624 ...................... (null)


    I could theoretically use the in-built Bulletin of Access allowing me to have this information about operations in a column beside the transaction number in TblTransactions, as this Bulletin allows for multiple records in one row, but I also need to connect the information about each operation assigned to a multitude of records of amounts related to that assigned operation. Therefore, the built-in Bulletin is not satisfactory.


    I would appreciate any help I can get.

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Queries don't do that. You'd need vb code to collect them.

    but you could create a Crosstab query to do something similar.
    use the query wizard to pick your row headers, and column headers.

  3. #3
    Nanuaraq is offline Advanced Beginner
    Windows 7 32bit Access 2013 32bit
    Join Date
    Mar 2017
    Posts
    77
    My end product should be a short overview. I have about 30 possible options for Operations, but only 2 or 3 related to each transaction. Crosstab showing all 30 of them would therefore seem unwieldy to the user. Hence my need of everything in one short string in the query.

    How would I go about using VB code? I know of how to enter the terminal, and have used some VBA in excel, but I am otherwise quite inexperienced in VB. Is this an easy or a tough task?

  4. #4
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    collect all records into a single string
    create a query where the records are sorted on Trans#
    the code scan thru the list, when the trans# changes, it posts the result of the combined OpsAssigned
    Paste the code into a module and run it from a button on a form

    usage: getRecs2String "qsMyQuery"

    Code:
    '----------------
    Sub getRecs2String(ByVal pvQry)
    '----------------
    Dim sSql As String, sBigStr As String
    Dim rst 'As Recordset
    Dim vPrev, vTxt
    
    vPrev = ""
    Set rst = CurrentDb.QueryDefs(pvQry).OpenRecordset      'open the query given
    With rst
       While Not .EOF
            vCurr = .Fields("TransactionNumber").Value
            vTxt = .Fields("OperationsAssigned").Value
            
            If vPrev <> vCurr And vPrev <> "" Then        'if trans# then post the results
                    'remove the last comma
                 sBigStr = Left(sBigStr, Len(sBigStr) - 1)
                    
                    'post the result to a table
                 sSql = "Insert into tTargTable ([TransactionNumber],[OperationsAssigned]) values ('" & vCurr * "','" & sBigStr & "')"
                 DoCmd.RunSQL sSql
                 
                 sBigStr = ""
            End If
                
            sBigStr = sBigStr & vTxt & ","
            vPrev = vCurr
           .MoveNext    'next record
       Wend
    End With
                
    rst.Close
    Set rst = Nothing
    End Function

  5. #5
    Nanuaraq is offline Advanced Beginner
    Windows 7 32bit Access 2013 32bit
    Join Date
    Mar 2017
    Posts
    77
    Wait, while you were answering that, I googled some more and found out that it is possible to embed a form in another form, and to have the embedded form show more than one record, and indeed to have the embedded form show only those records that meet the criteria for the parent form. This even provides mere with the ability to embed summed queries in the parent form. Wonderful.

    But thank you for taking the time to replying.

  6. #6
    HiTechCoach's Avatar
    HiTechCoach is offline MS MVP - Access Expert
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2010
    Location
    Oklahoma, USA
    Posts
    702
    Nanuaraq,

    It is possible to do what you originally ask. I have only had to do it a few time since 1993 with the 100's of databases I have created. It has only been for exporting to Excel.

    I urge you to look at the templates that is part of Access. Look at the Sale/Invoicing templates They will show you how to use sub forms to show records from a related table.

    Also check out the Northwind example. It shows what a more complete application in Access can do.

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

Similar Threads

  1. Replies: 1
    Last Post: 06-24-2016, 02:34 PM
  2. Replies: 8
    Last Post: 04-18-2013, 04:41 PM
  3. Replies: 3
    Last Post: 01-01-2013, 06:22 PM
  4. Creating a String in VBA over multiple lines
    By Mazdaspeed6 in forum Programming
    Replies: 13
    Last Post: 01-03-2011, 12:32 PM
  5. Creating a string from all records in a table
    By ocordero in forum Programming
    Replies: 2
    Last Post: 08-07-2006, 09:21 AM

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