Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2004
    Posts
    66

    Red face Unanswered: Query Too Complex

    I'm really hoping someone can help me. I'm a bit of an Excel wizard, but am still learning access. I'm struggling with a query that was easy to handle just as calculations in Excel, but isn't possible in Access. Here is the sql behind the query...

    SELECT Debtor.SSN, Debtor.LastName, Debtor.FirstName, Check.Amount, Check.Interest, Check.RCF, [Amount]+[Interest]+[RCF] AS Prin, Check.AttorneyFee, Check.StoreID, Check.Track, CtInfo.Case, CtInfo.SFee, CtInfo.SFeePd, CtInfo.SPSFee, CtInfo.SPSFeePd, CtInfo.GFee, CtInfo.GFeePd, CtInfo.GPSFee, CtInfo.GarnPSFeePd, CtInfo.IPSFee, CtInfo.IPSFeePd, CtInfo.OFee, CtInfo.OFeePd, PCROSS.[1], PCROSS.[3], IIf([SFeePd]=1,Nz([SFee]),0)+IIf([GFeePd]=1,Nz([GFee]),0)+IIf([SPSFeePd]=1,Nz([SPSFee]),0)+IIf([GarnPSFeePd]=1,Nz([GPSFee]),0)+IIf([IPSFeePd]=1,Nz([IPSFee]),0)+IIf([OFeePd]=1,Nz([OFee]),0) AS Client, IIf([SFeePd]=2,Nz([SFee]),0)+IIf([GFeePd]=2,Nz([GFee]),0)+IIf([SPSFeePd]=2,Nz([SPSFee]),0)+IIf([GarnPSFeePd]=2,Nz([GPSFee]),0)+IIf([IPSFeePd]=2,Nz([IPSFee]),0)+IIf([OFeePd]=2,Nz([OFee]),0) AS AttyFee, IIf(Nz([3])=0,0,IIf((Nz([3])<(Nz([AttyFee]))),Nz([3]),(Nz([AttyFee])))) AS PAttyFee, IIf(Nz([3])=0,0,IIf((Nz([3])-Nz([PAttyFee]))<Nz([Client]),(Nz([3])-Nz([PAttyFee])),Nz([Client]))) AS PClient, IIf(Nz([3])=0,0,IIf(((Nz([3])-(Nz([PAttyFee])+Nz([PClient])))/2)<Nz([Prin]),((Nz([3])-(Nz([PAttyFee])+Nz([PClient])))/2),Nz([Prin]))) AS PPrin, IIf(Nz([3])=0,0,(Nz([3])-(Nz([PAttyFee])+Nz([PClient])+Nz([PPRIN])))) AS PATTY, IIf(Nz([1])=0,0,(IIf((Nz([1])<(Nz([AttyFee])-Nz([PAttyFee]))),Nz([1]),(Nz([AttyFee])-Nz([PAttyFee]))))) AS CAttyFee, IIf(Nz([1])=0,0,IIf((Nz([1])-Nz([CAttyFee]))<(Nz([Client])-Nz([PClient])),(Nz([1])-Nz([CAttyFee])),(Nz([Client])-Nz([PClient])))) AS CClient, IIf(Nz([1])=0,0,IIf(((Nz([1])-(Nz([CAttyFee])+Nz([CClient])))/2)<(Nz([Prin])-Nz([PPrin])),((Nz([1])-(Nz([CAttyFee])+Nz([CClient])))/2),Nz([Prin])-Nz([PPrin]))) AS CPrin, Nz([1])-(Nz([CAttyFee])+Nz([CClient])+Nz([Cprin])) AS New
    FROM Debtor RIGHT JOIN (([Check] INNER JOIN PCROSS ON Check.TransactionID = PCROSS.TransactionID) INNER JOIN CtInfo ON Check.TransactionID = CtInfo.TransactionID) ON Debtor.DebtorInfoID = Check.DebtorInformationID;

    I read that it's best to shorten all the names cause this error may be caused by too many characters...but that hasn't seemed to really help.

    I can get the query to run as is, but it won't as soon as I add another field (either in the query or in the report for which this query is needed)...I am trying to get it to then add three of these newly calculated fields, but it won't seem to.

    Any experts out there willing to help me out?

    Thanks a ton!

    Susan

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,418
    Provided Answers: 7
    BREAK it down

    you can run Query that read other query


    Table -> Query1 -> Query2 ->Query3 -> TheAnswer
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    Apr 2004
    Posts
    66
    Tried that - wrote a query for the first part (pulling all the fields together), then ran equations in the second query with those fields...still get the exact same error at the same point...Am I missing the point here?

  4. #4
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,418
    Provided Answers: 7
    sh..t it must be complex LOL


    Table ->Query1 ->MakeTable1 = newtable

    Newtable -> Query2 -> Query3 -> Maketable2 = Newtable2

    Newtable2 -> Query4 -> Report


    Then Wrote a Macro

    Setwarning off
    run MakeTable1
    run Maketable2
    setwarning on
    print Report
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  5. #5
    Join Date
    Apr 2004
    Posts
    66
    First - I have to say - thank you so much for being willing to help me - I can't tell you how much I appreciate it.

    And no...it's really not that complex...just a bunch of if statements! But...I have to do something different, so....

    okay - I clearly missed the point of query to a query to a query - I didn't realize that I should do that make table thing...I'll try that (never done it before!!) and see if that works!

    Thanks a ton!

    Susan

  6. #6
    Join Date
    Jan 2004
    Posts
    492
    Wow - that code is very hard to read....

    I know Access writes the code for you, but since you seem eager to learn, I'll give you some invaluable advice. Though as a disclaimer, it seems that Access loves to overwrite the SQL you write with their own, so save often!

    This is a sample of how Access writes code for you. They always use full table names everywhere. They also allow for spaces in table and table names, as long as you put the name in brackets [ ]. My advice to you (and it looks like you are already doing this) is to either make all the columns/tables one word, or use an underscore. The less brackets and parentheses in your query, the easier it is to read. SQL really does not need parentheses around most statements for it to work.

    Code:
    Select full_table_name1.col1, full_table_name2.col1
    from full_table_name1, full_table_name2
    where full_table_name1.id = full_table_name2.id
    Instead, you can use aliases. An alias represents the table name, and can be used throughout the entire query. Here is that same query using aliases.

    Code:
    Select f1.col1, f2.col1
    from full_table_name1 f1, full_table_name2 f2
    where f1.id = f2.id
    Simply put the alias after the table name with a space (no commas). This will tell SQL that you are going to refer to the table using that alias. When you are selecting many columns as you are in your query, this will cut down the amount of code you have yet does the exact same thing.

    Also note that I dont use the inner join syntax, but the join in the where statement is the same concept. For me, its a lot less coding - I dont see the point in adding any unnecessary junk in my queries....but that is a matter of my own preference.

    Hope this gives you some ideas!

  7. #7
    Join Date
    Apr 2004
    Posts
    66
    Thanks for the pointers - I am very eager to learn and frustrated by the lack of good coding tips in books. I'll try using alias too!

    Susan

  8. #8
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,418
    Provided Answers: 7
    Originally posted by mommyof4
    First - I have to say - thank you so much for being willing to help me - I can't tell you how much I appreciate it.

    And no...it's really not that complex...just a bunch of if statements! But...I have to do something different, so....

    okay - I clearly missed the point of query to a query to a query - I didn't realize that I should do that make table thing...I'll try that (never done it before!!) and see if that works!

    Thanks a ton!

    Susan
    hay that "I didn't realize that I should do that make table thing..."
    it what I would do who say thats right as long as the print out are printed

    What I've done in the pass

    eg wrote a database to selected events in our work place

    got it talking to our payroll system got it to query and do a maketable of hours (good its in access can play with it know)

    then i could put hour/payroll => to a Event that happen on site base on that emp bla bla


    i broke the problem down into small query / sometime had to do a maketable then query them again get answer

    then just wrote a macro to run the Queries in the right order and then
    print the .... report

    i've joined

    AS400 data =>access
    ourpayroll => access

    so there I got As400 => Ourpayroll

    just have to get the joins right
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  9. #9
    Join Date
    Oct 2003
    Posts
    706
    I feel that this query is simply ... incomprehensible.

    You just can't really do queries that have iif() function-calls like that. Not do so and ever hope to maintain what you did.

    I strongly advise you to rewrite the query, using more than one query (perhaps a macro?) and design it from the outset to be maintainable. Which simply means that you have a ghost of a chance of understanding it tomorrow.

    Understand, please, that I'm not saying anything personal whatsoever about you or anyone: it's simply strong advice. You can box yourself into "maintenance hell."
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  10. #10
    Join Date
    Apr 2004
    Posts
    66
    Hello all,

    Thank you to everyone who responded so quickly. I am grateful for all the advice (and no, I don't take it personnally at all - I know I have a lot to learn and am lucky to have people willing to share their thoughts!)

    I was able to make this all work by taking StePHan's advice - much, much thanks for clarifying it all for me!

    I'm also using alias's now, and thinking more about maintence!

    With much thanks!

    Susan

Posting Permissions

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