table reconciliation and the endless loop

[CraigDolphin]

I have been tasked with coming up with a way to automate reconciliation of fish-ticket data in two databases. One database is administered by WA State Dept. Fish & Wildlife (no ability to access this myself) and the other by my employer (Lummi Nation). Both databases should contain identical information (yeah right) since each organisation gets duplicate copies of fish tickets produced by wholesale buyers whenever they buy fish/shellfish.

I can get a summary table in excel from both databases. I have imported them into access as tables, and done select queries to sort the records in ascending order by fish "ticket number" and then by date, species, weight landed, etc.

Each fish ticket number may have more than on record in the table (e.g., a fisherman might catch 200lbs of Chinook and 100 lbs of Coho salmon and both are reported on the same fish ticket.

So, there are three problems I need to be looking for in each table: 1-tickets that are recorded in one db but not the other, 2-records from a ticket that have no matching record in the other database (eg., 4 records in the tribal db and 5 records in the state db for the same ticket number), and 3-when parallel records do exist, are there any significant discrepancies in the recorded amounts of some of the fields for that record?

The tables are quite large (~14,500 records for one year of data) and need to be reconciled reasonably frequently during fisheries to avoid going over quota. The scope and time-consuming nature of this task if done manually has meant that, in practise, no reconciliation has been attempted in 4 years!

I have had a crack at using vba to do this task but without much success. One approach I tried weemed to be working on some sample tables (~300 records) but at about halfway through the process it kept coming up with a no current record error that I couldn't track down. I think it was a result of too much recursive activity between the functions.

I redesigned my vba approach to try to do more with do loops rather than calling functions but now I'm stuck in a loop and was wondering if someone might help me spot my problem?

What I am trying to do with this script is:
make 2 recordsets based on the ordered queries of the state and tribal tables
start at the beginning of each recordset
check the current ticket number against a table of unpaired ticket numbers (built from queries beforehand)
if they do not appear then we can exclude error type 1 and compare the actual ticket numbers.
if the numbers do not match, then we likely have an error of type 2. The record with the lower ticket number has to be exported to a table, and we move to the next record in that recordset and restart the pairwise comparison of ticket numbers.
If the ticket numbers do match, then we compare the data in each field for a type 3 error. If all is the same then we move on to the next record in each recordset and start over. If not, then we export the disagreeing records to suitable tables, and move to the next record in each recordset and start the comparison over again.
If we come to the end of a recordset, then the remaining records in the other trecordset (if any) are to be exported to the appropriate table.

I've attached a copy of the db. The code is linked to a command button on a form that should open at startup. The db is in Access 2000 format. I've tried to comment the code to explain what I'm trying to do.

So, after that long-winded explanation, I'd greatly appreciate any help that may be offered. I'm relatively new to vba and doubtless am making some error(s) that I just can't see.

 

[KeithG]

you can use joins and relationships in queries to produce your results. I will look at your sample and make some examples.

 

[KeithG]

There are four example quires I have made for you

1. Shows tickets from State that don't exist in Tribal
2. Shows tickets from Tribal that don't exist in State
3. Shows tickets from Tribal and State that have inconsistent FishID
4. Shows tickets from Tribal and State that have inconsistent AreaNo

Do you understand how I have made these? You could use VBA but SQL will be much easier. You are getting your "No current Record" error probably because either you have step pasted the last record in the table or because there are no records in the table.

 

[CraigDolphin]

thanks Keith. I will check your example before I make any further comment

Cheers,
Craig

 

[CraigDolphin]

Keith, thanks again for taking the time to help.

Question: Since we have to manually look at filed tickets to check our 'discrepancy data', it makes sense to produce a single table showing ticket number and field-information showing where the discrepancies exist. That way, we can check all the questionable fields at the same time when we look at the ticket.

The approach I was aiming for was to produce both tables in an excel spreadsheet, and use conditional formatting to highlight where the two tables disagree. These tables would show only those records where discrepancies occur (thus winnowing out the thousands of 'good' records' and saving weeks of pointless work). Your method would allow me to produce a table of ticket numbers and discrepancies for each field in the original table.

Can I take the information from these multiple queries, and build a single table from it?

Cheers

 

[KeithG]

You could make an append query to append the descrepensys to a table. What I would do is make a table with every ticket number in it an make a field in this table for every field that you need to verify accuracy and make it a boolean (Yes/No) type. Then you can make update queries to update the checkboxes of the tickets that have discrepencies on them. I also wanted to mention that you can also use conditonal formatting in a report in Access.