Hi,
I have a problem that will probably be simple to solve but I just can't see it. I have an Access table mostly populated but one field partly populated but includes Null values. I cannot simply use an Insert Into query as the Null value has to be different on every occasion with up to 130 consecutive additions of 16 in the column. Also the Query then has to reset for the next record null. There are 137 records to be updated with varying lengths. This affects some 12,000 odd individual records to be updated. Can someone suggest how to structure this query.
I have prepared a spreadsheet with a sample of the data.
Thanks in advance.

I have re-read that post 5 times now. And it still makes no sense at all to me.
Impossible. A NULL value is a NULL value. There can't be any difference between different NULL values.
So you end up with numbers such as 16, 32, 48, 64, ... 6400, 6464, ... 8320 ??? (8320 is 130 times 16)???
Okay, which is it. 137 records or 12,000 records??? Or are the 137 records scattered througout those 12,000 records? Or????

I'm sorry, but I can read each set of words individually and they parse just fine. But when I put together the sentences they just make no sense.

Maybe you need to show us sample data? Three kinds: "before" data, the data to be used for the update, "after" data??

Hi,
Sorry about confusion with my post. I'm aware of the issue of Null and used the wrong term. 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 | 24 | 0 | R | 9
CDC90050 | A | SCM70001 | 16 | 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!

Do you have the option of doing this is some server-side language? Or even in an executable application?

Examples:
In Java, as a standalone.
In PHP.
In ASP.
Whatever?

I think that would be far be easier than trying to get this right in a SQL query.

Oh, and what kind of DB is this??? SQL Server? Oracle? MySQL? What?

I am *assuming* that when you show us records such as That you intend that the "groups" are based on the part in each record. But then how are the records then *ordered*???
Excepting for the first two records you showed, it looks like they are ordered by the last field (1,9,9C5,9C6,etc.), but those first two records (32 and then a second 1) toss that out of the possibilities.

You just aren't giving enough details.

And you say that these records are coming via a query on other tables? So why aren't you doing the summations as part of *that* query?

Very very lost, still.