Results 1 to 5 of 5
  1. #1
    ClariceT is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2022
    Posts
    9

    Group records by concatenating textual fields and summing values

    Hi,



    My problem:
    I need to analyze a very large linked database. This database contains the data of some employees who, due to certain peculiarities of the company, appear in the same month with two different registration numbers and, consequently, also two different positions – they were promoted and this month are in the transition phase.

    What I need:
    Group the employees who are in this situation by name, concatenating their registration codes and the position they occupy, however, adding the amounts received.

    My data is in the format of Query A in this example, I need it to look like Query B:
    Click image for larger version. 

Name:	agrupar_registros.png 
Views:	19 
Size:	38.7 KB 
ID:	48481

    I got this result by doing a tedious combination of queries, but the processing got soooo slow.

    Is it possible to do this in a single SQL query?

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Possibly.
    But readers need to know the structure of your tables, and
    it would be helpful if you provided the queries involved in your "a tedious combination of queries".

    Readers need info and we have no knowledge of your environment.

    You may get some insight from Allen Browne's ConcatRelated function.
    Last edited by orange; 08-09-2022 at 05:15 AM. Reason: spelling

  3. #3
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Run some code to collect each persons Codes into a single field, tCOdList
    then in Qry A, remove COD field, join tCOdLIst on QryA.name.

  4. #4
    Join Date
    Apr 2017
    Posts
    1,673
    This a classical example of poor design!

    You need an employees table, where every employee is registered with unique ID. And tables like EmployeeOccuopations and EmployeeDepartments, where all employment changes for every employee are registered along with change date (and probably an EmployeeNames table too - to cope e.g. family name changes after marriage). When you'd have this, the promotion of employee would be registered in EmployeeOccuopations table, but employee's ID remains same.

  5. #5
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    make a field BLOCK in manager table ,
    make query qsBlocks that shows Person, CoD , and sorts the Person


    the code below will scan the list and collect the 'block' of CoD codes and update the table with the group of CoD per person.
    then add the 'block' to your summation query.


    Code:
    Public Sub CollectTags()
    Dim rst
    Dim vPrevName, vName, vID, vBlock
    
    
    HrGlass
    Set rst = CurrentDb.OpenRecordset("qsBlocks")
    vPrevName = ""
    
    
    With rst
      While Not .EOF
         vID = .Fields("ClientID").Value & ""
         vName = .Fields("LastFirst").Value & ""
         vWord = .Fields("CoD").Value & ""
      
         If vName <> vPrevName And vPrevName <> "" Then  'post new rec
             sSql = "update tManagers set [block] ='" & vBlock & "' where [LastFirst]='" & vPrevName & "'"
             DoCmd.RunSQL sSql
             
             vBlock = ""
         End If
         
         vBlock = vBlock & vWord & ","
         vPrevName = vName
        .MoveNext
      Wend
    End With
    
             sSql = "update tResults set [block] ='" & vBlock & "' where [LastFirst]='" & vPrevName & "'"
             DoCmd.RunSQL sSql
    
    
    
    HrGlass False
    MsgBox "Done"
    Set rst = Nothing
    End Sub

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

Similar Threads

  1. Replies: 2
    Last Post: 11-26-2016, 01:07 PM
  2. Replies: 1
    Last Post: 04-19-2013, 06:53 PM
  3. Replies: 2
    Last Post: 03-28-2013, 05:59 PM
  4. Replies: 3
    Last Post: 07-10-2012, 05:50 PM
  5. Concatenating fields from matching records
    By MWMike in forum Queries
    Replies: 1
    Last Post: 10-28-2010, 10:49 PM

Tags for this Thread

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