Results 1 to 12 of 12
  1. #1
    Join Date
    Nov 2010
    Posts
    8

    Unanswered: Work around a formula that is too long

    I am trying to do a long expression in a query that will then be used by a report to output a printed sheet. But the formula is fairly lengthy and exceeds the (what I consider pretty crappy) limitation. How can I get around this? I'm assuming I need to use VBA to run the expression but how do I make this 'link' in to the form?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Depends what the formula is. Wanna show and tell?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Nov 2010
    Posts
    8
    Quote Originally Posted by pootle flump View Post
    Depends what the formula is. Wanna show and tell?
    Pleased to mate, I just didn't want to scare people off with the length of the formula, as it's not really complex in the least, just long.

    Qty: IIf([Forms]![frmChooseAProductForOrder]![opgComplete]=1,
    [Forms]![frmChooseAProductForOrder]![AOrders]*[tblMainTable]![QtyPerA]+
    [Forms]![frmChooseAProductForOrder]![BOrders]*[tblMainTable]![QtyPerB]+
    [Forms]![frmChooseAProductForOrder]![COrders]*[tblMainTable]![QtyPerC]+
    [Forms]![frmChooseAProductForOrder]![DOrders]*[tblMainTable]![QtyPerD]+
    [Forms]![frmChooseAProductForOrder]![EOrders]*[tblMainTable]![QtyPerE]+
    [Forms]![frmChooseAProductForOrder]![FOrders]*[tblMainTable]![QtyPerF]+
    [Forms]![frmChooseAProductForOrder]![GOrders]*[tblMainTable]![QtyPerG]+
    [Forms]![frmChooseAProductForOrder]![HOrders]*[tblMainTable]![QtyPerH]+
    [Forms]![frmChooseAProductForOrder]![IOrders]*[tblMainTable]![QtyPerI]+
    [Forms]![frmChooseAProductForOrder]![JOrders]*[tblMainTable]![QJtyPer],
    IIf([Forms]![frmChooseAProductForOrder]![opgComplete]=2,IIf([tblMainTable]![S/T?]="Trim",
    [Forms]![frmChooseAProductForOrder]![AOrders]*[tblMainTable]![QtyPerA]+
    [Forms]![frmChooseAProductForOrder]![BOrders]*[tblMainTable]![QtyPerB]+
    [Forms]![frmChooseAProductForOrder]![COrders]*[tblMainTable]![QtyPerC]+
    [Forms]![frmChooseAProductForOrder]![DOrders]*[tblMainTable]![QtyPerD]+
    [Forms]![frmChooseAProductForOrder]![EOrders]*[tblMainTable]![QtyPerE]+
    [Forms]![frmChooseAProductForOrder]![FOrders]*[tblMainTable]![QtyPerF]+
    [Forms]![frmChooseAProductForOrder]![GOrders]*[tblMainTable]![QtyPerG]+
    [Forms]![frmChooseAProductForOrder]![HOrders]*[tblMainTable]![QtyPerH]+
    [Forms]![frmChooseAProductForOrder]![IOrders]*[tblMainTable]![QtyPerI]+
    [Forms]![frmChooseAProductForOrder]![JOrders]*[tblMainTable]![QtyPerJ],
    IIf([Forms]![frmChooseAProductForOrder]![opgComplete]=3,IIf([tblMainTable]![S/T?]="Seats",
    [Forms]![frmChooseAProductForOrder]![AOrders]*[tblMainTable]![QtyPerA]+
    [Forms]![frmChooseAProductForOrder]![BOrders]*[tblMainTable]![QtyPerB]+
    [Forms]![frmChooseAProductForOrder]![COrders]*[tblMainTable]![QtyPerC]+
    [Forms]![frmChooseAProductForOrder]![DOrders]*[tblMainTable]![QtyPerD]+
    [Forms]![frmChooseAProductForOrder]![EOrders]*[tblMainTable]![QtyPerE]+
    [Forms]![frmChooseAProductForOrder]![FOrders]*[tblMainTable]![QtyPerF]+
    [Forms]![frmChooseAProductForOrder]![GOrders]*[tblMainTable]![QtyPerG]+
    [Forms]![frmChooseAProductForOrder]![HOrders]*[tblMainTable]![QtyPerH]+
    [Forms]![frmChooseAProductForOrder]![IOrders]*[tblMainTable]![QtyPerI]+
    [Forms]![frmChooseAProductForOrder]![JOrders]*[tblMainTable]![QtyPerJ],0),0))),0))

    It is looking to see if the settees ordered are Complete (seats and trim parts) or seats only or trim parts only. Then it multiplies the amount of each of the 10 variations of each product by the amount ordered. This same multiplication is done 3 times on the Iif statement of which option is selected in the opgComplete option group.

    In reality a very simple calculation... just very long.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    This looks like the root of the problem might be a repeating groups problem
    First normal form - Wikipedia, the free encyclopedia
    Usually calculations like this, especially when simple sums, are due to a design flaw. SQL does calculations against rows very well, but not against columns because there is very rarely a need.

    Assuming you don't want to discuss the design of your table (no one ever seems to want to) then I would recommend using four columns to do this. Put the three calculations in three columns and put the Iif in a fourth column, referencing the column that contains the correct calculation.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Nov 2010
    Posts
    8
    I have no aversion to discussing my table design but to skip that right this second, I did try what you suggest with the 4 columns and it did work perfectly, except for one very bizarre hurdle I couldn't fathom.

    It asked for the first 3 columns as if they were parameters. However, it didn't actually use what I put in as a 'parameter', it did the calculation exactly correctly. UNLESS you put a zero or no answer in as the 'parameter' in which case you got no results.

    So yes, your idea should be the solution in theory but I don;t know how to avoid these non existent parameter boxes appearing.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Can you post your SQL in full?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    there is a finite limit for how long your SQL can be in Access JET. there is also a limit on columns in a query (IIRC its 250)
    if you find you are hitting this problem do it as a series of linked queries which have a one to one join

    top level query
    select Q1.acolumn,Q1.bcolumn,Q1.column,Q1.dcolumn, q2.ecolumn,q2.fcolumn,q2.golumn,q2.hcolumn from Q1
    join Q2 on Q2.thiscolumn=Q1.thatcolumn
    sub queries
    Q1: select acolumn,bcolumn,column,dcolumn from atable
    Q2: select ecolumn,fcolumn,golumn,hcolumn from atable
    bear in mind you can shorten the length of the SQL by using the as clause

    eg
    select a,b,c,d from myincrediblylongnamedtable
    select t1.a,t1.b,t1.c,t1.d from myincrediblylongnamedtable as t1
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Nov 2010
    Posts
    8
    Pootle Flump: to paste here the SQL I recreated it that way (because I'd been trying to find an alternative way as I was having trouble), but whenever I close the Append Query it removes the 3 columns that are doing the expressions that the Qty field adds up and appends to the tblOrders. Here is what it is:

    Complete: IIf([Forms]![frmChooseAProductForOrder]![opgComplete]=1,[Forms]![frmChooseAProductForOrder]![AOrders]*[tblMainTable]![QtyPerA]+[Forms]![frmChooseAProductForOrder]![BOrders]*[tblMainTable]![QtyPerB]+[Forms]![frmChooseAProductForOrder]![COrders]*[tblMainTable]![QtyPerC]+[Forms]![frmChooseAProductForOrder]![DOrders]*[tblMainTable]![QtyPerD]+[Forms]![frmChooseAProductForOrder]![EOrders]*[tblMainTable]![QtyPerE]+[Forms]![frmChooseAProductForOrder]![FOrders]*[tblMainTable]![QtyPerF]+[Forms]![frmChooseAProductForOrder]![GOrders]*[tblMainTable]![QtyPerG]+[Forms]![frmChooseAProductForOrder]![HOrders]*[tblMainTable]![QtyPerH]+[Forms]![frmChooseAProductForOrder]![IOrders]*[tblMainTable]![QtyPerI]+[Forms]![frmChooseAProductForOrder]![JOrders]*[tblMainTable]![QtyPerJ],0)

    Trim: IIf([Forms]![frmChooseAProductForOrder]![opgComplete]=2,IIf([tblMainTable]![S/T?]="Trim",[Forms]![frmChooseAProductForOrder]![AOrders]*[tblMainTable]![QtyPerA]+[Forms]![frmChooseAProductForOrder]![BOrders]*[tblMainTable]![QtyPerB]+[Forms]![frmChooseAProductForOrder]![COrders]*[tblMainTable]![QtyPerC]+[Forms]![frmChooseAProductForOrder]![DOrders]*[tblMainTable]![QtyPerD]+[Forms]![frmChooseAProductForOrder]![EOrders]*[tblMainTable]![QtyPerE]+[Forms]![frmChooseAProductForOrder]![FOrders]*[tblMainTable]![QtyPerF]+[Forms]![frmChooseAProductForOrder]![GOrders]*[tblMainTable]![QtyPerG]+[Forms]![frmChooseAProductForOrder]![HOrders]*[tblMainTable]![QtyPerH]+[Forms]![frmChooseAProductForOrder]![IOrders]*[tblMainTable]![QtyPerI]+[Forms]![frmChooseAProductForOrder]![JOrders]*[tblMainTable]![QtyPerJ],0),0)

    Seats: IIf([Forms]![frmChooseAProductForOrder]![opgComplete]=3,IIf([tblMainTable]![S/T?]="Seats",[Forms]![frmChooseAProductForOrder]![AOrders]*[tblMainTable]![QtyPerA]+[Forms]![frmChooseAProductForOrder]![BOrders]*[tblMainTable]![QtyPerB]+[Forms]![frmChooseAProductForOrder]![COrders]*[tblMainTable]![QtyPerC]+[Forms]![frmChooseAProductForOrder]![DOrders]*[tblMainTable]![QtyPerD]+[Forms]![frmChooseAProductForOrder]![EOrders]*[tblMainTable]![QtyPerE]+[Forms]![frmChooseAProductForOrder]![FOrders]*[tblMainTable]![QtyPerF]+[Forms]![frmChooseAProductForOrder]![GOrders]*[tblMainTable]![QtyPerG]+[Forms]![frmChooseAProductForOrder]![HOrders]*[tblMainTable]![QtyPerH]+[Forms]![frmChooseAProductForOrder]![IOrders]*[tblMainTable]![QtyPerI]+[Forms]![frmChooseAProductForOrder]![JOrders]*[tblMainTable]![QtyPerJ],0),0)

    Then
    Qty: [Complete]+[Trim]+[Seats]

    But it is now asking me for Complete, Trim and Seats as parameters, rather than using the results from the expressions above and concatenating what I put in the parameter boxes, not even adding them. ie if i put 5 in Complete parameter box, 6 in Trim and 7 in Seats it puts the results in Qty field as 567!

    Where am I going wrong?

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Do you know how to get in to SQL view? In 2007, leftmost icon of Results in Design ribbon. Go to SQL View then copy and paste it all here. The design view is a propriety Access thing that doesn't translate well to the forum.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I don't think you get that kind of immediate alias availability in jet-sql...

    If you insist on moving forward with this design (which will continue to cause you massive headaches), you will probably have to wrap you're calculated values up in to their own query that can be called by subsequent queries wishing to use your field aliases.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  11. #11
    Join Date
    Sep 2006
    Posts
    265
    Martin,

    What I would suggest if this is related to a question relating to another forum, the form in 1.jpg could be treated as an entry point and then update a table with each Product against each Order. In effect you have a non-normalised entry form with normalised data because then you can do a simple sum(Order * Qty).

    Simon

  12. #12
    Join Date
    Nov 2010
    Posts
    8
    Ok, I'm on the brink of losing my mind (and job) on this now, because what should clearly (to me) be working, isn't. I am getting ridiculous, unexplainable problems with my queries whereby it returns different results depending on what other products are on the form, even though each query looks at different boxes in the form, the only universal one being the customer and the order number.

    So... I have started again from scratch, doing my tables the way you suggest. What I don't understand though is how to set the relationships up.

    I've attached a picture of how it's set up so far but I don't get how this Quantities Per Variation should work (so it is probly wrong on the pic)

    - Each Product has multiple Items. (eg a settee has seats, arms, platforms etc)
    - Each Product has multiple Variations. (eg a settee can be 2seater, 3 seater etc)
    - Each Product will have a different set of Variations to the next. (eg one settee might have a chair, 2seater, 2½seater and 3seater, where another has a 2seater, 3seater, bed and corner unit)
    - Each Item needs a Quantity in each of those Variations. (eg a 2seater has 2 seats, 2 arms, 1 platform; a 3 seater has 3 of those same seats, 2 of those same arms and 1 platform which is different to the 2seater, ie some Items are on more than one Variation, some are unique to one Variation and the quantity can differ)

    So should the Products be related to these Variations or the Items?

    Extra info: My old setup had 10 Variations on each Item (where it had less than 10 the extras were all 0s) and each Product stored the name of those Variations (where it had less than 10 the extras were all empty). The form updates when you choose a Product with the Variation names next to the text box for entering the quantity ordered, which it then multiplied by the quantity of each Item on that Variation.
    It ran append queries for each product separately (the form displayed a possible 9 products for an order) then a select query collated all the items in the table it appended to that had the same order number.

Posting Permissions

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