Results 1 to 12 of 12
  1. #1
    moorecurls is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Posts
    24

    Query to evaluate list of amounts on invoice that match a total

    I need to develop a query that will evaluate a list of numbers. The below screen attached is in excel with the Invoice Total Amt, the Invoice number, and the line items of each transaction in the Amount column. The highlighted yellow Amounts are the totals that match the Invoice Total Amt. I need a query that will find these amounts that match the Invoice Total Amt. Does anyone have any suggestions on what type of query will work for this? Thanks.
    Attached Thumbnails Attached Thumbnails Invoice data.PNG  

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    don't think there is such a query. Taking your first invoice 1355BXC01, you have 4 amounts, of which two match the total. Without any other data the only way you could do this is with some sort of function to take the first amount, add the second, if over the invoice amount, reverse the addition and add the next item, if under add the following items until over or equals. If over, reverse and start from the 2nd item. If under either match what you have or ignore. But even that will fail if the figures required are the first and last amounts. It can probably be done using a recursive function of some sort but I really wouldn't want to get involved with trying to work out how that might work. I would google something like 'account reconciliation algorithms' to see if you can find some code you can adapt

    I'd be more inclined to look at how these amounts have been booked to an invoice in the first place and prevent it happening going forward.

  3. #3
    JSR is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2018
    Location
    Reno, NV
    Posts
    41
    I don't understand why some rows are or are not in yellow. I don't see them matching anything. You also have a lot of duplicates, like the first invoice with four rows where there's four numbers but no data associated with it.

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    like the first invoice with four rows where there's four numbers
    I've assumed the OP has split the data into two column sets so columns F-I should be below columns A-D
    I don't understand why some rows are or are not in yellow.
    they are the unmatched amounts.

    So first invoice 1355BXC01, total value 2500.96, 4 individual amounts, the last two (highlighted yellow) add up to the total amount, the other two remain unmatched. Not shown in the example, but don't know how you would choose which amount if the same i.e. in the first invoice, if the first row was 344.96 same as 3rd row not 345.60

  5. #5
    JSR is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2018
    Location
    Reno, NV
    Posts
    41
    Okay, I see it now.

    This could done, but it has to be done in VBA due to the complexity.

    With n entries, there are 2^n different ways to pick entries to be included or not. For the first invoice, n is 4, so there are 2^4=16 different combinations that the code would need to examine, sum and see if it matches the invoice total.

    With the last invoice, there are 32 entries. 2^32 is 4,294,967,296. There must be a better way!

    How did you determine which entries to highlight in yellow? I'm sure you didn't cycle through 4 billion combinations to come up with that.

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    How did you determine which entries to highlight in yellow?
    I'm guessing manual trial and error (human brain is still more powerful than a computer!), and/or there is other data the OP is not sharing. For example, the invoice total must have been created from individual invoice lines. You can then match on invoice line on an amount by amount basis, perhaps leaving a rump to be reviewed manually.

    Lots of questions before a more detailed answer can be given. Like why are there negative values on an invoice? Shouldn't they be on a credit note? And if they are to cancel out a payment, on the invoice on the right why is line 3 not matched against the line above, or the last line. Assuming these are invoices and payments then as matched, the implication is that Invoice Total Amt is actually a payment, not an invoice total - which would the sum of the Amount column.

    OP needs to be much clearer about the requirement

  7. #7
    mcucino is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Jul 2017
    Location
    Providence, RI
    Posts
    74
    I'm curious how the extraneous entries came to be. Should they not be there, or do they represent a different type of data such as internal cost? I agree with whoever suggested tackling the problem from the front end - either collect more data so you know what should be applied to the invoice or prevent the extraneous entries from being submitted. I'm curious to see where this goes. I do a lot of financial reconciliation databases and I have dealt with similar dilemmas so I may be able to help when we have more information.

  8. #8
    moorecurls is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Posts
    24
    Thanks for the idea of the account reconciliation algorithms, I'll look into this. The data comes in an html file that is parsed out by field names and yes there is well over 500k lines of data per month with all of the different gl descriptions of each dr and cr. The wish of front end tackle of this problem isn't an option although trust me, I wish it was...but then I won't get better at writing queries. I'm just starting to develop VBA but have a good foundation so any ideas in that direction I will run with. One thing I forgot to mention is there is a unique id that runs most of my current queries. Many of the invoices match exactly, so that query was easy to write. I'm running about 50 access queries on this data already that reduces the invoices that are unmatched to about 40k lines of data, I'd like to find away to evaluate via a query or VBA. There is always only one match for the many numbers in the amount column, however there might be several dr and cr that need to be included in the matched amounts if they balance out to zero plus the actual amount that matches. What I'm thinking of doing is dividing out the dr and cr into different tables. Then finding all of the invoices that have already been matched and with those just finding the dr and cr that would balance to zero. I'm just looking for any ideas or suggestions on ways to find matches on these amount to invoice totals.

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you need to be clear about your reconciliation rules - does a cr that matches a dr take precendence over a cr to be included in the match.

    I've recently completed a reconciliation application for a client - around 500k records per month. The 'easy' match only accounted for around 40% of transactions, but by bringing data in from other sources, applying string matching techniques etc, I got it up to 99% - still left some 5000 records unmatched tho. If the only data you have is what you have shown us then I don't think there is much you can do

  10. #10
    moorecurls is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Posts
    24
    The recon is simple the first group of numbers in the amount column that matches the invoice total would be the match. Any extra numbers that balance to zero also need to be included in the match to make sure we have as many of the id's that are unique transaction in the month recon as possible.

    I'm not interested in purchasing any kind of application for recon, but thanks for the offer. I'll get this solved soon I'm sure.

  11. #11
    JSR is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2018
    Location
    Reno, NV
    Posts
    41
    In order for a computer to figure this out, it's still going to be 2^n calculations, which seems excessive. Isn't there some way to determine which transactions are included in the total and which aren't? How did you determine which lines to highlight in yellow?

  12. #12
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    since you have already excluded the easy matches you need more data otherwise as JSR says you could be into a massive number of iterations. But if you are going ahead as is then suggest limit your testing to invoices with no more than perhaps 5 or 6 rows until you have it right otherwise could take forever.

    also need to be included in the match to make sure we have as many of the id's
    well there is some data you have not displayed - where is the ID? Without it, I would say it would be impossible to do in Access.

    To model it out in excel, pivot your data to go horizontal

    InvTotal..Invoice..Amt1..Amt2..Amt3..Amt4.….

    then in the following columns put a calculation for each of the possible combinations - for 4 Amounts that would be 16 columns. 5 would be 32 columns, 6 amounts 64 columns. Excel has some 16k columns, so you could get to model around 12 amounts.

    and then a final column (or perhaps use conditional formatting) to identify which column(s - there could be more than 1) matches the invoice total. The formula in the identified column tells you which amounts are reconciled

    Any extra numbers that balance to zero
    would be a reason for having multiple columns identified as matching - so your identifier column would need to choose the column with the most columns used in the calculation

    You can perhaps exclude some rows where the sum of the amounts is less that the inv total (allowing for negatives) so will never match. Also rows with single amounts which are greater than the InvTotal

    Suggest use VBA to populate these calculated columns with the calculation and the identifier column calculation - it will give you feel for what a vba routine will need to look like in Access, and for the identifier column you could perhaps display the identified calculated column calculation rather than the value.

    Once you have a working model you can look to apply it to Access. Remember Access is limited to 256 columns i.e. the max it will handle horizontally is 7 amounts. So you would need to 'reorientate' the calculations vertically to handle a greater number.

    Good luck

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Sub total and total on invoice form
    By knh2r in forum Access
    Replies: 3
    Last Post: 07-13-2016, 02:09 PM
  2. Combobox list does not match query
    By tcox in forum Access
    Replies: 5
    Last Post: 11-20-2015, 10:41 AM
  3. Replies: 12
    Last Post: 04-20-2015, 06:44 PM
  4. Replies: 8
    Last Post: 04-29-2013, 12:11 PM
  5. Replies: 4
    Last Post: 10-04-2012, 11:17 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums