Link to home
Start Free TrialLog in
Avatar of ensa
ensa

asked on

Cummulative Line chart

Hi Guys,

I have a table with three fields, Weeknno, Office and Cases. I want to produce a line chart that shows the cumulative total number of cases for each office over the weeks. For example, if I have 3 offices, each office would have a different line represented on the graph showing its growing total number of cases over a period of time. Th einformation is currently stored in a table like:

Weekno |  Office  | No Cases
01         | London |  26
01         | Paris    |  32
02         | London | 14         (cumulative total would be 40)
02         | Paris     | 4          (cumulative totla would be 36)

.. and so on...

Is it posssible to do this?

Many thanks

Ensa
Avatar of svenkarlsen
svenkarlsen
Hi ensa,
You can make a query like this to create the accumulated figures to base your graph on:

SELECT tbl.WeekNo, tbl.office, DSum("[No Cases]","tbl","WeekNo <= " & [WeekNo] & " AND [office]=" & [office]) AS AccCases
FROM tbl
ORDER BY tbl.WeekNo, tbl.office;


Kind regards,
Sven
Avatar of ensa
ensa

ASKER

Hi Sven,

Thanks for the prompt reply. Unfortunatly, the output produces #error, when I try this. When I mouse over the column a msgbox says 'The object doesn't contain the automation object' and then displays the name of the office. All the fields and table name are spelt correctly. Any ideas?

Thanks again

Ensa
Avatar of svenkarlsen
svenkarlsen
Ensa,

could you post the query you've made here as SQL, please ?

Sven
Avatar of ensa
ensa

ASKER

Sven,

SQL below as you posted with the table entered.

SELECT CSOfficesTab.WeekNo, CSOfficesTab.office, DSum("[No Cases]","CSOfficesTab","WeekNo <= " & [WeekNo] & " AND [office]=" & [office]) AS AccCases
FROM CSOfficesTab
ORDER BY CSOfficesTab.WeekNo, CSOfficesTab.office;



Ensa
Avatar of ensa
ensa

ASKER

Hi Sven,

Sorted it!

Correct SQL needed single quotes round in the Dsum ie.

SELECT CSOfficesTab.Weekno, CSOfficesTab.OFFICE, CSOfficesTab.[No Cases], DSum("[No Cases]","CSOfficesTab","WeekNo <= '" & [WeekNo] & "' AND [office]='" & [office] & "'") AS AccCases
FROM CSOfficesTab
ORDER BY CSOfficesTab.Weekno, CSOfficesTab.OFFICE;


Now working perfectly. Many thanks for your help.

Ensa
ASKER CERTIFIED SOLUTION
Avatar of svenkarlsen
svenkarlsen
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial