none
Jet-SQL, combine 3 columns into 1 with group sorting order RRS feed

  • Question

  • Hey community,

    I am working on a project that needs me to combine 3 columns into 1. In addition, the combined records should following a grouping order.

    Column1 is the parent of Column2 and Column2 is the parent of Column3.

    Here is the sample data:

    Col1	Col2	Col3
    1-a	2-a	3-a
    1-a	2-a	3-b
    1-a	2-b	3-c
    1-a	2-b	3-d
    1-a	2-b	3-e
    1-a	2-b	3-f
    1-a	2-c	3-g
    1-a	2-c	3-h
    1-a	2-c	3-i
    1-a	2-c	3-j
    1-a	2-c	3-k
    1-a	2-d	3-l
    1-a	2-d	3-m
    1-a	2-d	3-n
    7-a	8-a	9-a
    7-a	8-a	9-b
    7-a	8-b	9-c
    7-a	8-b	9-d
    7-a	8-b	9-e
    7-a	8-b	9-f
    7-a	8-c	9-g
    7-a	8-c	9-h
    7-a	8-c	9-i
    7-a	8-c	9-j
    7-a	8-c	9-k
    7-a	8-d	9-l
    7-a	8-d	9-m
    7-a	8-d	9-n
    

    Proposed result:

    Please advise, thanks.

    Sincerely,

    Mel

    Friday, September 4, 2015 4:06 PM

