need help to open, for ,next 3

[mikeasa]

I am an old basic programmer and dont know how to deal with opening a file. I trying to understand a type of ACCESS module VB code. This is what I am trying to do.

from the query

table A.field 5

npa: qmknpa(table A.field 5)

to the module

this is what i need

table A.field 5 13-18 digits 300K in records
table B.field 2 2-6 digits 3K in records
I want to match table A.field 5 to table B.field 2 and have it look at the beggining of table A.field 5 by the lenth of table B.field 2 and match that string.

example

open table B

for x= 1 to eof

if left(table A.field 5,len(table B.field 2))= table B.field 2 then qmknpa = table B.field 2 : end next

else next

end

 

[MajP]

You need to explain this a little more. You mention "files" but then you talk about queries. Here is an example. In table B I have shortened last names, in table A I have complete last names.
ex

Smi Smith
Jone Jones
Merch Merchant

SELECT tblB.txtSSN, tblB.strFirstName, tblB.strLastName, tblA.strLastName
FROM tblB, tblA WHERE (([tblB].[strLastName]=Left([tblA].[strLastName],Len([tblB].[strLastName]))

This query returns the matches

Is this what you mean?

 

[traingamer]

This is a continuation of thread thread702-1262454

 

[mikeasa]

please forgive the cross over in file table. I come from old world basic. There are 2 tables 1 variable table_A 300K records the other fixed table_B 3k records. I need to match the field in A to the field in B. The field in A is 13-17 digits long this has to match field in B 3-6 digits long and disply that value. I did this in a query using SQL statements. It took to long to complete. I thought using a VB inside module with statements above would work faster.

 

[traingamer]

After reading the answers in the previous posting, I have several important questions before trying to answer this.

How often are records added to table b?

How often are they added to table a?

In either case, you probably want to work with one or more recordsets.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[MajP]

No way will you be able to write code using recordsets that is faster then sql.

I would think the fastest way would be using arrays especially if you can sort these digits. You could then use a binary search algorithm or something a little fancier.

 

[traingamer]

No way will you be able to write code using recordsets that is faster then sql.

Sure you can if you're using two recordsets rather than 300,000 sql selects. Yes, arrays with a binary search would be faster still.

My actual point, is that there is no need for either - the records should be updated as they are added to the database, removing the need to run a massive update daily (or whenever).


Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[MajP]

Actually, if these are numbers and not digits I have some similar code. Same problem, the table had 1million records and the query took a long time. I was looking for differences between two values.

What you do not want to do is compare your 30K against every record in the 300k. What is that 9000000000 steps? If you can do a binary search you cant get that down to about 200k steps. At this size you may be able to stick with recordsets and not use arrays.

 

[mikeasa]

In table A the data is NEW all the time daly, weekly or monthly it will depend on how large the file is. Now it is done monthly but we will most likely do it weekly to keep file smaller.

In table B we may add new records not often.

 

[PHV]

I did this in a query using SQL statements
Could you please post the SQL code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MajP]

I see that this was a carryover from another post so I was missing some background. I agree with Greg that you should look at a better strategy and update your table dynamically.

I will restate my assertion that 999 times out of 1000 you can write faster more efficient sql code then looping using recordsets. Using calculated fields in a query can be slow, and often there are tricks to optimize your sql strategy. You might require intermediate queries or temp tables. How slow was Remou's strategy? Also, are these fields indexed?

I am not sure what Greg's approach would be using two recordsets, I can not think of an efficient strategy. If you read through the first recordset (30k) and did a find first looking for the best solution in the second recordset you would have on the average 90k searches , and then would have to write to a table. I would think that this would be slower than Remou's suggestion. I might be wrong.

 

[mikeasa]

I thank you all for your help I got it figured out and it works great. I took all your input and created what I needed.

again thank you