none
Query access 2003 Error message: Query is to complex ? RRS feed

  • Question

  • When I add the [LNRONGNAM] field to the new error message: "Query is to complex", if I do not add this field [LNRONGNAM], the query runs fine.

    LNRONGNAM: IIf(Not IsNull([LNRONG]) And Not IsNull([BSSX]),[LNRONG]+[BSSX],IIf(IsNull([LNRONG]) And Not IsNull([BSSX]),[BSSX],[LNRONG]))
    [LNRONGNAM]=[LNRONG]+[BSSX]

    This is my faulty question, can repairs help me ?

    SELECT HDMUABAN.CHINHANH, Sum(IIf([LOAIHD]='MUA' Or [LOAIHD]='KLE',[SOLUONG]/1000)) AS TONGSLMUA, IIf(Not IsNull([TONGSLMUA]) And Not IsNull([SLMUATM]),[TONGSLMUA]-[SLMUATM],IIf(IsNull([TONGSLMUA]) And Not IsNull([SLMUATM]),[SLMUATM],[TONGSLMUA])) AS SLMUASX, Sum(IIf(([LOAIHD]='MUA' Or [LOAIHD]='KLE') And Not IsNull([PAKD]) And IsNull([CHEBIEN]),[SOLUONG]/1000)) AS SLMUATM, Sum(IIf([LOAIHD]='KLE' And IsNull([PAKD]),[SOLUONG]/1000)) AS MUANKSX1, Sum(IIf(([LOAIHD]='MUA' And IsNull([PAKD])) Or ([LOAIHD]='BAN' And Not IsNull([CHEBIEN])),[SOLUONG]/1000)) AS MUANKSX2, IIf(Not IsNull([MUANKSX1]) And Not IsNull([MUANKSX2]),[MUANKSX1]+[MUANKSX2],IIf(IsNull([MUANKSX1]) And Not IsNull([MUANKSX2]),[MUANKSX2],[MUANKSX1])) AS MUANKSX, Sum(IIf([LOAIHD]='BAN' Or [LOAIHD]='BAN2',[SOLUONG]/1000)) AS TONGSLBAN, IIf(Not IsNull([TONGSLBAN]) And Not IsNull([SLBANTM]),[TONGSLBAN]-[SLBANTM],IIf(IsNull([TONGSLBAN]) And Not IsNull([SLBANTM]),[SLBANTM],[TONGSLBAN])) AS SLBANSX, Sum(IIf(([LOAIHD]='BAN' Or [LOAIHD]='BAN2') And Not IsNull([PAKD]) And IsNull([CHEBIEN]),[SOLUONG]/1000)) AS SLBANTM, Sum(IIf(([LOAIHD]='BAN' Or [LOAIHD]='BSFG') And Not IsNull([PAKD]),[LAIGOP]/1000000)) AS LAIGOPSX1, Sum(IIf([LOAIHD]='BAN' And Not IsNull([PAKD]),[SOLUONG]/1000)) AS LAIGOPSXSL1, Sum(IIf(([LOAIHD]='MUA' And IsNull([PAKD]) And IsNull([BUHAOHUT])) Or ([LOAIHD]='KLE' And IsNull([PAKD]) And IsNull([BUHAOHUT])),([SOLUONG]/1000000)*400)) AS LAIGOPSX2, Sum(IIf(([LOAIHD]='MUA' And IsNull([PAKD]) And IsNull([BUHAOHUT])) Or ([LOAIHD]='KLE' And IsNull([PAKD]) And IsNull([BUHAOHUT])),[SOLUONG]/1000)) AS LAIGOPSXSL2, IIf(Not IsNull([LAIGOPSXSL1]) And Not IsNull([LAIGOPCBSL]),[LAIGOPSXSL1]-[LAIGOPCBSL],IIf(IsNull([LAIGOPSXSL1]) And Not IsNull([LAIGOPCBSL]),[LAIGOPCBSL],[LAIGOPSXSL1])) AS LAIGOPSX3SL, IIf(Not IsNull([LAIGOPSX1]) And Not IsNull([LAIGOPCB]),[LAIGOPSX1]-[LAIGOPCB],IIf(IsNull([LAIGOPSX1]) And Not IsNull([LAIGOPCB]),[LAIGOPCB],[LAIGOPSX1])) AS LAIGOPSX3, Sum(IIf([LOAIHD]='BAN' And Not IsNull([PAKD]) And IsNull([CHEBIEN]),[SOLUONG]/1000000)) AS LAIGOPCBSL, Sum(IIf(([LOAIHD]='BAN' Or [LOAIHD]='BSFG') And Not IsNull([PAKD]) And IsNull([CHEBIEN]),[LAIGOP]/1000000)) AS LAIGOPCB, IIf(Not IsNull([LAIGOPSX2]) And Not IsNull([LAIGOPSX3]),[LAIGOPSX2]+[LAIGOPSX3],IIf(IsNull([LAIGOPSX2]) And Not IsNull([LAIGOPSX3]),[LAIGOPSX3],[LAIGOPSX2])) AS LAIGOPSX, Sum(IIf([LOAIHD]='BSLGT',[LAIGOPDTCP]/1000000)) AS LAIGOPSX4, IIf(Not IsNull([LAIGOPSX]) And Not IsNull([LAIGOPCB]),[LAIGOPSX]+[LAIGOPCB],IIf(IsNull([LAIGOPSX]) And Not IsNull([LAIGOPCB]),[LAIGOPCB],[LAIGOPSX])) AS TLAIGOP, Sum(IIf(([LOAIHD]='MUA' And IsNull([PAKD]) And Not IsNull([BUHAOHUT])) Or ([LOAIHD]='KLE' And IsNull([PAKD]) And Not IsNull([BUHAOHUT])),[SOLUONG]/1000)) AS SLBUHAOHUT, Sum(IIf([LOAIHD]='BAN' And Not IsNull([PAKD]),[CHIPHI]/1000000)) AS CPLAIGOPSX1, Sum(IIf([LOAIHD]='BAN' And Not IsNull([PAKD]) And IsNull([CHEBIEN]),[CHIPHI]/1000000)) AS CPLAIGOPCB, IIf(Not IsNull([CPLAIGOPSX1]) And Not IsNull([CPLAIGOPCB]),[CPLAIGOPSX1]-[CPLAIGOPCB],IIf(IsNull([CPLAIGOPSX1]) And Not IsNull([CPLAIGOPCB]),[CPLAIGOPCB],[CPLAIGOPSX1])) AS CPLAIGOPSX, Sum(IIf([LOAIHD]='BAN' And Not IsNull([PAKD]),[CHIPHI]/1000000)) AS TCPLAIGOP, IIf(Not IsNull([LAIGOPSX]) And Not IsNull([CPLAIGOPSX]),[LAIGOPSX]-[CPLAIGOPSX],IIf(IsNull([LAIGOPSX]) And Not IsNull([CPLAIGOPSX]),[CPLAIGOPSX],[LAIGOPSX])) AS LOINHUANSX, IIf(Not IsNull([LAIGOPCB]) And Not IsNull([CPLAIGOPCB]),[LAIGOPCB]-[CPLAIGOPCB],IIf(IsNull([LAIGOPCB]) And Not IsNull([CPLAIGOPCB]),[CPLAIGOPCB],[LAIGOPCB])) AS LOINHUANTM, IIf(Not IsNull([TLAIGOP]) And Not IsNull([TCPLAIGOP]),[TLAIGOP]-[TCPLAIGOP],IIf(IsNull([TLAIGOP]) And Not IsNull([TCPLAIGOP]),[TCPLAIGOP],[TLAIGOP])) AS TLOINHUAN, IIf(Not IsNull([SLMUASX]),([SLMUASX]*4)/100) AS TRUHAOHUT, IIf(Not IsNull([TLOINHUAN]) And Not IsNull([BSSXT]),[TLOINHUAN]+[BSSXT],IIf(IsNull([TLOINHUAN]) And Not IsNull([BSSXT]),[BSSXT],[TLOINHUAN])) AS TLOINHUANN, Sum(IIf([LOAIHD]='BSSXT',[LAIGOPDTCP]/1000000)) AS BSSXT, Sum(IIf([LOAIHD]='BSFG',[LAIGOPDTCP]/1000000)) AS BSFG, Sum(IIf([LOAIHD]='BSFGT',[LAIGOPDTCP]/1000000)) AS BSFGT, Sum(IIf([LOAIHD]='CPQL',[CHIPHI]/1000000)) AS CPQL, Sum(IIf([LOAIHD]='KHLV',[CHIPHI]/1000000)) AS KHLV, IIf(Not IsNull([CPQL]) And Not IsNull([KHLV]),[CPQL]+[KHLV],IIf(IsNull([CPQL]) And Not IsNull([KHLV]),[KHLV],[CPQL])) AS TCPQL, IIf(Not IsNull([TLOINHUAN]) And Not IsNull([TCPQL]),[TLOINHUAN]-[TCPQL],IIf(IsNull([TLOINHUAN]) And Not IsNull([TCPQL]),[TCPQL],[TLOINHUAN])) AS LNRONG, First(IIf(Not IsNull([LNRONG]) And Not IsNull([BSSX]),[LNRONG]+[BSSX],IIf(IsNull([LNRONG]) And Not IsNull([BSSX]),[BSSX],[LNRONG]))) AS LNRONGNAM
    FROM HDMUABAN
    WHERE (((HDMUABAN.NGAYKY)>=#2/1/2017# And (HDMUABAN.NGAYKY)<=#2/28/2017#))
    GROUP BY HDMUABAN.CHINHANH
    HAVING (((HDMUABAN.CHINHANH)='GIA LAI'));

    Tuesday, March 7, 2017 6:34 AM

All replies

  • > can repairs help me?

    No, the query is too complex, which means the statement exceeds the parser's capacity. What can very likely help is redesigning your database. You seem to be having a "spreadsheet table" with many fields allowed to be null, not a "tall and skinny" relational table. I cannot read your language but if you translate this query to English and tell us more about the overall purpose of your database we may be able to give you some guidance.


    -Tom. Microsoft Access MVP

    Tuesday, March 7, 2017 2:06 PM
  • My query has a single table and when calculating the first field data will take the first field for the second field and the same for the following fields, so when I count the last field, I get the error on, What information do you need ?
    Wednesday, March 8, 2017 1:46 AM
  • > What information do you need ?

    I already told you: I cannot read your language but if you translate this query to English and tell us more about the overall purpose of your database we may be able to give you some guidance.


    -Tom. Microsoft Access MVP

    Wednesday, March 8, 2017 2:09 AM
  • Hi lamtriendong,

    you can see that you have a very long query that doing so many calculations.

    when you add another field in this query then it looks like , it is out of capacity of Access Database.

    you need to remove the unnecessary fields and calculations from your query.

    you had mentioned that you have only one table.

    if possible then try to normalize your database.

    create the relationship between tables.

    and then try to fetch the necessary details from that tables.

    it will work more fast and efficiently.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, March 8, 2017 5:14 AM
    Moderator
  • I will send my article for you to see, I want to calculate profit dragon = [LNRONGNAM],  in the Access file there are 2 query name: qryCandoi_True (not added [LNRONGNAM] field) and qryCandoi_Err (added [LNRONGNAM] field). you see the attachment http://www.mediafire.com/file/sps8l7ral12i80p/data.rar

    LNRONGNAM: IIf(Not IsNull([LNRONG]) And Not IsNull([BSSXT]),[LNRONG]+[BSSXT],IIf(IsNull([LNRONG]) And Not IsNull([BSSXT]),[BSSXT],[LNRONG]))
    [LNRONGNAM]=[LNRONG]+[BSSXT]

    Thursday, March 9, 2017 2:56 AM
  • Hi lamtriendong,

    we already suggested you a suggestion to solve this issue is to remove some conditions.

    it is doing so many calculations that is above the capacity of the access database.

    so we cant do anything in this issue.

    this is the limit of Access and if you cross the limit then it will not work.

    so you have to make your query simpler then before.

    the best solution is to normalize your database.

    your database contains only one table and you put everything in that one table.

    so looks like it did not designed properly.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, March 9, 2017 7:46 AM
    Moderator
  • I would try using the nz function to simplify your logic.  Try the formula below:

    LNRONGNAM: nz([LNRONG],0) + nz([BSSX],0)

    The nz function will return the first parameter if not null, or the second parameter if the first is null.

    Also, your HAVING criteria seems like it could be moved to the WHERE clause to be more efficient..  In the SQL designer, create a another column for that field and set it to "Where" and put the criteria there, instead of in the same column as a grouping column, which I think is what you are doing.



    I was also looking at:

    Sum(IIf(([LOAIHD]='MUA' And IsNull([PAKD]) And IsNull([BUHAOHUT])) Or ([LOAIHD]='KLE' And IsNull([PAKD]) And IsNull([BUHAOHUT])),([SOLUONG]/1000000)*400)) AS LAIGOPSX2

    I don't see the false part of the IIf statement.  Instead, you might try something like (I think the logic is equivalent(?) :

    IIf([LOAIHD] in ("MUA","KLE") and [PAKD] is null and [HUHAOHUT] is null, ([SOLUONG]/1000000)*400, 0)

    Thursday, March 9, 2017 2:04 PM
  • 1. My data is designed in many tables, but I'm criticizing the table for error messages to send to you.

    2. I tried replacing statement 1 with statement 2 but still got error: "Query is to complex"
    statement 1:
    [CODE]
    LNRONGNAM: IIf(Not IsNull([LNRONG]) And Not IsNull([BSSXT]),[LNRONG]+[BSSXT],IIf(IsNull([LNRONG]) And Not IsNull([BSSXT]),[BSSXT],[LNRONG]))
    [/CODE]

    statement 2:
    [CODE]
    LNRONGNAM: nz([LNRONG],0) + nz([BSSXT],0)
    [/CODE]

    3. I've simply made this command run well
    [CODE]
    LAIGOPSX2: Sum(IIf(([LOAIHD]='MUA' And IsNull([PAKD]) And IsNull([BUHAOHUT])) Or ([LOAIHD]='KLE' And IsNull([PAKD]) And IsNull([BUHAOHUT])),([SOLUONG]/1000000)*400)) 

    As =
    LAIGOPSX2: Sum(IIf(([LOAIHD] in ("MUA","KLE") And IsNull([PAKD]) And IsNull([BUHAOHUT])),([SOLUONG]/1000000)*400)) 
    [/CODE]

    4. I will learn and simplify the conditional statement, can you help me simplify the command above ?
    Friday, March 10, 2017 3:02 AM
  • Hi lamtriendong,

    if still you are getting the "Query is too complex" error then I think you can try to use "Union" in you query.

    so you can divide your query in several parts.

    which make query simpler.

    so at the end you will have several small queries and you can connect that all the queries with "Union" so that you will get output of all queries together.

    so it looks like you are only executing one query to get whole result.

    this approach may solve your issue.

    generally we use "Union" to combine result from multiple tables but here in this case you can try to divide the query for the same table and try to execute it without error.

    example of Union:

    SELECT name, price, warranty_available, exclusive_offer
    FROM Products
    UNION ALL
    SELECT name, price, guarantee_available, exclusive_offer
    FROM Services;

    Reference:

    union query

    UNION Operation (Microsoft Access SQL)

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, March 13, 2017 4:22 AM
    Moderator
  • At the suggestion of you rewritten, I have not yet thought of your new spelling of the referral sent to me, you can rewrite the new way with you my sample data has sent you
    Wednesday, March 22, 2017 2:53 AM
  • Hi lamtriendong,

    you had mentioned that,"At the suggestion of you rewritten, I have not yet thought of your new spelling of the referral sent to me, you can rewrite the new way with you my sample data has sent you".

    did you try to implement my last suggestion?

    if yes, is your issue solved with that?

    if no, please try to implement it and then let me know about the results.

    from your last reply , it looks like you want me to modify your query.

    you need to modify it by yourself, we can only provide you suggestion to solve the issue.

    you need to implement that suggestion to test it whether it can solve your issue or not.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, March 23, 2017 5:39 AM
    Moderator
  • One thing I see that will give you an error is the order in which you create an alias and then use it. 

    Example is TONGSLMUA.

    SELECT HDMUABAN.CHINHANH, Sum(IIf([LOAIHD]='MUA' Or [LOAIHD]='KLE',[SOLUONG]/1000)) AS TONGSLMUA, IIf(Not IsNull([TONGSLMUA]) And Not IsNull([SLMUATM]),[TONGSLMUA]-[SLMUATM],IIf(IsNull([TONGSLMUA]) And Not IsNull([SLMUATM]),[SLMUATM],[TONGSLMUA])) AS SLMUASX,

    I generally find that Access processes a query from bottom up of the SQL.  The alias must be created before it can be used.  Access will be trying to make calculations/logic decisions using TONGSLMUA before it is created in the SQL statement.

    The same for SLMUATM and probably other aliases.


    Build a little, test a little

    Thursday, March 23, 2017 6:10 PM