Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Unanswered: Bugger of an update problem ...

    Hi all.

    This one is a hummer ... Preface: Access 2000.

    I have a Billing table that has to be modded to split out regular purchases from expense reports ... No problem.

    Problem: I have a form to modify existing expense entries (reg misc expenses and expense reports) that I've got to upgrade ...

    Dilemma: If you change a work order then the purchase/expense amt has to be reassigned. If the amt changes the ammt has to be updated. If the Expense Report flag gets changed the amt has to move from one column to another ... Any good ideas on an precedence algorithm for doing this?

    Any sympathy?

    - Mike

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    sympathy you have -- an excellent topic title. why should all those lovely words go to waste!

    but as you & i (amongst others) often comment here, perhaps a teeny tiny bit of detail about the data structures..........

    from your post, the question/answer seems well tied-up in the table design.

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Originally posted by izyrider
    sympathy you have -- an excellent topic title. why should all those lovely words go to waste!

    but as you & i (amongst others) often comment here, perhaps a teeny tiny bit of detail about the data structures..........

    from your post, the question/answer seems well tied-up in the table design.

    izy
    Izy,

    Actually data structure. The table structure is something like:

    WorkOrderNumber Text 8
    Regular Hours Double 8

    OT Hours Double 8
    DT Hours Double 8
    Regular Hours Cost Currency 8

    OT Hours Cost Currency 8
    DT Hours Cost Currency 8
    Purchases Currency 8
    Expenses Currency 8


    The Purchases column and the Expenses Column are accumulators for each work order. When changes are made to expense records that dump into those columns well that is the crux of my problem ... There are many decision trees needed to cover all possible "change bases" ...

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    *BUMP*

    This is not a database design issue ... This is a logic issue where I now have 3 competing criteria that influence each other ...

    Case in point: If I change the work order then I have to subtract the original amount from the original WO and add the new amount to the new WO. The caveat being WHICH column do I update? Why, the column that is defined by the Expense Report Flag ... That begs the question of which column do I subtract from initially? And the rhetorical answer is: Why, the column that is defined by the Expense Report Flag ... Decision tree decision tree decision tree ...

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    since you are talking to yourself, i'll join you.

    can you do it a different way: instead of editing the existing record (with all the logic headaches) can you add a couple of new records: one a reversal of the previous "transaction" and the other a new "transaction"


    izy
    currently using SS 2008R2

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Originally posted by izyrider
    since you are talking to yourself, i'll join you.

    can you do it a different way: instead of editing the existing record (with all the logic headaches) can you add a couple of new records: one a reversal of the previous "transaction" and the other a new "transaction"


    izy
    Izy,

    Yes I could do that but, the purpose of this form is to correct the actual record itself ... The other purpose of this is that the expense record appears on a cost report and I don't want to add correction records that will also appear ... If a customer were to see the report they would be wondering if we know what we're doing ...

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    i obviously don't know all your details, but accepting that you dont want error/reversal/new as records, can it work to try something similar in memory - see what the net consequences are, and update your original record based on that?



    good luck, izy
    currently using SS 2008R2

  8. #8
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Izy,

    That is what I'll have to do ... It get's complicated because there is a dependency table that holds 2 accumulator columns that have to be updated when either reassigning the work order or expense flag or changing the amount ... That is what I was talking about ... I just have to break it down into its component parts and generate the appropriate code ...

    Pseudo-code:

    If work order has changed then
    if expense report then subtract out amt from old expense column
    else subtract out amt from old purchases column
    if expense report then add to new expense column the amt
    else add to new purchases column the amt
    elseif amt has changed then
    if expense report then subtract out old amt from expense column
    else subtract out old amt from purchases column
    if expense report then add new amt to expense column
    else add new amt to purchases column
    endif

    This is essentially what I was looking for ...

Posting Permissions

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