Results 1 to 7 of 7
  1. #1
    kagoodwin13 is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Feb 2012
    Posts
    181

    Query help - permutations

    I have the following data structure

    CUSTOMER_ID LINE_OF_BUSINESS LOCATIONS
    1 PC 3
    1 BH 1
    1 NULL 3
    2 PT 1
    2 CM 2
    2 NULL 1
    3 BH 1
    3 CM 1


    I want the output to look like this as a permutation of the combinations of Lines of Business, if a customer has more than one line of business.

    LINE_OF_ BUSINESS COUNT_OF_CUSTOMERS
    PC+BH 1
    PT+CM 1
    BH+CM 1


    This data will be used to make a treemap graph.

    I tried to create flags, such as

    CUSTOMER_ID PC_BH_FLAG PT_CM_FLAG BH_CM_FLAG
    1 1 0 0
    2 0 1 0
    3 0 0 1




    And I could do a UNION like this:

    SELECT PC_BH_FLAG AS FLAG, COUNT(CUSTOMER_ID) AS COUNT_OF_CUSTOMERS FROM table UNION ALL SELECT PT_CM_FLAG AS FLAG, COUNT(CUSTOMER_ID) AS COUNT_OF_CUSTOMERS FROM table

    but the "table" is a very long derived table with many where statements and a union all within that to filter a table with millions of records down to about 3k or so.

    I'm afraid repeating the UNION ALL with the derived table repeated so many times is going to choke the crap out of the database and take 10 minutes to run. I trust the load balancer but that is probably not the most efficient query to get the desired output.

    Any ideas?

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    9,713
    see if this helps you get started. I only took an introductory statistics course but I don't think you're dealing with permutations, just combinations.
    http://allenbrowne.com/func-concat.html
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

  3. #3
    kagoodwin13 is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Feb 2012
    Posts
    181
    You are correct, combinations instead of permutations. I haven't messed with that in so long that I forgot about the difference. I just make big tables add and count. That's about my math extent these days.

    This really helps. I can't use VBA because I'm in SQL Server but the concatenation idea is definitely worth exploring. Thanks!

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    9,713
    Sorry, I didn't notice where you posted this. I don't browse forums, per se. I work off of the "new posts" list and probably should pay more attention to the forum the thread is in.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    2,610
    I would explore theDBguy's SimpleCSV function, you should be able to re-write it in TSQL:
    http://www.accessmvp.com/thedbguy/co...itle=simplecsv

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

  6. #6
    NoellaG's Avatar
    NoellaG is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    818
    Hi, I have created a table tblLOB with the fields Customer_ID, LOB (Line Of Busyness), Locations and filled it with your example data:
    Using the following SQL instruction:

    Code:
    select * from tblLOB;
    declare @LOB nvarchar(max)
    declare @CustID int
    
    
    create table #LOBs (CustID int, CustLOBs nvarchar(max))
    
    
    declare cCustomers  cursor fast_forward for
    select distinct  Customer_ID from tblLOB
    
    
    OPEN cCustomers
        FETCH NEXT FROM cCustomers INTO @CustID;
        
        WHILE @@fetch_status = 0
        BEGIN
            select @LOB = coalesce (@LOB,'') + coalesce(LOB,'') + '-' --as LinesOfBussiness
            from tblLOB where Customer_ID = @CustID
            insert into #LOBs(custID, custLOBs) values (@CustID, @LOB)
            select @LOB = ''
            FETCH NEXT FROM cCustomers INTO  @CustID
        END
        close cCustomers;
        deallocate cCustomers;
    
    
    select * from #LOBs;
    drop table #LOBs
    you get the following result:

    CustID CustLOBs
    1 PC-BH--
    2 PT-CM--
    3 BH-CM-

    I don't know if this is the result you want, and it needs a bit of finetuning, but I hope it helps

  7. #7
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    2,065
    Depending on the version of SQL you are running you could use the

    String_Agg() https://docs.microsoft.com/en-us/sql...l-server-ver15

    Function?
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

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

Similar Threads

  1. Replies: 8
    Last Post: 05-19-2013, 10:11 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