Efficiently searching multiple tables (1 Viewer)

Hi everyone, this is a great site and it has been very helpful, but I'm not sure I am tackling my problem the right way. I'd appreciate any insights.

I have a database of insurance payments which is separated by object type(advisor, Policy, Payment, Customer etc). I import a .csv file into a temporary table which is effectively a list of payments. I now want to find a way to make sure that each customer, policy, and advisor mentioned in this temporary table is also in my main database, and put them in if they are not.

After this is done, I want to put each payment into the payment table, using the primary keys of the Advisor, Policy etc tables.

How would you, the wise access programmers of the world, tackle this. I am currently looping through many times in vba, but it seems to take an age, and that is only for the duplicate checking.

Thanks for any help

Looping via VBA is most often bound to be much slower than running some SQL that is optimised by the machinery itself.

One suggestion:

Make and run an update (oops, I meant INSERT ) query for each item found in your temp table but not in the table of interest, ie customer, policy, and advisor

Then make an update (again, INSERT)query that locates the right payment record based on the payment data in the temp table and the keys found in your tables based on the customer, policy, and advisor in the temp table. This would work best if all these items were indexed. A bit more complex but more efficient solution would be to add columns to your temptable and store the keys corresponding to each customer, policy, and advisor

One tricky bit is verification of you input in terms of apparent duplicates - misspelled names that aren't new but just misspelled. I guess the need for this depends on how much control there is over the input to your .csv

Sorry for the beginner question but how can I stop an insert query putting in duplicates?

Use an Outer Join on the match fields with the Source table as the "Show All Records" side of the join and Is Null as the criteria on the destination version of field.

if what Galaxion just said is not clear, then try the "unmatched" query wizard - this will show you how to find the items that are already on file, and those that aren't

bascially. you need to get together a set of queries, for each different record type

a) add new items to your tables (based on unmatched query) and maybe
b) update existing items

you can actually do a) and then update all items, if it's easier - same result obviously.

once you have all this worked out, you can put the whole procedure onto a single button click, incliding the csv selection and import.

I'm a bit lost here sorry, what does the Is Null do? I've used the wizard and some modifying and come up with this which seems to work:

Null means there is no value in the field.

The LEFT JOIN shows all the records in the left table so any records with no match in the right table will have Null in the join fields.

Sorry to bring this back, but why does this code allow duplicates? Can it not check the records it has just put in? It will not allow duplicates if the entry is already in the table, but will import the same thing twice.