Running Totals (1 Viewer)

[chthomas]

Hi,

Am attaching a db related to my question

I have 2 tables "Summary Table" and "Select1Summary"


"Summary Table" shows projects, departments and the available hours for each department

eg.

In "Summary Table" for 71043-40 Project, there are 1808 hours available at the beginning

"Select1Summary" shows Project, department, Month and the hours each department will be booking

eg.

In "Select1Summary" Table(Actually its a summary query, but am putting it as a table)

for 71043-40 Project, Department 11 on 30/04/2007 will be booking 171 hours (ie in April dept. 11 books 171 hours for project 71043-40)

for 71043-40 Project, Department 11 on 31/05/2007 will be booking 135 hours

for 71043-40 Project, Department 11 on 30/06/2007 will be booking 108 hours

What i need to show is running totals in a new query like


for 71043-40 Project , Department 11 on 30/04/2007 Available hours = 1637 (ie 1808-171)
for 71043-40 Project, Department 11 on 31/05/2007 Available hours = 1502 (ie 1637-135)
for 71043-40 Project, Department 11 on 30/06/2007 Available hours = 1394 (ie 1502-108)

Kind Regards,

Charley

 

[Jon K]

Since you want to display the running totals by Project and Department, you need to add them in the DSum().

BalanceHours: [AvailableHours] - DSum("[SumOfHours]","[Select1Summary]","Projcode='" & [Projcode] & "' and Department ='" & [Department] & "' and Month<=#" & [Month] & "#")
.

 

[neileg]

Do you need to do this at query level? Running sums are easy in a report.

 

[chthomas]

JonK,

Thanks a million. I was getting lost there. It works. Thanks for sparing your time.

Neilg,

I need to export this to excel, the reason for using in query.

Regards,

Charley