UPDATE skips one record

[simon03]

Hi,

I have a query which reads a table, calculates some values and updates some of the table's fields. The issue that I have is that sometime (and randomly) the update skips one record. The query uses an inner join and looks as follows:

UPDATE TEMP4 AS t1 INNER JOIN TEMP4 AS t2 
ON (t1.Month = DATEADD(""m"",1,t2.Month)) 
SET t1.index=(1+t1.ratio)* t2.index, t1.ratio = IIF( t2.index=0 ,t2.ratio, t2.index)

I can run the query multiple times without seeing any problem. When the issue occurs I do not receive any error.

I should also add that the query/update is run via VBA code. Not sure if this makes any difference.

Do you have any suggestions/ideas why this happen and how I can force the query to always update all records?

Thanks!
Simone

 

[jdraw]

Why are you storing Month as an individual field?
Month and index are reserved words in Access.
Most readers will advise you , do NOT store calculated values.

 

[simon03]

Right. I changed the code to make it more readable here but actually my SQL code looks like this:

UPDATE TEMP4 AS t1 INNER JOIN TEMP4 AS t2 
ON (t1.Month_ = DATEADD("m",1,t2.Month_)) 
SET t1.index_=(1+t1.ratio)* t2.index_, t1.ratio = IIF( t2.index_=0 ,t2.ratio, t2.index_)

Why am I storing calculated values? it is a long story and I opened a couple of threads in this forum a few months ago. The results of the discussion was that in my case the best way was to store the calculations.

Thanks,
S.

 

[Minty]

Can you post up some trial data and highlight the records that get missed?
Without seeing the issue first hand I doubt we'll get to the bottom of it.

 

[plog]

Why am I storing calculated values? it is a long story and I opened a couple of threads in this forum a few months ago.

Can you point to that thread? I can't seem to find it in your post history.

Also, things aren't usually random. Find the offending data and post it, along with the corresponding data in the other table. My guess is this has to do with a NULL value somewhere. Either no record when you link to T2 or t2.ratio and/or t2.index is blank for a partuclar record.

 

[simon03]

I guess this post summarises quite well and was quoted by other users later as the right approach to my problem:

http://www.access-programmers.co.uk/forums/showpost.php?p=1374347&postcount=33

Regarding posting a sample of the DB it is quite complicated at the moment (meaning that the VBA does hold a lot of calculations before running the UPDATE query and the problem occurs only there). I will try to have it posted soon.

Though I was wondering if anyone has ever had a problem like this. Why would Access randomly skip to update one record? and why doesn't this happen every time I run the UPDATE query? The data is the same, nothing changes, same calculations before but sometime it fails to update one record.

Thanks,
Simone

 

[arnelgp]

to see error:

docmd.runSql "
UPDATE TEMP4 AS t1 INNER JOIN TEMP4 AS t2 ON (t1.Month_ = DATEADD('m',1,t2.Month_)) SET t1.index_=(1+t1.ratio)* t2.index_, t1.ratio = IIF( t2.index_=0 ,t2.ratio, t2.index_)"

 

[simon03]

I guess I have found the error and it's not in the UPDATE per se. After running the update I noticed that the TEMP4 table is not always sorted by date. It appears that the inner join sometime messes up the order of the table.

I have implemented this code:

UPDATE TEMP4 AS t1 INNER JOIN (SELECT * from TEMP4 ORDER by [Month]) AS t2 
ON (t1.Month_ = DATEADD("m",1,t2.Month_)) 
SET t1.index_=(1+t1.ratio)* t2.index_, t1.ratio = IIF( t2.index_=0 ,t2.ratio, t2.index_)

As far as I have tested this code, it works fine.

Does this make any sense?

Thanks,
S.

 

[plog]

Not at all. You never use any order specific logic in the superquery that t2 is part of. There's really no reason to apply an order to t2.

 

[simon03]

plog, you are right. That was not the issue. It looks like the issue is what was pointed out in one of the first replies (use of reserved words for fields). I was actually using the reserved word "Month" for a field.

What I noticed is that when the algorithm produced wrong calculations, records were not ordered anymore (not that they need to be ordered for the algorithm to work but it was interesting to note this).

I tested the new algorithm by running it 4000 times and worked fine. It is though interesting that in certain of my previous tests, I tested the algorithm (which included the reserved word) about 1000 times and only 2-3 times it produced wrong calculations.

Just for peace of mind, how is that a reserved word used in fields only sometime creates issues? Is this a reasonable explanation for my problem?

Thanks,
S.