Need to add cells from column B to create new total in column D

[Erikka T]

This must be elementary, but I am new to VBA. I have a SQL query that returns data to my sheet that is grouped by status, and ordered by date. Here is an example of what is returned:
9/27/2003 17:00 1 Open-Propose
9/27/2003 17:00 1 Open-Review
9/27/2003 17:00 31 Open
10/4/2003 17:00 2 Open-Review
10/4/2003 17:00 38 Open
10/4/2003 17:00 4 Open-Propose

I need to create a chart so that for each given date, a total of all "Open*" is presented, because for charting purposes it doesn't matter what the status is, the overall count is what is important.

I want this returned:

9/27/2003 17:00 33
10/4/2003 17:00 44

I would like to write something that selects the sheet, and then compares the date cell to the next date cell and then total up all the numbers for that same date, and stick it in a new column so that I can chart it.

I suppose I could use a command button, but it would be better if the function could just run if the data on the sheet is changed.

Thanks!

 

[Frank Kabel]

Hi
a non-VBA solution: Use a pivot table for this. See:
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
http://www.ozgrid.com/Excel/excel-pivot-tables.htm

 

[CLR]

I would use the Data > Subtotals feature..........

Vaya con Dios,
Chuck, CABGx3

This must be elementary, but I am new to VBA. I have a SQL query that

returns data to my sheet that is grouped by status, and ordered by date.
Here is an example of what is returned:

9/27/2003 17:00 1 Open-Propose
9/27/2003 17:00 1 Open-Review
9/27/2003 17:00 31 Open
10/4/2003 17:00 2 Open-Review
10/4/2003 17:00 38 Open
10/4/2003 17:00 4 Open-Propose

I need to create a chart so that for each given date, a total of all

"Open*" is presented, because for charting purposes it doesn't matter what
the status is, the overall count is what is important.

I want this returned:

9/27/2003 17:00 33
10/4/2003 17:00 44

I would like to write something that selects the sheet, and then compares

the date cell to the next date cell and then total up all the numbers for
that same date, and stick it in a new column so that I can chart it.

I suppose I could use a command button, but it would be better if the

function could just run if the data on the sheet is changed.

 

[Jamie Collins]

This must be elementary, but I am new to VBA. I have a SQL query that returns data to my sheet that is grouped by status, and ordered by date. Here is an example of what is returned:
9/27/2003 17:00 1 Open-Propose
9/27/2003 17:00 1 Open-Review
9/27/2003 17:00 31 Open
10/4/2003 17:00 2 Open-Review
10/4/2003 17:00 38 Open
10/4/2003 17:00 4 Open-Propose

I need to create a chart so that for each given date, a total of all "Open*" is presented, because for charting purposes it doesn't matter what the status is, the overall count is what is important.

I want this returned:

9/27/2003 17:00 33
10/4/2003 17:00 44

I think this is the query you require:

SELECT
MyDateTime,
LEFT(MyStatus,4),
SUM(MyAmount) AS MyAmount
FROM
MyTable
GROUP BY
MyDateTime,
LEFT(MyStatus,4)
HAVING
LEFT(MyStatus,4)='Open'
ORDER BY MyDateTime
;

Jamie.

--