Comparing Records in two (2) Tables.

[ssanfu]

Again let me say thank you for all you help in both cases when I had troubles.
Thanks


RalphJ

Even though you solved this, attached is the mdb I created to test the code.

My small table has 201 records and the large table has 1133 records. The inner loop (Large table records) loops 227,733 times. So that means there are 8,198,388 field comparisons made. (201*6*1133*6)

I modified the code to use the arrays. I don't know how long the comparisons take using the recordsets (non array), but the (new) array code takes 6 to 7 seconds to execute.

[RalphJ]

Even though you solved this, attached is the mdb I created to test the code.

My small table has 201 records and the large table has 1133 records. The inner loop (Large table records) loops 227,733 times. So that means there are 8,198,388 field comparisons made. (201*6*1133*6)

I modified the code to use the arrays. I don't know how long the comparisons take using the recordsets (non array), but the (new) array code takes 6 to 7 seconds to execute.

Thanks Steve
I will take a look at it. If it is faster than what I have I will be happy to use it. Take care and again thanks for all your assistance
RalphJ

[RalphJ]

Thanks Steve
I will take a look at it. If it is faster than what I have I will be happy to use it. Take care and again thanks for all your assistance
RalphJ

Steve
The code is about 30 seconds faster than the code I had modified. It rus the 12 part array with 3600 and 600 respectively in more or less 75 seconds.

I will use your code and again thanks
RalphJ

[ssanfu]

Steve,
I have had an opportunity to run the code. I altered the array to fit the program and had 0-12 array. The code worked fine but is pretty slow. The compare was done between 600 records in the S.table and 3500 in the L.Table. It takes about 17-18 minutes to complete the comparision. It does provide the right answers when it is done, and updates each record accordingly.

I have also modified the code I had posted and tried to see if it would be quicker. It will compare the same amout of records in 9 minutes but it writes a cumulative total to the S.Table and writes the value to each record (does not update a single record, but all of them (600). I could not make it loop correctly so I used a GoTo statement. (I know it is not good code but I was just testing). I can't seem to stop the way the records are updated with that version of code. This is the Array version you had provide me some time ago.

Anyway, that is the story. If you have any ideas on how to speed it up I am open to suggestions. I do believe the older code that had the array would be faster if I could stop it from updating 600 records every time the values change.

Thanks
Ralph

Glad to help (if I did). If you have 12 numbers in each table, then there would be a total of 302,400,000 comparisons! That would take a while...

I'm kinda flying blind without being able to see your database. I will have access (no pun intended) to A2K7 in a couple of days, so if you want and if you can convert it, I will take a look at it and see if I can speed it up.

As far as the way it is updating all the tables, it is the placement of the update lines.

[RalphJ]

Glad to help (if I did). If you have 12 numbers in each table, then there would be a total of 302,400,000 comparisons! That would take a while...

I'm kinda flying blind without being able to see your database. I will have access (no pun intended) to A2K7 in a couple of days, so if you want and if you can convert it, I will take a look at it and see if I can speed it up.

As far as the way it is updating all the tables, it is the placement of the update lines.

Steve
I think it is functioning about as well as it can. I have compared the speed to some commerical programs that perform the same function and we are only a 1 or 2 seconds slower. Thats pretty good for Access and VBA.
Don't ever think you did not help. I assure you, you were a tremendous help.
Again thanks for that help
RalphJ