Updating one table from another table - performance issue

[ozdemirozgur]

Hi,

I am very new to access and eed some help. I need to update one table from another and there is one to many relations. i have the following code which works but because the second table has 700,000 records, it is extremely slow. Is there any way to improve the code.

Do While Not Main10_2000_Combined.EOF
RemunerationD = 0

Do While Not Rem10_2000_Combined_Annualized.EOF
If ((Rem10_2000_Combined_Annualized!Coy_Id = Main10_2000_Combined!Coy_Id) And (Rem10_2000_Combined_Annualized!Rem_Year = 2000) Then
RemunerationD = RemunerationD + Rem10_2000_Combined_Annualized!TOTAL

End If
Loop
Loop

 

[Beetle]

The way to improve this would be to use an Update query instead of trying to loop through a record set, especially one with >700,000 records! (yeah, I bet that's slow!)

Can you provide some more details about your structure and what, exactly, it is you're trying to do?

 

[pbaldy]

Seems like I saw this before? 3 thoughts. First, you appear to be trying to store annual totals or something. Generally that wouldn't be a good idea; you'd just calculate the totals on the fly with a totals query.

Second, if you still want to do it, would be to wonder if an append or update query would be a better solution. It would be based on the same type of totals query you'd use above. Most of the time an SQL solution is far more efficient than a recordset loop solution.

Third, addressing your question more directly, your inner loop appears to loop the entire table every pass of the outer loop (based on the If test). Much more efficient would be to open the recordset inside the first loop using an SQL statement that only pulled the relevant records based on ID and year, or even better summed those records, eliminating the loop.

More info on what you're doing would likely help, like what the raw and target data looks like.

 

[pbaldy]

Geez, am I a slow typist or what?

 

[ozdemirozgur]

Hi both,

yes, I am trying to get the totals from the second table. I do not have how to write an update statement here/. Basically table one ahs Company Id, and Table 2 has Company Id, Year and Salary. I will be glad if you can write me a short update statement?

thanks, ozgur

 

[Beetle]

In that case you don't need an update query (or any other method of moving these records). You should not be attempting to store the same data in two different tables. Just create a simple Select query joining the two tables on CompanyID and display whatever fields are necessary from both tables. For example, the Company Name from Table1 and the Year and Salary from Table2. If you're not sure how to do this, post back.

 

[ozdemirozgur]

Hi,

it is not the same information, I need to have the total of salaries for each company. I need a update statement which will read the company master table then go to the salaries table, find the salaries for each company and then get total by year.

thanks

 

[pbaldy]

Try this query, changing the field names as appropriate:

SELECT CompanyID, [Year], Sum(Salary) AS TotalSalaries
FROM Table2
GROUP BY CompanyID, [Year]

If that looks like it's giving you the correct info, it's easy to get the name.

 

[ozdemirozgur]

Hi Paul,

thanks, but I do not need a query on Access. I need an update routine which will update my master table after getting the calculations.

thanks, ozgur

 

[pbaldy]

And what if you could get the numbers with a simple query instead of a god-awful nested loop?

 

[Beetle]

In a relational database like Access, you should not be trying to store calculated values like this in a table. There are several reasons for this, but the main reason is that if any of the underlying data that the original calculation was based on gets changed, or added to, then you have data in your table which is wrong, and no automatic way to detect that fact.

You can calculate this data in a Totals query anytime you need. There is no reason to store it in a table. If you are doing this because you are trying to create a data set that is going to be exported to some external (non Access, non relational) database, or a spreadsheet, you likely still don't need a table. You can just transfer the data from the query to the external source.

There is a right way and a wrong way to do things. The goal of this forum is to help people do things the right way.

 

[ozdemirozgur]

Hi all,

Thanks for your email, I do understand why I should not store the total in the main table. It is a data which has the salary details of companies back to 2003-2008 which will never change. I am not a professional programmer but just doing my masters degree.

Some of the salary data is missing, and some are wrong etc. My main table has also other fields such company type etc. Then once I get the totals I need to delete some of the companies based on the salary total values, company type etc etc. I have a field to mark if the salary data of a company has problems then I will not usethem in my SPSS analysis. So, there are tons of other works once I get the totals. unfortuantely, I cannot do these tasks without seeing the totals first. Please can you tell me how I will ge the totals on the first table?

thanks, ozgur

 

[boblarson]

Hi Paul,

thanks, but I do not need a query on Access. I need an update routine which will update my master table after getting the calculations.

And that response that you just gave is like a doctor (you) going up to a fisherman (paul) and asking the fisherman (paul) what bait to use on his fishing pole to catch crab as he needs to catch 20 crab. And then the fisherman (paul) tells the doctor (you) that it isn't the best way to catch crab, that it would be faster and more efficient to use a crab pot. But the doctor (you) say that you don't need a crab pot, that you can catch 20 crab with a fishing pole and line. The moral of that story is that yes, you can catch crab with a fishing pole. But it is a fairly rare feat and it would take much, much longer to do than if you used the RIGHT TOOL and used the crab pot (in your case the QUERY).

Get it?

 

[ozdemirozgur]

Hi,

I really like this forum. It is becoming real fun but still not helping. Hopefully I am not a doctor and not a fisher man. I need only one fish and will never fish again so do not need to learn the best methods of finishing.

Please can someone help?

thanks, ozgur

 

[spikepl]

Q: I am digging a hole, and progress is slow, can anyone advise?
A:Why not get an excavator?
Q: I will only do the digging once, so I do not want an excavator, I want to use a toothpick

Seriously, do you think that reposting your question here: http://www.access-programmers.co.uk/forums/showthread.php?p=1065372#post1065372 will bring any other advice?

Update:

If it is a one off, set the machine to run Friday, pick up the results Moday, and end of story.

Besides, it is a waste of the voluntary support resources on this site just to leave a post hanging, and posting the very same question one more time.