running total time period

[dadaboss92]

Hi,

My table t_activity has got:
a foreign key key_project related to the project name
a foreign key key_architect related to the architect name
date
period which is a combo list with AM and PM

In my form i write the name of the architect who work on a project, the date and if it was during the AM or PM

I would like to follow up the time spent on each project. To succeed i need a running total of the AM and PM period for each project according to the dates


Exp:
Projet1 david 15/05/2015 AM
Projet1 david 15/05/2015 PM
Projet1 david 16/05/2015 AM
Projet1 david 16/05/2015 PM
Projet1 david 17/05/2015 AM
Projet1 david 17/05/2015 PM

Projet2 david 18/05/2015 AM
Projet2 jean 18/05/2015 AM
Projet2 jean 18/05/2015 PM


Projet2 jean 19/05/2015 AM
Projet2 jean 19/05/2015 PM

Cumul progressif
Projet1 15/05/2015 2
Projet1 16/05/2015 4
Projet1 17/05/2015 6

Projet2 18/05/2015 3
Projet2 18/05/2015 5


I tried many fonction as but none work:
Expr1: DSum("[period]","t_activity","[date]='" & [date] & "'")
Expr1: DSum("[period]","t_activity Query","[date]='" & [date] & "'")
Expr1: DSum("[Countofperiod]","t_activity","[date]='" & [date] & "'")
Expr1: DSum("[Countofperiod]","t_activity Query","[date]='" & [date] & "'")
Expr1: DSum("[period]","t_activity","[date]=#"&[date]&"#")
Expr1: DSum("[period]","t_activity Query","[date]=#"&[date]&"#")
Expr1: DSum("[CountOfperiod]","t_activity","[date]=#"&[date]&"#")
Expr1: DSum("[CountOfperiod]","t_activity Query","[date]=#"&[date]&"#")


Expr1: Dcount("[CountOfperiod]","t_activity Query","[date]=#"&[date]&"#")
Expr1: Dcount("[CountOfperiod]","t_activity","[date]=#"&[date]&"#")
Expr1: Dcount("[period]","t_activity Query","[date]=#"&[date]&"#")
Expr1: Dcount("[period]","t_activity","[date]=#"&[date]&"#")
Expr1: Dcount("[Countofperiod]","t_activity Query","[date]='" & [date] & "'")
Expr1: Dcount("[Countofperiod]","t_activity","[date]='" & [date] & "'")
Expr1: Dcount("[period]","t_activity Query","[date]='" & [date] & "'")
Expr1: Dcount("[period]","t_activity","[date]='" & [date] & "'")




Expr1: DCount("[key_architect]","[t_activity]","[key_Project]=""" & [key_Project] & """ AND [Date]<=#" & Format([Date],'mm/dd/yyyy') & "#")
Expr1: DCount("[key_architect]","[t_activity]","[key_Project]=""" & [key_Project] & """ AND [Dateexec]<=#" & Format([Dateexec],'yyyy/mm/dd') & "#")
Expr1: DCount("[key_architect]","[t_activity]","[key_Project]=""" & [key_Project] & """ AND [Date]<=#" & Format([Date],'yyyy/mm/dd') & "#")

I give you some screen shots also

Thank you in advance

[June7]

Running total is not easy to do in form or query. Textbox on report has a RunningSum property so is easy on report.

Do you need a running total or do you need a total count of periods for each date on each project?

SELECT Project, [date], Count(Period) AS CountPer FROM t_activity GROUP BY Project, [date];

Is the date field a date/time type, not text?

DCount("[period]","t_activity","[date]=#" & [date] & "# AND Project='" & [Project] & "'")


Date is a reserved word and should not use reserved words as names.

[dadaboss92]

Thank you for your reply but it doesn't work.

It is essential for me to have the running total per date and per project in order to do a graph. I need data to make it (running total = axis y) & (date = axis y).

If there is another solution or idea, even to add or modify my table i am ok.

Thank you

Running total is not easy to do in form or query. Textbox on report has a RunningSum property so is easy on report.

Do you need a running total or do you need a total count of periods for each date on each project?

SELECT Project, [date], Count(Period) AS CountPer FROM t_activity GROUP BY Project, [date];

Is the date field a date/time type, not text?

DCount("[period]","t_activity","[date]=#" & [date] & "# AND Project='" & [Project] & "'")


Date is a reserved word and should not use reserved words as names.

[June7]

"Doesn't work" means the query runs without error but doesn't provide the output you want?

Bing: Access query running total

Review: https://support.microsoft.com/en-us/kb/290136