1. Registered User
Join Date
Jun 2002
Posts
5

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. Registered User
Join Date
Feb 2002
Posts
403
How is the figure calculated? What is the formula?

3. Registered User
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. Registered User
Join Date
Feb 2002
Posts
403
So how does the user tell Access how to include what in the sum?

5. Registered User
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. Registered User
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. Registered User
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. Registered User
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. Registered User
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. Registered User
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
•