Results 1 to 5 of 5
  1. #1
    Join Date
    May 2003
    Posts
    70

    Unanswered: Query too complex

    Select tblTable.Field, tblTable2.Field2, tblTable2.Field3 AS S ...... ORDER BY Switch(S = 'b1',0,S = 'b2',1,S = 'b3',2,S = 'b4',3,S = 'b5',4,S = 'b6',5,S = 'b7',6,S = 'b8',7,S = 'b9',8,S = 'b10',9,S = 'b11',10,S = 'b12',11,S = 'b13',12,S = 'b14',13,S = 'b15',14,S = 'b16',15,S = 'b17',16,S = 'b18',17,S = 'b19',18,S = 'b20',19,S = 'b21',20,S = 'b22',21,S = 'b23',22,S = 'b24',23,S = 'b25',24,S = 'b26',25,S = 'b27',26,S = 'b28',27,S = 'b29',28,S = 'b30',29,S = 'b31',30,S = 'b32',31,S = 'b33',32,S = 'b34',33,S = 'b35',34,S = 'b36',35,S = 'b37',36,S = 'b38',37,S = 'b39',38,S = 'b40',39,S = 'b41',40,S = 'b42',41,S = 'b43',42,S = 'b44',43,S = 'b45',44,S = 'b46',45,S = 'b47',46,S = 'b48',47,S = 'b49',48,S = 'b50',49,S = 'b51',50,S = 'b52
    ',51,S = 'b53',52,S = 'b54',53,S = 'b55',54,S = 'b56',55,S = 'b57',56,S = 'b58',57,S = 'b59',58,S = 'b60',59,S = 'b61',60,S = 'b62',61,S = 'b63',62,S = 'b64',63,S = 'b65',64,S = 'b66',65,S = 'b67',66,S = 'b68',67,S = 'b69',68,S = 'b70',69,S = 'b71',70,S = 'b72',71,S = 'b73',72,S = 'b74',73,S = 'b75',74,S = 'b76',75,S = 'b77',76,S = 'b78',77,S = 'b79',78,S = 'b80',79,S = 'b81',80,S = 'b82',81,S = 'b83',82,S = 'b84',83,S = 'b85',84,S = 'b86',85,S = 'b87',86,S = 'b88',87,S = 'b89',88,S = 'b90',89,S = 'b91',90,S = 'b92',91,S = 'b93',92,S = 'b94',93,S = 'b95',94,S = 'b96',95,S = 'b97',96,S = 'b98',97,S = 'b99',98,S = '
    a1',99,S = 'a2',100,S = 'a3',101,S = 'a4',102,S = 'a5',103,S = 'a6',104,S = 'a7',105,S = 'a8',106,S = 'a9',107,S = 'a10',108,S = 'a11',109,S = 'a12',110,S = 'a13',111,S = 'a14',112,S = 'a15',113,S = 'a16',114,S = 'a17',115,S = 'a18',116,S = 'a19',117,S = 'a20',118,S = 'a21',119,S = 'a22',120,S = 'a23',121,S = 'a24',122,S = 'a25',123,S = 'a26',124,S = 'a27',125,S = 'a28',126,S = 'a29',127,S = 'a30',128,S = 'a31',129,S = 'a32',130,S = 'a33',131,S = 'a34',132,S = 'a35',133,S = 'a36',134,S = 'a37',135,S = 'a38',136,S = 'a39',137,S = 'a40',138,S = 'a41',139,S = 'a42',140,S = 'a43',141,S = 'a44',142,S = 'a45',143,S = 'a46',144,S = 'a47',145,S = 'a48',146,S = 'a49',147,S = 'a50',148,S = 'a51',149,S = 'a52',150,S = 'a53',151,S = 'a54',152,S = 'a55',153,S = 'a56',154,S = 'a57',155,S = 'a58',156,S = 'a59',157,S = 'a60',158,S = 'a61',159,S = 'a62',160,S = 'a63',161,S = 'a64',162,S = 'a65',163,S = 'a66',164,S = 'a67',165,S = 'a68',166,S = 'a69',167,S = 'a70',168,S = 'a71',169,S = 'a72',170,S = 'a73',171,S = 'a74',172,S
    = 'a75',173,S = 'a76',174,S = 'a77',175,S = 'a78',176,S = 'a79',177,S = 'a80',178,S = 'a81',179,S = 'a82',180,S = 'a83',181,S = 'a84',182,S = 'a85',183,S = 'a86',184,S = 'a87',185,S = 'a88',186,S = 'a89',187,S = 'a90',188,S = 'a91',189,S = 'a92',190,S = 'a93',191,S = 'a94',192,S = 'a95',193,S = 'a96',194,S = 'a97',195,S = 'a98',196,S = 'a99',197)ASC


    I receive a query too complex error when trying to run this. I need to be able to allow the user to specify their custom sort and then apply this SQL statemnt as a rowsource for a report. However, with this, nothing shows up and when I try to run this as a query I get the Query too complex error.

    Thanks,

  2. #2
    Join Date
    Nov 2003
    Posts
    267
    Do they really need to beable to sort by every field possible. You may need to seriously re-evaluate that thought. Aside from the fact that if access is saying it is to complex, it won't do it.


    As a work around. Breakup your query and reports. You may need to have 3 or four report/queries working in the background. Based on the option they select, display the report that allows that sorting.

    S-

  3. #3
    Join Date
    May 2003
    Posts
    70
    It's not that simple. I've tried other approaches. They have to do it like that.

    Thanks,

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    take all that code and put it into another table, then do a join and bob's your uncle

    Select tblTable.Field, tblTable2.Field2, tblTable2.Field3 AS S
    ... from ... , switchtable
    ... where ... tblTable2.Field3 = switchtable.id
    ... ORDER BY switchtable.sortkey


    rudy
    http://r937.com/

  5. #5
    Join Date
    Nov 2003
    Posts
    267
    You other alternative with to create the query in Code. Creat the basic select statement and then add on the sorting criterias that are needed, based on what the select.

    For example

    sSQL = "Select * form Table1 Order BY "

    If s = "B0" then

    sSQL = sSQL & "Field1"

    ENdif

    ....


    sSQL - SSQL & " ASC"

    FInal sSQL = Select & From Table1 Order By Field1 ASC

    then you will pass this query directly to the report OR overwrite the query that the report is linked to.

    Good Luck

    S-

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •