sum and insert into table?

[mahmad]

Hi,

I currently have a table which holds the following:

Team Amount Date
------ --------- -----
Blue 100 01/01/08
Red 200 01/01/08
Green 500 01/01/08
Yellow 50 01/01/08

Team Amount Date
------ --------- -----
Blue 100 02/01/08
Red 200 02/01/08
Green 500 02/01/08
Yellow 50 02/01/08


I am able to show these on a graph on a daily basis, how can show sum the
Amount for each team as the days go by so i can show them on a graph for a
whole month. So i would have something like this:

Team Amount
------ ---------
Blue 200
Red 400
Green 1000
Yellow 100

Thanks

Mo

 

[John W. Vinson]

Hi,

I currently have a table which holds the following:

Team Amount Date
------ --------- -----
Blue 100 01/01/08
Red 200 01/01/08
Green 500 01/01/08
Yellow 50 01/01/08

Team Amount Date
------ --------- -----
Blue 100 02/01/08
Red 200 02/01/08
Green 500 02/01/08
Yellow 50 02/01/08


I am able to show these on a graph on a daily basis, how can show sum the
Amount for each team as the days go by so i can show them on a graph for a
whole month. So i would have something like this:

Team Amount
------ ---------
Blue 200
Red 400
Green 1000
Yellow 100

Well, you certainly wouldn't want to store this sum in any table, but you can
calculate it on demand. Make it a Totals query; group by Color and sum Amount,
and use a criterion on the date field (which, by the way, you should rename;
Date is a reserved word). Try

SELECT [Team], Sum([Amount])
FROM yourtable
WHERE [Date] >= [Enter start date:] AND [Date]<= [Enter end date:]

 

[mahmad]

Hi,

As you can see my current data comes from multiple tables. So where can i
fit the query you suggest. Note the tables are imported from a view within
MS SQL.

