Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-19-2011, 01:30 AM   #1
NZArchie
Newly Registered User
 
Join Date: May 2011
Location: Christchurch, New Zealand
Posts: 84
Thanks: 3
Thanked 1 Time in 1 Post
NZArchie is on a distinguished road
Efficiently searching multiple tables

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

NZArchie is offline   Reply With Quote
Old 07-19-2011, 01:48 AM   #2
spikepl
Eledittingent Beliped
 
spikepl's Avatar
 
Join Date: Nov 2010
Location: San Serriffe
Posts: 6,144
Thanks: 77
Thanked 980 Times in 958 Posts
spikepl is a jewel in the rough spikepl is a jewel in the rough spikepl is a jewel in the rough
Re: Efficiently searching multiple tables

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

Last edited by spikepl; 07-19-2011 at 02:19 AM.
spikepl is offline   Reply With Quote
The Following User Says Thank You to spikepl For This Useful Post:
NZArchie (07-29-2011)
Old 08-01-2011, 07:23 PM   #3
NZArchie
Newly Registered User
 
Join Date: May 2011
Location: Christchurch, New Zealand
Posts: 84
Thanks: 3
Thanked 1 Time in 1 Post
NZArchie is on a distinguished road
Re: Efficiently searching multiple tables

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

NZArchie is offline   Reply With Quote
Old 08-01-2011, 08:56 PM   #4
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,638
Thanks: 95
Thanked 1,498 Times in 1,413 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Efficiently searching multiple tables

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.
Galaxiom is offline   Reply With Quote
Old 08-01-2011, 09:25 PM   #5
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,791
Thanks: 55
Thanked 1,027 Times in 993 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: Efficiently searching multiple tables

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.
__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the bottom left of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
Old 08-03-2011, 01:24 AM   #6
NZArchie
Newly Registered User
 
Join Date: May 2011
Location: Christchurch, New Zealand
Posts: 84
Thanks: 3
Thanked 1 Time in 1 Post
NZArchie is on a distinguished road
Re: Efficiently searching multiple tables

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:

Code:
INSERT INTO Advisors
SELECT [TempTable-SovImport].AgentName, [TempTable-SovImport].AdvisorLastName
FROM [TempTable-SovImport] LEFT JOIN Advisors ON ([TempTable-SovImport].[AdvisorLastName] = Advisors.[LastName]) AND ([TempTable-SovImport].[AgentName] = Advisors.[FirstName])
WHERE (((Advisors.FirstName) Is Null));
NZArchie is offline   Reply With Quote
Old 08-03-2011, 03:26 PM   #7
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,638
Thanks: 95
Thanked 1,498 Times in 1,413 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Efficiently searching multiple tables

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.

Galaxiom is offline   Reply With Quote
Old 08-07-2011, 10:05 PM   #8
NZArchie
Newly Registered User
 
Join Date: May 2011
Location: Christchurch, New Zealand
Posts: 84
Thanks: 3
Thanked 1 Time in 1 Post
NZArchie is on a distinguished road
Re: Efficiently searching multiple tables

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.

Code:
strSql = "DELETE Advisors.AdvisorID " & vbCrLf & _
"FROM Advisors " & vbCrLf & _
"WHERE (((Advisors.AdvisorID) Not In (SELECT First(Advisors.AdvisorID) AS FirstOfAdvisorID " & vbCrLf & _
"FROM Advisors " & vbCrLf & _
"GROUP BY Advisors.FirstName, Advisors.LastName;)));"

NZArchie is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Query searching multiple tables' field names (not values) russi General 14 01-25-2007 10:20 AM
Searching across multiple tables with same columns.... ds9686 General 1 12-02-2005 03:37 PM
Storing multiple values efficiently MrAustin General 3 01-11-2005 09:47 PM
Searching multiple tables Michelangelo Queries 3 01-10-2003 05:50 AM
Searching/Updating multiple tables at once vangogh228 Queries 2 11-12-2002 08:02 AM




All times are GMT -8. The time now is 01:08 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World