Answers

  • I wrote 6 queries to get my desired result in Access. I may need your help to simplify these queries.

    Query1: Level1
    SELECT DISTINCT Col1.Col1, Col1.L1 AS Rank1, 0 AS Rank2, 0 AS Rank3
    FROM (SELECT e.Col1, c.L1, 0 AS L2, 0 AS L3 FROM ThreeLevel AS E INNER JOIN (SELECT a.Col1, COUNT(b.Col1) AS L1 FROM (SELECT e.Col1 FROM ThreeLevel AS e GROUP BY e.Col1)  AS a, (SELECT e.Col1 FROM ThreeLevel AS e GROUP BY E.Col1)  AS b WHERE a.Col1 > b.Col1 OR a.Col1 = b.Col1 GROUP BY a.Col1)  AS C ON e.Col1 = c.Col1 ORDER BY c.Col1)  AS Col1;
    
    Query2: Level2
    SELECT DISTINCT Col2.Col2, Col2.Col1, Col2.L1 AS Rank2
    FROM (SELECT e.Col2, e.Col1, c.L1 FROM ThreeLevel AS E INNER JOIN (SELECT a.Col2, a.Col1, COUNT(b.Col2) AS L1 FROM (SELECT e.Col2, e.Col1 FROM ThreeLevel AS e GROUP BY e.Col2, e.Col1)  AS a, (SELECT e.Col2, e.Col1 FROM ThreeLevel AS e GROUP BY e.Col2, e.Col1)  AS b WHERE a.Col2 >= b.Col2 AND a.Col1 >= b.Col1 GROUP BY a.Col2, a.Col1)  AS C ON e.Col2 = c.Col2 ORDER BY c.Col2, c.Col1)  AS Col2;
    
    Query3: Level2_
    SELECT '    ' & L2.Col2 AS Col2, L1.Rank1, L2.Rank2, 0 AS Rank3
    FROM Level1 AS L1 INNER JOIN Level2 AS L2 ON L1.Col1 = L2.Col1;
    
    Query4: Level3
    SELECT DISTINCT Col3.Col3, Col3.Col2, Col3.Col1, Col3.L1 AS Rank3
    FROM (SELECT e.Col3, e.Col2, e.Col1, c.L1 FROM ThreeLevel AS e INNER JOIN (SELECT a.Col3, a.Col2, a.Col1, COUNT(b.Col3) AS L1 FROM (SELECT e.Col3, e.Col2, e.Col1 FROM ThreeLevel AS e GROUP BY e.Col3, e.Col2, e.Col1)  AS a, (SELECT e.Col3, e.Col2, e.Col1 FROM ThreeLevel AS e GROUP BY e.Col3, e.Col2, e.Col1)  AS b WHERE a.Col3 >= b.Col3 AND a.Col2 >= b.Col2 AND a.Col1 >= b.Col1 GROUP BY a.Col3, a.Col2, a.Col1)  AS c ON e.Col3 = c.Col3 ORDER BY c.Col3, c.Col2, c.Col1)  AS Col3;
    
    Query5: Level3_
    SELECT '        ' & L3.Col3 AS Col3, L1.Rank1, L2.Rank2, L3.Rank3
    FROM (Level1 AS L1 INNER JOIN Level2 AS L2 ON L1.Col1 = L2.Col1) INNER JOIN Level3 AS L3 ON L2.Col2 = L3.Col2;
    
    Query6: ThreeLevelGroupingOrder
    SELECT Col1 AS Col
    FROM (SELECT * FROM Level1 AS L1
    UNION
    SELECT * FROM Level2_ AS L2
    UNION
    SELECT * FROM Level3_ AS L3
    ORDER BY Rank1, Rank2, Rank3)  AS Rank;

    Query result in Access:




    Monday, September 7, 2015 1:48 AM
  • I did it in 3 queries but it is SLOW!  --

        Query14 --

    SELECT TableA.Col1 & TableA.Col2 & TableA.Col3 AS Sort1, 1 AS Line, TableA.Col1, "     " AS Col2, "     " AS Col3
    FROM TableA
    ORDER BY TableA.Col1 & TableA.Col2 & TableA.Col3
    UNION ALL SELECT TableA.Col1 & TableA.Col2 & TableA.Col3 AS Sort1, 2 AS Line, "     ", TableA.Col2, "     "
    FROM TableA
    UNION ALL SELECT TableA.Col1 & TableA.Col2 & TableA.Col3 AS Sort1, 3 AS Line, "     ", "     ", TableA.Col3
    FROM TableA;

        Query16 --

    SELECT Query14.[Sort1] AS Expr1, Query14.Line, Query14.Col1, Query14.Col2, Query14.Col3, (SELECT Count([XX].[Line]) FROM Query14 AS [XX] WHERE [XX].[Sort1]&[XX].[Line] <= Query14.Sort1 & Query14.Line) AS RecCount
    FROM Query14
    ORDER BY Query14.[Sort1], Query14.Line;

         Query17 --

    SELECT IIf([Col1]<=(SELECT Max([XX].Col1) FROM Query16 AS [XX] WHERE [XX].RecCount < Query16.RecCount),"     ",[Query16].[Col1]) & "  " & IIf([Col2]<=(SELECT Max([XX].Col2) FROM Query16 AS [XX] WHERE [XX].RecCount < Query16.RecCount),"     ",[Query16].[Col2]) & "  " & IIf([Col3]<=(SELECT Max([XX].Col3) FROM Query16 AS [XX] WHERE [XX].RecCount < Query16.RecCount),"     ",[Query16].[Col3]) AS Col
    FROM Query16
    WHERE (((IIf([Col1]<=(SELECT Max([XX].Col1) FROM Query16 AS [XX] WHERE [XX].RecCount < Query16.RecCount),"     ",[Query16].[Col1]) & "  " & IIf([Col2]<=(SELECT Max([XX].Col2) FROM Query16 AS [XX] WHERE [XX].RecCount < Query16.RecCount),"     ",[Query16].[Col2]) & "  " & IIf([Col3]<=(SELECT Max([XX].Col3) FROM Query16 AS [XX] WHERE [XX].RecCount < Query16.RecCount),"     ",[Query16].[Col3]))>"     "))
    ORDER BY Query16.RecCount, Query16.Expr1, Query16.Line;


    Build a little, test a little

    Thursday, September 10, 2015 2:09 AM

