Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  Other Forums  MS Access  Access update query problem

Author  Topic  weirdg Starting Member 4 Posts Posted - 2009-09-03 : 12:34:08 Hi, I have a problem I can't seem to get past. I have some rows with incorrect values that I need to update. The row values in question come from an append query to the table that has many inner joins to multiple tables. The first 4 rows are appended from separate append queries with the last 5 (in this example) from a single append query. The full table consists of another 136 sets of similarly structured info with varying amounts of rows from 9 to 130. It is very difficult to assign a constant to declare the value. Table structure all text fields with no key fields. Sample data below. There are 9 records to display that are connected with Tot field = 32 for 5 values. .........................Len Tot CDC90050 | A | SCM70001 | 8 | 0 | 0 | R | 32 CDC90050 | A | SCM70001 | 8 | 8 | 0 | R | 1 CDC90050 | A | SCM70001 | 8 | 16 | 0 | R | 1 CDC90050 | A | SCM70001 | 8 | 24 | 0 | R | 9 CDC90050 | A | SCM70001 | 16 | 32 | 0 | R | 9C5 CDC90050 | A | SCM70001 | 16 | 32 | 0 | R | 9C6 CDC90050 | A | SCM70001 | 16 | 32 | 0 | R | 9C7 CDC90050 | A | SCM70001 | 16 | 32 | 0 | R | 9C8 CDC90050 | A | SCM70001 | 16 | 32 | 0 | R | 9C9 I want to keep the first 32 then next 32 = 32 + 16 for second and so on to keep on adding 16 to next row 32. In essence this is a running total of the sum of the previous Len + Tot Fields. This is what I want to achieve: .........................Len..Tot CDC90050 | A | SCM70001 | 8 | 0 | 0 | R | 32 CDC90050 | A | SCM70001 | 8 | 8 | 0 | R | 1 CDC90050 | A | SCM70001 | 8 | 16 | 0 | R | 1 CDC90050 | A | SCM70001 | 8 | 32 | 0 | R | 9C5 CDC90050 | A | SCM70001 | 16 | 48 | 0 | R | 9C6 CDC90050 | A | SCM70001 | 16 | 64 | 0 | R | 9C7 CDC90050 | A | SCM70001 | 16 | 80 | 0 | R | 9C8 CDC90050 | A | SCM70001 | 16 | 96 | 0 | R | 9C9 You may be able to suggest an easier way to put this calculation into the table. hope that makes it clearer. KISS(Keep It Simple Stupid) is my motto! KISS(Keep It Simple Stupid) is my motto!