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

E

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!
 
C

CLR

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

Vaya con Dios,
Chuck, CABGx3


Erikka T said:
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.
 
J

Jamie Collins

Erikka T said:
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.

--
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top