Update query (1 Viewer)

[Paltusn9]

Hello everyone and thank you for your support.
I would like to know your opinion how can I update the field " fSum" and get the result as in table "AfterUpdate"
- Criteria: ID and fld1
- Update field: fSum
- Remark: I have 30 000 records in my table to be calculated

InitialData

id	fld1	fSum
1​	10​	0​
2​	10​	50​
3​	10​	0​
4​	20​	0​
5​	20​	0​
6​	20​	500​
7​	20​	0​
8​	30​	600​
9​	30​	0​
10​	30​	700​
11​	30​	0​
12​	40​	0​
13​	40​	200​
14​	40​	0​
15​	40​	0​

AfterUpdate

id	fld1	fSum
1​	10​	0​
2​	10​	50​
3​	10​	50​
4​	20​	0​
5​	20​	0​
6​	20​	500​
7​	20​	500​
8​	30​	600​
9​	30​	600​
10​	30​	700​
11​	30​	700​
12​	40​	0​
13​	40​	200​
14​	40​	200​
15​	40​	200​

 

[theDBguy]

Hi. Welcome to AWF!

You might be able to use a subquery, but you can also definitely do it using VBA.

 

[plog]

I don't follow your example. These 2 expected results don't jive:


ID=4, you want to remain at 0
ID=15, you want to update to 200

Why is 15 updated but not 4?

I think it would be best to explain the algorithm in english. Write a paragraph of what you want to happen.

 

[isladogs]

I think the logic is this.
For each value of fld1, update all zero fSum values to the previous non-zero fSum value for that fld1.
Sort of like a 'running sum' ... but not quite?

 

[Paltusn9]

Thank you very much for your kind and quick response.
I am doing this task a whole week and I am ready to give up. This forum is my last chance

There are 2 criterias ID and fld1 and based on them the data should be calculated.
For instance:
ID=4 has value 20 in the fld1. Totally, value 20 repeats 4 times ( ID=4/5/6/7), but ID=4 and ID=5 have fSum=0, nothing needs to be updated.
The only ID=7 (goes after ID=6 with fld1=20 and fSum=500) should be updated as 500 taken from above records.

Next, fld1=30 (ID=8/9/10/11)
The fsum of ID=8 and fld1=30 is 600. For ID=9 (fld 30) the fsum should be 600 as above. For ID=10 fSum remains as 700 and in last, for ID =11 the fSum should be 700 (as above value)

Hopefully, now it's clear and sorry for english. I guess, it can hurt you ))

 

[isladogs]

Hi @Paltusn9
Sorry but I didn't understand your explanation.
Did you understand what I suggested in post #4? If so, did I understand it correctly?

 

[Paltusn9]

Hello @isladogs
Not exactly, It doesn't need to update all zero values. The values gone after non-zero are needed to be updated for which fld1 is the same
ID 4/5/6/7; fld1=30

ID	fld1	fSum	After update
4	30	0	0
5	30	100	100
6	30	0	100
7	30	200	200
8	40	0	0

 

[plog]

Your english is good and now I follow your logic. I built a SELECT query to determine the new value of each record's fSum. Here's that query:

SELECT s1.id, s1.fld1, s1.fSum, (Select MAX(fSum) FROM InitialData WHERE s1.fld1=InitialData.fld1 AND InitialData.id<=s1.id) AS NewfSum
FROM InitialData AS s1;

Paste that into your database and you should be able to convert it to an UPDATE--you might have to make it a subquery and link InitialData to it to do so.

 

[isladogs]

Hello @isladogs
Not exactly, It doesn't need to update all zero values. The values gone after non-zero are needed to be updated for which fld1 is the same
ID 4/5/6/7; fld1=30

ID	fld1	fSum	After update
4	30	0	0
5	30	100	100
6	30	0	100
7	30	200	200
8	40	0	0

Hi
That was what I was trying to say,..even if it wasn't clear!

 

[Paltusn9]

@plog,
The query works perfectly! Wow...Thank you so much! This is exactly what I need !
May I please bother you a bit more. Can you please advise how I can build an update quiery ? It is my first time I have a deal with subquery....
I appreciate for your help. Does it look like that ?

Update s1 Inner Join InitialData On ((s1.fld1=InitialData.fld1) AND (InitialData.id<=s1.id))
Set s1.NewfSum=Max(fSum)
FROM InitialData as s1

 

[plog]

I'm uncertain how to make it into an UPDATE query because the sub-query makes it unupdateable. I would instead make it a MAKE TABLE and then use the table that is made to UPDATE InitialData.

 

[Paltusn9]

@plog
Thank you very much for your help!