none
Access - Database for Banking Reconcilliation RRS feed

  • Question

  • Hi Everyone,

    I am creating an access database to assist us with bank reconciliation on a mass scale. We currently have two tables, one based on the payments accounted for on our corporate end and the other showing the payments accounted for on our end.

    Step 1: We are trying to find a way to have the system go through both tables and match the CHECK NUMBERS, AMOUNT PAID, INVOICE PAID, and CORP INVOICE ID. If these 4 fields match on both tables we would like it to auto select the checkbox, representing that these two rows match, and auto populate a unique ID# that is the same on both tables to show which two lines link to each other. Is this possible?? Also, with the payments received on our end, the AMOUNT PAID might include penalties and interest fees. What we did is include the amount paid w/o the late fees; so when we do the match, if the first AMOUNT PAID is not a match we then need it to look at the AMOUNT W/O PI field and see if those amounts match. If so continue with checking the check box and leaving the unique ID#.

    Step 2: After the system has gone through and matched as many rows as possible, now we will take the remaining rows and try to manually match these. I have created a form for both tables that we would like to base off a query that shows all the rows where the checkbox is unchecked. Next we created another form showing both subforms so that we can try to match each line up manually. Our goal is to be able to identify more matches by manually selecting the check box on both forms and clicking a button that makes these two match by adding the unique ID#.

    I have attached a screen shot of the form with sample data to explain what we are trying to do. If anyone has helpful tips on how I should approach this, it would be gratefully appreciated.

    Wednesday, May 30, 2018 1:32 PM

All replies

  • There is no screenshot. 

    Groeten, Peter http://access.xps350.com/

    Wednesday, May 30, 2018 6:21 PM
  • If you are able to join the 2 tables such that matching records join correctly - - that is in affect your unique key, even if it involves multiple fields - though a bit of a hassle to work with.

    I would set up an autonumber field in 1 table - - that will then generate integers for every row.... and a regular number field in the second table .... and then for the joined records write the autonumber from T1 over to the number field of T2

    As to the non joins - - those should be evident in a no-match query.... and then I suppose someone must manually do the write of the key value as appropriate.

    This is an unusual and somewhat complicated situation that is going to take some finesse....

    Thursday, May 31, 2018 11:40 AM
  • Sorry...It appears I cannot attach anything until my account is verified. I can email you the screenshots if you would like?
    Thursday, May 31, 2018 12:57 PM
  • That might work! Is there any code I can write that matches the 4 fields and it they are a true match, take the autonumber from table one and copy that into the field for the second table?
    Thursday, May 31, 2018 1:03 PM
  • make your 4 join lines using query design view.  no code needed.  add the autonumber & number fields before making the query so that these fields are included....

    assuming 1 table as autonumber values then copy & modify the query (remove all unrelated fields to keep things simple) and reoption to be instead an Update Query where the auto field is writing into the number field...

     - - if it won't let you due to non-updateable query you may have to write off the record set to a temp table....

    Thursday, May 31, 2018 1:45 PM
  • I am sorry but I am not fully following. Is there a way I can email you the screen shots of what we have currently set up?
    Thursday, May 31, 2018 3:20 PM
  • If you are not familiar with Query Design View and making join lines between tables....be sure to do a little online/video tutorial in that topic as it is a very core feature that you'll need to have a handle on.....

    Thursday, May 31, 2018 3:34 PM
  • That is OK. See http://access.xps350.com/ for the address.

    Groeten, Peter http://access.xps350.com/

    Friday, June 1, 2018 10:46 AM
  • An update query to do step 1 could look like:

    UPDATE T1 INNER JOIN T2 ON (T1.CORP_INVOICE_ID = T2.CORP_INVOICE_ID) AND (T1.[INVOICE PA] = T2.[INVOICE PA]) AND (T1.[AMOUNT PAID] = T2.[AMOUNT PAID]) AND (T1.CHECK_NUMBER = T2.CHECK_NUMBER) SET T2.ID_OF_T1 = [ID];

    Or in design mode:

    The second step can be simular. In that case you only match unmatched records. So make 2 queries to select those records from T1 an T2 and make an update query like this one with thode 2 queries.


    Groeten, Peter http://access.xps350.com/

    Friday, June 1, 2018 11:14 AM
  • It seems my picure dos not show up either.

    Groeten, Peter http://access.xps350.com/

    Friday, June 1, 2018 11:16 AM