SELECT Sum(dbo_salesitems.sprice) AS SumOfsprice, dbo_salesorders.srep,
dbo_delv.dtaxd, dbo_delv.dntflg
FROM (dbo_delv INNER JOIN dbo_delvitems ON dbo_delv.delvno =
dbo_delvitems.delvnoa) INNER JOIN (dbo_salesorders INNER JOIN dbo_salesitems
ON dbo_salesorders.son = dbo_salesitems.sona) ON (dbo_delvitems.ditem =
dbo_salesitems.sonitem) AND (dbo_delvitems.dord = dbo_salesorders.son)
GROUP BY dbo_salesorders.srep, dbo_delv.dtaxd, dbo_delv.dntflg
HAVING (((dbo_salesorders.srep)="blue" Or (dbo_salesorders.srep)="green" Or
(dbo_salesorders.srep)="red" Or (dbo_salesorders.srep)="yellow" Or
(dbo_salesorders.srep)="isolok") AND ((dbo_delv.dtaxd)>#3/1/2008#) AND
((dbo_delv.dntflg)="Y"))
ORDER BY dbo_salesorders.srep, dbo_delv.dtaxd;

Hi,

I currently have a table which holds the following:

Team Amount Date
------ --------- -----
Blue 100 01/01/08
Red 200 01/01/08
Green 500 01/01/08
Yellow 50 01/01/08

Team Amount Date
------ --------- -----
Blue 100 02/01/08
Red 200 02/01/08
Green 500 02/01/08
Yellow 50 02/01/08


I am able to show these on a graph on a daily basis, how can show sum the
Amount for each team as the days go by so i can show them on a graph for a
whole month. So i would have something like this:

Team Amount
------ ---------
Blue 200
Red 400
Green 1000
Yellow 100

Well, you certainly wouldn't want to store this sum in any table, but you can
calculate it on demand. Make it a Totals query; group by Color and sum Amount,
and use a criterion on the date field (which, by the way, you should rename;
Date is a reserved word). Try

SELECT [Team], Sum([Amount])
FROM yourtable
WHERE [Date] >= [Enter start date:] AND [Date]<= [Enter end date:]

 

[mahmad]

Hi John,

Here is my current query which shows me the correct infor for a number of
days. What i want to do is to also show data for a month that is a
comulative figure.

SELECT Sum(dbo_salesitems.sprice) AS SumOfsprice, dbo_salesorders.srep,
dbo_delv.dtaxd, dbo_delv.dntflg
FROM (dbo_delv INNER JOIN dbo_delvitems ON dbo_delv.delvno =
dbo_delvitems.delvnoa) INNER JOIN (dbo_salesorders INNER JOIN dbo_salesitems
ON dbo_salesorders.son = dbo_salesitems.sona) ON (dbo_delvitems.ditem =
dbo_salesitems.sonitem) AND (dbo_delvitems.dord = dbo_salesorders.son)
GROUP BY dbo_salesorders.srep, dbo_delv.dtaxd, dbo_delv.dntflg
HAVING (((dbo_salesorders.srep)="blue" Or (dbo_salesorders.srep)="green" Or
(dbo_salesorders.srep)="red" Or (dbo_salesorders.srep)="yellow" Or
(dbo_salesorders.srep)="isolok") AND ((dbo_delv.dtaxd)>#3/1/2008#) AND
((dbo_delv.dntflg)="Y"))
ORDER BY dbo_salesorders.srep, dbo_delv.dtaxd;


I have created another table called monthly_despatched and insert the data
from the above query into this table and then i used your query as below.
However i dont get an output. What am i doing wrong.

SELECT Monthly_Despatched.team, Sum(Monthly_Despatched.amount) AS SumOfamount
FROM Monthly_Despatched
WHERE (((Monthly_Despatched.sdate)>=#3/1/2008# And
(Monthly_Despatched.sdate)=#3/31/2008#))
GROUP BY Monthly_Despatched.team;

Hi,

I currently have a table which holds the following:

Team Amount Date
------ --------- -----
Blue 100 01/01/08
Red 200 01/01/08
Green 500 01/01/08
Yellow 50 01/01/08

Team Amount Date
------ --------- -----
Blue 100 02/01/08
Red 200 02/01/08
Green 500 02/01/08
Yellow 50 02/01/08


I am able to show these on a graph on a daily basis, how can show sum the
Amount for each team as the days go by so i can show them on a graph for a
whole month. So i would have something like this:

Team Amount
------ ---------
Blue 200
Red 400
Green 1000
Yellow 100

Well, you certainly wouldn't want to store this sum in any table, but you can
calculate it on demand. Make it a Totals query; group by Color and sum Amount,
and use a criterion on the date field (which, by the way, you should rename;
Date is a reserved word). Try

SELECT [Team], Sum([Amount])
FROM yourtable
WHERE [Date] >= [Enter start date:] AND [Date]<= [Enter end date:]

 

[John W. Vinson]

As you can see my current data comes from multiple tables. So where can i
fit the query you suggest.

You can base your totals query on a Table.

You can also base your totals query on another Query.

If your linked SQL views give unacceptable performance, *DEMONSTRATED* not
assumed, then it may be worth the extra step of creating a local table, but
that's the only case I could make for doing so.

 

[John W. Vinson]

Hi John,

Here is my current query which shows me the correct infor for a number of
days. What i want to do is to also show data for a month that is a
comulative figure.

"a cumulative figure"?? cumulative over what, in what way? A running sum, a
grand total, or what?

SELECT Sum(dbo_salesitems.sprice) AS SumOfsprice, dbo_salesorders.srep,
dbo_delv.dtaxd, dbo_delv.dntflg
FROM (dbo_delv INNER JOIN dbo_delvitems ON dbo_delv.delvno =
dbo_delvitems.delvnoa) INNER JOIN (dbo_salesorders INNER JOIN dbo_salesitems
ON dbo_salesorders.son = dbo_salesitems.sona) ON (dbo_delvitems.ditem =
dbo_salesitems.sonitem) AND (dbo_delvitems.dord = dbo_salesorders.son)
GROUP BY dbo_salesorders.srep, dbo_delv.dtaxd, dbo_delv.dntflg
HAVING (((dbo_salesorders.srep)="blue" Or (dbo_salesorders.srep)="green" Or
(dbo_salesorders.srep)="red" Or (dbo_salesorders.srep)="yellow" Or
(dbo_salesorders.srep)="isolok") AND ((dbo_delv.dtaxd)>#3/1/2008#) AND
((dbo_delv.dntflg)="Y"))
ORDER BY dbo_salesorders.srep, dbo_delv.dtaxd;


I have created another table called monthly_despatched and insert the data
from the above query into this table and then i used your query as below.
However i dont get an output. What am i doing wrong.

SELECT Monthly_Despatched.team, Sum(Monthly_Despatched.amount) AS SumOfamount
FROM Monthly_Despatched
WHERE (((Monthly_Despatched.sdate)>=#3/1/2008# And
(Monthly_Despatched.sdate)=#3/31/2008#))

The line above should have <= rather than = - you're showing only data from
the 31st.