Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2002
    Posts
    5

    Unanswered: Backwards calculations??

    I'm attempting to create a db to work out which lines of figures that make up an end figure.

    I've got the rows of numbers, but need to a db to identify the right combination of lines that add up to the correct end figure (I also need to have a tolerance of +-5 units).

    At the moment, I'm maually using a spreadsheet which is taking ages! So any help or ideas would be greatly appreciated!

    Thanks, Newby#2

  2. #2
    Join Date
    Feb 2002
    Posts
    403
    How is the figure calculated? What is the formula?

  3. #3
    Join Date
    Jun 2002
    Posts
    5
    Hi, thanks for replying!

    There is no formula as yet, in my spreadsheet I have an end figure, and I'm just cutting and pasting (!) until I get close...

    I thought I might be able to have a yes/no flag in a database that could try the values and populate a flag to say whether or not each line is included... The main problem is how I get access to try different values...

    For example: Say I have a 31 days of information, each day could have 5 lines/numbers, some of which are included in the total for the month, some may not. I need to identify by using the month total which lines are included in the sum.

    ...make any sense?

  4. #4
    Join Date
    Feb 2002
    Posts
    403
    So how does the user tell Access how to include what in the sum?

  5. #5
    Join Date
    Jun 2002
    Posts
    5
    The user would have a figure for the end of a month, and lots of lines that could potentially make that up...

    For each calculation I would like to have access sum the seperate lines, and calculate the difference between this and the month end figure. Hopefully, it would then somehow work out which lines make up the difference in order to exclude them.

    (By exclude them, I mean flag them up, so that I can ascertain the correct combination of lines that make up the sum).

    So the user would basically have to import the lines of data, input the target month end figure, click a button and let access identify the right lines...

  6. #6
    Join Date
    Feb 2002
    Posts
    403
    So we could have, in simple terms

    21/2 500
    25/2 500
    29/2 500

    Month total 1000

    How does Access know which dates apply to this total?

  7. #7
    Join Date
    Jun 2002
    Posts
    5
    ok, you'd have a months worth of data in the target figure.

    at least one number for each day, there may be more (which is where the excluding lines bit comes in).

    So access would know that it has 31 days, but maybe 41 lines... in which case 10 lines should be excluded. To identify these it would have to trial and error the number until they match.

    example would be:

    1/2/01 - 10
    1/2/01 - 2
    1/2/01 - 0
    etc...

    Say that it was like this for the whole month, and the total/target was 310. Access would exclude the lines for each day where the values are 0, and 2.

  8. #8
    Join Date
    Feb 2002
    Posts
    403
    So in other words intially you would like Access to go through and total the highest values and see if they total equal to a target value.

    What happens when these do not total to the target value, how does Access know which records to maintain in the sum?

    EG:

    21/6 100
    21/6 50
    21/6 20
    22/6 200
    22/6 20
    22/6 40

    Monthly Total 90

    How does Access know to try target value 2 with target value 6.

  9. #9
    Join Date
    Jun 2002
    Posts
    5
    This is exactly the problem!

    In effect, access will have to run some sort of IF query... IF certain values = target value OK, if not try all combinations... How exactly I get it to do this is a challenge to say the least!

    I can afford to have a small tolerance, rounding will mean that I will never get an exact match, but the best combination is what I'm after.

    There can only/will always be ONE line (in the final sum) from each day, the problem is working out which line was used to get the target figure.

  10. #10
    Join Date
    Feb 2002
    Posts
    403
    The point I am trying to make is being missed.

    What is unique about record 2 and 6 which means they contribute to the equation whilst 1,3,4, and 5 have nothing to do with it.

Posting Permissions

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