All replies

  • A single query will not do it but a report with grouping and hide duplicates will.

    Multiple queries that sort, number the records, and then eliminate the duplicates would work.

    Will a report not do it for you?


    Build a little, test a little

    Friday, September 4, 2015 4:23 PM
  • A report does not work in my case because the table will be copied over to an excel datasheet from the Access database.

    How could multiple queries could do that?

    Friday, September 4, 2015 5:01 PM
  • How could multiple queries could do that?

    First query sort and number the records. Second query with subquery to identify and eliminate the duplicate data.

    Post the SQL of your query if you need more help.


    Build a little, test a little

    Friday, September 4, 2015 5:06 PM
  • How could multiple queries could do that?

    First query sort and number the records. Second query with subquery to identify and eliminate the duplicate data.

    Post the SQL of your query if you need more help.


    Build a little, test a little


    Is that a request for me? If I know how to write it, I would not post my question in here.
    Friday, September 4, 2015 6:39 PM
  • Yes MelZZ,

    It is important for us to get a real feel for your Table relationships and the proper Field names is helpful as well as a real representation of the data types etc.


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Friday, September 4, 2015 7:41 PM

  • Is that a request for me? If I know how to write it, I would not post my question in here.
    I want you to post the SQL of your current query so it can be analyzed and provide you with suggested queries to meet your needs.

    Build a little, test a little

    Friday, September 4, 2015 8:20 PM
  • I have provided the table, sample data and desired result to you. There is only one table, 3 columns.

    I have not name the table. Let's say, "TableA".

    3 columns: Column1, Column2 and Column3.

    Column1 is the parent of Column2 and Column2 is the parent of Column3.

    Please see the desired result in my question section. Thanks.

    Friday, September 4, 2015 9:02 PM

  • Is that a request for me? If I know how to write it, I would not post my question in here.

    I want you to post the SQL of your current query so it can be analyzed and provide you with suggested queries to meet your needs.

    Build a little, test a little


    I am not using any query right now. There is only one table. Sample data provided in my question.
    Friday, September 4, 2015 9:03 PM
  • Could u please just simply import my sample data into your Access table? That's the only table I have. I have not write any query yet.
    Friday, September 4, 2015 9:06 PM
  • I have provided the table, sample data and desired result to you. There is only one table, 3 columns.

    I have not name the table. Let's say, "TableA".

    3 columns: Column1, Column2 and Column3.

    Column1 is the parent of Column2 and Column2 is the parent of Column3.

    Please see the desired result in my question section. Thanks.

    Sorry this does not make since to me to have a Parent Column in the same Table as the Child Column. The first thing I would suggest is correctly normalizing your Tables for database use rather than a spreadsheet use. If I understand you correctly you will need three Tables, 1 for each Column and set the relationships between each Table to each other as is appropriate. Only then can you get a Report to work the way you want via a query to bring the information to it.

    • Table A has data specific to Column 1
    • Table B has data specific to Column 2
    • Table C has data specific to Column 3

    Also you haven't included the data type for each set of data. If you tell us a b & c = short text, that is different if the real data is number, date and text. the queries all have to be written differently.

    Hth


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012


    • Edited by KCDW Friday, September 4, 2015 9:51 PM
    Friday, September 4, 2015 9:49 PM
  • A report does not work in my case because the table will be copied over to an excel datasheet from the Access database.

    You can export a report to an Excel file.

    Build a little, test a little

    Friday, September 4, 2015 9:55 PM
  • A simple base query to start from could be like this in three related tables

    SELECT tbl1.Col1, tbl2.Col2, tbl3.Col3
    FROM tbl1 LEFT JOIN (tbl2 LEFT JOIN tbl3 ON tbl2.[tbl3_ID] = tbl3.[ID]) ON tbl1.tbl2_ID = tbl2.ID;

    This is only to bring the data together in a query to use in the Report where you would manipulate the way it looks.

    Hth


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Friday, September 4, 2015 10:00 PM
  • I have provided the table, sample data and desired result to you. There is only one table, 3 columns.

    I have not name the table. Let's say, "TableA".

    3 columns: Column1, Column2 and Column3.

    Column1 is the parent of Column2 and Column2 is the parent of Column3.

    Please see the desired result in my question section. Thanks.

    Sorry this does not make since to me to have a Parent Column in the same Table as the Child Column. The first thing I would suggest is correctly normalizing your Tables for database use rather than a spreadsheet use. If I understand you correctly you will need three Tables, 1 for each Column and set the relationships between each Table to each other as is appropriate. Only then can you get a Report to work the way you want via a query to bring the information to it.

    • Table A has data specific to Column 1
    • Table B has data specific to Column 2
    • Table C has data specific to Column 3

    Also you haven't included the data type for each set of data. If you tell us a b & c = short text, that is different if the real data is number, date and text. the queries all have to be written differently.

    Hth


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012


    This is what I get as a single datasheet. I need to write a query for that.
    Friday, September 4, 2015 11:47 PM
  • A simple base query to start from could be like this in three related tables

    SELECT tbl1.Col1, tbl2.Col2, tbl3.Col3
    FROM tbl1 LEFT JOIN (tbl2 LEFT JOIN tbl3 ON tbl2.[tbl3_ID] = tbl3.[ID]) ON tbl1.tbl2_ID = tbl2.ID;

    This is only to bring the data together in a query to use in the Report where you would manipulate the way it looks.

    Hth


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Could you please read my question? I need 3 columns to be combined as 1 column. Your query returns 3 columns to me that is not what I asked.
    Friday, September 4, 2015 11:48 PM
  • A report does not work in my case because the table will be copied over to an excel datasheet from the Access database.

    You can export a report to an Excel file.

    Build a little, test a little

    I will need to do that. Before that I need to write a query to combine 3 columns into 1. Please see my screenshot in my question section.
    Friday, September 4, 2015 11:49 PM
  • I have provided the table, sample data and desired result to you. There is only one table, 3 columns.

    I have not name the table. Let's say, "TableA".

    3 columns: Column1, Column2 and Column3.

    Column1 is the parent of Column2 and Column2 is the parent of Column3.

    Please see the desired result in my question section. Thanks.

    Sorry this does not make since to me to have a Parent Column in the same Table as the Child Column. The first thing I would suggest is correctly normalizing your Tables for database use rather than a spreadsheet use. If I understand you correctly you will need three Tables, 1 for each Column and set the relationships between each Table to each other as is appropriate. Only then can you get a Report to work the way you want via a query to bring the information to it.

    • Table A has data specific to Column 1
    • Table B has data specific to Column 2
    • Table C has data specific to Column 3

    Also you haven't included the data type for each set of data. If you tell us a b & c = short text, that is different if the real data is number, date and text. the queries all have to be written differently.

    Hth


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012


    Or just simplify my question,

    Do you know how to write a query from the sample data I provided to you to the proposed result in my question section?

    Friday, September 4, 2015 11:51 PM
  • These two queries will do what you want --

    SELECT YourTable.[Cost Center], YourTable.Line, YourTable.Column, (SELECT Count([XX].[Cost Center] & [XX].Line & [XX].Column) FROM YourTable AS [XX] WHERE [XX].[Cost Center] <= YourTable.[Cost Center] AND [XX].[Line] <= YourTable.Line AND [XX].[Column] <= YourTable.Column) AS RecNo
    FROM YourTable
    GROUP BY YourTable.[Cost Center], YourTable.Line, YourTable.Column;

    SELECT qryRecNo.RecNo, IIf((SELECT [XX].[Cost Center] FROM qryRecNo AS [XX] WHERE [XX].[RecNo] = qryRecNo.RecNo -1)=[Cost Center],"",[Cost Center]) AS Cost_Center, IIf((SELECT [XX].[Line] FROM qryRecNo AS [XX] WHERE [XX].[RecNo] = qryRecNo.RecNo -1)=[Line],"",[Line]) AS Line_, qryRecNo.Column
    FROM qryRecNo
    ORDER BY qryRecNo.RecNo;


    Build a little, test a little

    It seems to me that this is a homework assignment based upon the insistence of using a query rather than any other method.

    Saturday, September 5, 2015 3:30 AM
  • These two queries will do what you want --

    SELECT YourTable.[Cost Center], YourTable.Line, YourTable.Column, (SELECT Count([XX].[Cost Center] & [XX].Line & [XX].Column) FROM YourTable AS [XX] WHERE [XX].[Cost Center] <= YourTable.[Cost Center] AND [XX].[Line] <= YourTable.Line AND [XX].[Column] <= YourTable.Column) AS RecNo
    FROM YourTable
    GROUP BY YourTable.[Cost Center], YourTable.Line, YourTable.Column;

    SELECT qryRecNo.RecNo, IIf((SELECT [XX].[Cost Center] FROM qryRecNo AS [XX] WHERE [XX].[RecNo] = qryRecNo.RecNo -1)=[Cost Center],"",[Cost Center]) AS Cost_Center, IIf((SELECT [XX].[Line] FROM qryRecNo AS [XX] WHERE [XX].[RecNo] = qryRecNo.RecNo -1)=[Line],"",[Line]) AS Line_, qryRecNo.Column
    FROM qryRecNo
    ORDER BY qryRecNo.RecNo;


    Build a little, test a little

    It seems to me that this is a homework assignment based upon the insistence of using a query rather than any other method.

    Thank you. This is a great query. I will save it for my future use.

    My question is to combine 3 columns into 1 with grouping order.

    I really need 1 column. Your query returns 3 columns.


    Sunday, September 6, 2015 1:55 AM
  • Just concatenate them with an IIF statement --

    IIF([RecNo] Is Null, "      ", [RecNo])  &  "  "  & IIF([Cost_Center] Is Null, "      ", [Cost_Center])  & "  " &  [Line_]


    Build a little, test a little

    Sunday, September 6, 2015 2:03 AM
  • Just concatenate them with an IIF statement --

    IIF([RecNo] Is Null, "      ", [RecNo])  &  "  "  & IIF([Cost_Center] Is Null, "      ", [Cost_Center])  & "  " &  [Line_]


    Build a little, test a little

    Let me communicate with you in this way. I have done the code in T-SQL, but I do not know how to write Jet-SQL to get the same result. I will attach my T-SQL code:

      SELECT Col FROM (
      SELECT DISTINCT Col1 AS Col
    				  ,DENSE_RANK () OVER (ORDER BY Col1) L1
    				  ,0 L2
    				  ,0 L3
      FROM [dbo].[ThreeLevel]
    
      UNION ALL
    
      SELECT DISTINCT '    '  + Col2
    				  ,DENSE_RANK () OVER (ORDER BY Col1) L1
    				  ,DENSE_RANK () OVER (ORDER BY Col2) L2
    				  ,0 L3
      FROM [dbo].[ThreeLevel]
    
      UNION ALL
    
      SELECT DISTINCT '        ' + Col3
    				  ,DENSE_RANK () OVER (ORDER BY Col1) L1
    				  ,DENSE_RANK () OVER (ORDER BY Col2) L2
    				  ,DENSE_RANK () OVER (ORDER BY Col3) L3
      FROM [dbo].[ThreeLevel]
    
      ) AS tl
      ORDER BY tl.L1, tl.L2, tl.L3 
    Do you know how to convert my T-SQL into Jet-SQL? Thanks.

    Sunday, September 6, 2015 3:15 AM
  • Just concatenate them with an IIF statement --

    IIF([RecNo] Is Null, "      ", [RecNo])  &  "  "  & IIF([Cost_Center] Is Null, "      ", [Cost_Center])  & "  " &  [Line_]


    Build a little, test a little

    Your query returns the following:

    If concatenate it using IIF, it returns this:

    I need something like this (38 rows based on my sample data, query written in T-SQL):

    Sunday, September 6, 2015 1:18 PM
  • Try this --

    IIF([RecNo] = "", "      ", [RecNo])  &  "  "  & IIF([Cost_Center] = "", "      ", [Cost_Center])  & "  " &  [Line_]


    Build a little, test a little

    Sunday, September 6, 2015 4:12 PM
  • Try this --

    IIF([RecNo] = "", "      ", [RecNo])  &  "  "  & IIF([Cost_Center] = "", "      ", [Cost_Center])  & "  " &  [Line_]


    Build a little, test a little

    Tried. This returned me Error message. In addition, if this returns a result, it should generate 28 records. I need a result of 38 rows. Please read my question and replies. Please.

    • Edited by MelZZ Sunday, September 6, 2015 5:00 PM typo
    Sunday, September 6, 2015 5:00 PM
  • Tried. This returned me Error message. In addition, if this returns a result, it should generate 28 records. I need a result of 38 rows. Please read my question and replies. Please.

    What was the error message?

    I will need to think about it some more to give you single field per output record/row.


    Build a little, test a little

    Sunday, September 6, 2015 5:38 PM
  • Tried. This returned me Error message. In addition, if this returns a result, it should generate 28 records. I need a result of 38 rows. Please read my question and replies. Please.

    What was the error message?

    I will need to think about it some more to give you single field per output record/row.


    Build a little, test a little

    SELECT qryThreeLevel.RecNo, IIf((SELECT [XX].Col1 
    FROM qryThreeLevel AS [XX] 
    WHERE [XX].[RecNo] = qryThreeLevel.RecNo -1)=Col1,"",Col1) AS Cost_Center, IIf((SELECT [XX].Col2 
    FROM qryThreeLevel AS [XX] 
    WHERE [XX].[RecNo] = qryThreeLevel.RecNo -1)=Col2,"",Col2) AS Line_, qryThreeLevel.Col3
    FROM qryThreeLevel
    ORDER BY qryThreeLevel.RecNo;

    SELECT IIf([RecNo]="","      ",[RecNo]) & "  " & IIf([Cost_Center]="","      ",[Cost_Center]) & "  " & [Line_] AS ThreeLevel
    FROM qryThreeLevel2;

    Monday, September 7, 2015 1:32 AM
  • I wrote 6 queries to get my desired result in Access. I may need your help to simplify these queries.

    Query1: Level1
    SELECT DISTINCT Col1.Col1, Col1.L1 AS Rank1, 0 AS Rank2, 0 AS Rank3
    FROM (SELECT e.Col1, c.L1, 0 AS L2, 0 AS L3 FROM ThreeLevel AS E INNER JOIN (SELECT a.Col1, COUNT(b.Col1) AS L1 FROM (SELECT e.Col1 FROM ThreeLevel AS e GROUP BY e.Col1)  AS a, (SELECT e.Col1 FROM ThreeLevel AS e GROUP BY E.Col1)  AS b WHERE a.Col1 > b.Col1 OR a.Col1 = b.Col1 GROUP BY a.Col1)  AS C ON e.Col1 = c.Col1 ORDER BY c.Col1)  AS Col1;
    
    Query2: Level2
    SELECT DISTINCT Col2.Col2, Col2.Col1, Col2.L1 AS Rank2
    FROM (SELECT e.Col2, e.Col1, c.L1 FROM ThreeLevel AS E INNER JOIN (SELECT a.Col2, a.Col1, COUNT(b.Col2) AS L1 FROM (SELECT e.Col2, e.Col1 FROM ThreeLevel AS e GROUP BY e.Col2, e.Col1)  AS a, (SELECT e.Col2, e.Col1 FROM ThreeLevel AS e GROUP BY e.Col2, e.Col1)  AS b WHERE a.Col2 >= b.Col2 AND a.Col1 >= b.Col1 GROUP BY a.Col2, a.Col1)  AS C ON e.Col2 = c.Col2 ORDER BY c.Col2, c.Col1)  AS Col2;
    
    Query3: Level2_
    SELECT '    ' & L2.Col2 AS Col2, L1.Rank1, L2.Rank2, 0 AS Rank3
    FROM Level1 AS L1 INNER JOIN Level2 AS L2 ON L1.Col1 = L2.Col1;
    
    Query4: Level3
    SELECT DISTINCT Col3.Col3, Col3.Col2, Col3.Col1, Col3.L1 AS Rank3
    FROM (SELECT e.Col3, e.Col2, e.Col1, c.L1 FROM ThreeLevel AS e INNER JOIN (SELECT a.Col3, a.Col2, a.Col1, COUNT(b.Col3) AS L1 FROM (SELECT e.Col3, e.Col2, e.Col1 FROM ThreeLevel AS e GROUP BY e.Col3, e.Col2, e.Col1)  AS a, (SELECT e.Col3, e.Col2, e.Col1 FROM ThreeLevel AS e GROUP BY e.Col3, e.Col2, e.Col1)  AS b WHERE a.Col3 >= b.Col3 AND a.Col2 >= b.Col2 AND a.Col1 >= b.Col1 GROUP BY a.Col3, a.Col2, a.Col1)  AS c ON e.Col3 = c.Col3 ORDER BY c.Col3, c.Col2, c.Col1)  AS Col3;
    
    Query5: Level3_
    SELECT '        ' & L3.Col3 AS Col3, L1.Rank1, L2.Rank2, L3.Rank3
    FROM (Level1 AS L1 INNER JOIN Level2 AS L2 ON L1.Col1 = L2.Col1) INNER JOIN Level3 AS L3 ON L2.Col2 = L3.Col2;
    
    Query6: ThreeLevelGroupingOrder
    SELECT Col1 AS Col
    FROM (SELECT * FROM Level1 AS L1
    UNION
    SELECT * FROM Level2_ AS L2
    UNION
    SELECT * FROM Level3_ AS L3
    ORDER BY Rank1, Rank2, Rank3)  AS Rank;

    Query result in Access:




    Monday, September 7, 2015 1:48 AM
  • I did it in 3 queries but it is SLOW!  --

        Query14 --

    SELECT TableA.Col1 & TableA.Col2 & TableA.Col3 AS Sort1, 1 AS Line, TableA.Col1, "     " AS Col2, "     " AS Col3
    FROM TableA
    ORDER BY TableA.Col1 & TableA.Col2 & TableA.Col3
    UNION ALL SELECT TableA.Col1 & TableA.Col2 & TableA.Col3 AS Sort1, 2 AS Line, "     ", TableA.Col2, "     "
    FROM TableA
    UNION ALL SELECT TableA.Col1 & TableA.Col2 & TableA.Col3 AS Sort1, 3 AS Line, "     ", "     ", TableA.Col3
    FROM TableA;

        Query16 --

    SELECT Query14.[Sort1] AS Expr1, Query14.Line, Query14.Col1, Query14.Col2, Query14.Col3, (SELECT Count([XX].[Line]) FROM Query14 AS [XX] WHERE [XX].[Sort1]&[XX].[Line] <= Query14.Sort1 & Query14.Line) AS RecCount
    FROM Query14
    ORDER BY Query14.[Sort1], Query14.Line;

         Query17 --

    SELECT IIf([Col1]<=(SELECT Max([XX].Col1) FROM Query16 AS [XX] WHERE [XX].RecCount < Query16.RecCount),"     ",[Query16].[Col1]) & "  " & IIf([Col2]<=(SELECT Max([XX].Col2) FROM Query16 AS [XX] WHERE [XX].RecCount < Query16.RecCount),"     ",[Query16].[Col2]) & "  " & IIf([Col3]<=(SELECT Max([XX].Col3) FROM Query16 AS [XX] WHERE [XX].RecCount < Query16.RecCount),"     ",[Query16].[Col3]) AS Col
    FROM Query16
    WHERE (((IIf([Col1]<=(SELECT Max([XX].Col1) FROM Query16 AS [XX] WHERE [XX].RecCount < Query16.RecCount),"     ",[Query16].[Col1]) & "  " & IIf([Col2]<=(SELECT Max([XX].Col2) FROM Query16 AS [XX] WHERE [XX].RecCount < Query16.RecCount),"     ",[Query16].[Col2]) & "  " & IIf([Col3]<=(SELECT Max([XX].Col3) FROM Query16 AS [XX] WHERE [XX].RecCount < Query16.RecCount),"     ",[Query16].[Col3]))>"     "))
    ORDER BY Query16.RecCount, Query16.Expr1, Query16.Line;


    Build a little, test a little

    Thursday, September 10, 2015 2:09 AM
  • Thanks. Yes. It is slow... Not sure if I can use it in my work environment.
    Monday, September 14, 2015 4:55 AM