Needing Quer(y/ies) to Count and keep a running total

[digitalshepard]

Hi,

This is my first post, and I'm still new to Access (although not databases.)

I'm working with the table below:

MonthOpen ---- MonthClosed
4-2011 ---- 7-2011
4-2011 ---- 6-2011
4-2011 ---- 6-2011
4-2011 ----
5-2011 ---- 7-2011
4-2011 ----
5-2011 ----
5-2011
5-2011 ---- 6-2011
5-2011 ---- 6-2011
6-2011 ---- 6-2011
6-2011 ---- 6-2011
6-2011 ---- 6-2011
6-2011 ---- 6-2011
6-2011 ---- 6-2011
6-2011 ---- 6-2011
6-2011 ----
7-2011 ----
7-2011 ----
7-2011 ----
7-2011 ----
7-2011 ----

and would like to use a query/ies to get this dataset:
Month ---- OpenTotal ---- ClosedTotal ---- Backlog
4-2011 ---- 5 ---- 0 ---- 5
5-2011 ---- 5 ---- 0 ---- 10
6-2011 ---- 7 ---- 10 ---- 7
7-2011 ---- 5 ---- 2 ---- 10

I've had success with queries for grouping and counting, but then merging open and closed together by month with a 0 total for months 4&5 has been beyond my ability. I'm assuming if I can get the open and closed fields correct, the running backlog shouldn't be so difficult.

I want to take this data and graph it Access (I know, painful compared to Excel). My guess is I'll want to dump this data and dump it into a table would be easiest for that purpose.

Any suggestions?

 

[Taruz]

Hi..

A query in this structure, gives the desired result.. table_name instead, type the name of your table..

select 
           MonthOpen, 
           OpenTotal, 
           ClosedTotal, 
           (
             select sum(OpenTotal-ClosedTotal) from (
                       select MonthOpen, (
                           select count(MonthOpen) from table_name 
                                     where (MonthOpen)=(trz.MonthOpen)) as OpenTotal, 
                             (
                            select count(MonthClosed) from table_name 
                                      where (MonthClosed)=(trz.MonthOpen)) as ClosedTotal
                             from table_name as trz
                             group by  trz.MonthOpen) as tt2  
                                      where tt.MonthOpen>=tt2.MonthOpen ) as Backlog
            from (select MonthOpen, (
                            select count(MonthOpen) from table_name where (MonthOpen)=(trz.MonthOpen)) as OpenTotal, 
                            (
                            select count(MonthClosed) from table_name where (MonthClosed)=(trz.MonthOpen)) as ClosedTotal
from table_name as trz
group by trz.MonthOpen)  as tt

 

[digitalshepard]

That was amazing Taruz! I am studying the SQL you shared and am learning a lot from what you shared. Thank you so much!