Conditional progressive sum

[davideitaly]

Hi all.
I have a query summing up all the work periods of some marine officers. They're supposed to do at max 6 periods onboard. This is the query structure, where "TOT" is a calculated field which gives the difference between Al (to) and Da (from).





This is how it looks like if I select a record (1664) related to an officer:



I would need to set another column calculating the "progressive" days sum, taking it from the totals. Some like that (made in Excel as example):



In other words I would need a formula asking the query to calculate the progressive sum of TOT for the 1 to 6 onboard periods for each officer ID.

Can anyone help me to build the right formula?

(if not in the same query, I could also put the formula on a dedicated field in the form recalling the query).

Thank you in advance!

Davide

[Gicu]

Hi Davide,
You need a cumulative sum or a running totals query - https://support.microsoft.com/en-us/...crosoft-access
In your case would look something like this:
Prog: DSum("[TOT_gg]","[anag_tot_imbarchi]","[ID_anag_allevi] ='" & [USER] & "' AND [Imbarco_n] <= " & [Imbarco_n])

Cheers,
Vlad

[davideitaly]

Hi Davide,
You need a cumulative sum or a running totals query - https://support.microsoft.com/en-us/...crosoft-access
In your case would look something like this:
Prog: DSum("[TOT_gg]","[anag_tot_imbarchi]","[ID_anag_allevi] ='" & [USER] & "' AND [Imbarco_n] <= " & [Imbarco_n])

Cheers,
Vlad

Thenk you Vlad.
It says there is an error in syntax, identifying this in bold: DSum("[TOT_gg]","[anag_tot_imbarchi]","[ID_anag_allevi] ='" & [USER] & "' AND [Imbarco_n] <= " & [Imbarco_n])

And then... what do you mean with "USER"?

Thanks.
D.

[Gicu]

Davide,

Please check the spelling, it was just meant as an example as I am not sure of your field names. [USER] should be an alias of your [ID_anag_allevi...]field. I would create a new query (anag_tot_imbarchi_prog) based on your original one, bring the fields you need from the source query and add an alias for user id (USER:ID_anag_allevi) and one for the IMB: imbarchi_n and us those in the formula:

DSum("[TOT_gg]","[anag_tot_imbarchi]","[ID_anag_allevi] =" & [USER] & " AND [Imbarco_n] <= " & [IMB])

Have a look at the link from Microsoft, it should help you understand how this works.

Cheers,
Vlad

[davideitaly]

Davide,

Please check the spelling, it was just meant as an example as I am not sure of your field names. [USER] should be an alias of your [ID_anag_allevi...]field. I would create a new query (anag_tot_imbarchi_prog) based on your original one, bring the fields you need from the source query and add an alias for user id (USER:ID_anag_allevi) and one for the IMB: imbarchi_n and us those in the formula:

DSum("[TOT_gg]","[anag_tot_imbarchi]","[ID_anag_allevi] =" & [USER] & " AND [Imbarco_n] <= " & [IMB])

Have a look at the link from Microsoft, it should help you understand how this works.

Cheers,
Vlad

Thanks Vlad. But it says there is a syntax mistake... but I can't got why or where...

[Gicu]

Can you show what you have? Screen- shot or even better a small Access db with the table (empty or a few sample records) and the query.

Cheers,
Vlad

[davideitaly]

I wouldn't know how to attach an entire DB. Anyway the situation is like I described in the first message. I just changed the fields name. Now the starting query is this one:



Behind is like that:



Where "ID_imbarchi_anagrafica" is linked to the ID main form.
"N" is the progressive number of onboard experiences.
"Dal" is from (date)
"Al" is to (date)
"TOT" is the field calculating the number of days spent on board.
I tried to insert the "DSum" code in the sixth colum to have the progressive sum, but it says there is a mistake in the syntax.
This is the code adapted to the new field names:

TOTALE: DSum("[TOT]","[imbarchi_stageqry]","[ID_imbarchi_anagrafica] =" & [ID_imbarchi_anagrafica]" & " AND [N] <= " & [N])

This is the screen message:



It says: "Syntax error in the expression - Operating or operator missing, letter or comma invalid or input text not included in quotation marks".

[Gicu]

The second argument in the dsum is the query name which doesn't seem right. Also, you need to use aliases in the condition part. Assuming the imbarchi_stageqry query you use in the dsum does also have a field called TOT and has the ID_imbarchi_anagrafica and N fields you need to rename the fields in the new query (imbarchi_sintesistage) to be different:
IDImbarchi:ID_imbarchi_anagrafica And NUM:N

TOTALE: DSum("[TOT]","[imbarchi_stageqry]","[ID_imbarchi_anagrafica] =" & [IDImbarchi]" & " AND [N] <= " & [NUM])

Cheers,
Vlad

[Gicu]

Davide,

Have a look at the attached file.

Cheers,
Vlad

[davideitaly]

The second argument in the dsum is the query name which doesn't seem right. Also, you need to use aliases in the condition part. Assuming the imbarchi_stageqry query you use in the dsum does also have a field called TOT and has the ID_imbarchi_anagrafica and N fields you need to rename the fields in the new query (imbarchi_sintesistage) to be different:
IDImbarchi:ID_imbarchi_anagrafica And NUM:N

TOTALE: DSum("[TOT]","[imbarchi_stageqry]","[ID_imbarchi_anagrafica] =" & [IDImbarchi]" & " AND [N] <= " & [NUM])

Cheers,
Vlad

Argh... getting crazy... I didn't know about the ALIAS trick. Anyway there's still something wrong. The result is #error:

STRUCTURE



TABLE

[Gicu]

Can you try and copy it from the file I've sent you and see if you still get the error?

[Gicu]

Looking at your data I see you have N as text not number so you need to wrap it in single quotes:

TOTALE: DSum("[TOT]","[imbarchi_stageqry]","[ID_imbarchi_anagrafica] =" & [IDImbarchi]" & " AND [N] <= '" & [NUM] & "'")

[Gicu]

Just realized that you might have problems if you keep N as text when you compare text. If you cannot change it in the table do it in the imbarchi_stageqry by using a N_Int:CInt([N]) and change the field names accordingly in the final query/

Vlad

[davideitaly]

Hi all.
Vlad's code works well. The core problem was just the field N as text. I changed it to number and now everything works in the queries.
But not in the form. Take a look, this is the form where the progressive sum is shown (fake data as example), with ID = 115:



But if I browse to the next record (ID = 116) to see the data of the next seafarer and it has no embarkation data, it pops up an error, like:



"Syntax error (missing operator) in the query expression "ID_imbarchi_anagrafica] = 116 etc...

Maybe I should put some Nz in the code?

Thanks!

[Gicu]

Davide,

It depends on your needs, you could use NZ or you could put Not Null in the Dal and Al fields of the query to force the calculation to occur only for the right records.

Cheers,
Vlad