DCount expression

  • Thread starter Thread starter Opal
  • Start date Start date
O

Opal

I have the following select query:

SELECT DISTINCT (Format([ProblemDate],"mmm"" '""yy")) AS DateOpen,
Count(Problem.ClosedDate) AS CountOfClosedDate,
Count(Problem.ProblemDate) AS CountOfProblemDate
FROM Problem
GROUP BY (Format([ProblemDate],"mmm"" '""yy"));

and I want to add an expression that will calculate the following:

previous month's (i.e. Jan) outstanding open problems plus February's
new problems opened minus February's problems closed and so on
so I would get a running number like:

O/S New closed Result
Jan 08 4 52 30 26
Feb 08 26 59 48 37
Mar 08 37 80 58 59

Can someone point me in the right direction to pull this together?
 
Use these two queries but you need to incorporate where ClosedDate Is Null
into the SQL in calculating the Closed and Results.
Problem_YrMn ---
SELECT Format([ProblemDate],"yyyymm") AS ProbYearMonth
FROM Problem
GROUP BY Format([ProblemDate],"yyyymm");

SELECT Problem_YrMn.ProbYearMonth,
Sum(IIf([ProbYearMonth]>Format([ProblemDate],"yyyymm") And
(Format([ClosedDate],"yyyymm")>=[ProbYearMonth]),1,0)) AS [O/S],
Sum(IIf(Format([ProblemDate],"yyyymm")=[ProbYearMonth],1,0)) AS New,
Sum(IIf([ProbYearMonth]=Format([ClosedDate],"yyyymm"),1,0)) AS Closed,
Sum((IIf(Format([ProblemDate],"yyyymm")=[ProbYearMonth],1,0)+IIf([ProbYearMonth]>Format([ProblemDate],"yyyymm")
And
(Format([ClosedDate],"yyyymm")>=[ProbYearMonth]),1,0))-IIf([ProbYearMonth]=Format([ClosedDate],"yyyymm"),1,0)) AS Results
FROM Problem, Problem_YrMn
GROUP BY Problem_YrMn.ProbYearMonth
ORDER BY Problem_YrMn.ProbYearMonth;
 
Use these two queries but you need to incorporate where ClosedDate Is Null
into the SQL in calculating the Closed and Results.
    Problem_YrMn ---
SELECT Format([ProblemDate],"yyyymm") AS ProbYearMonth
FROM Problem
GROUP BY Format([ProblemDate],"yyyymm");

SELECT Problem_YrMn.ProbYearMonth,
Sum(IIf([ProbYearMonth]>Format([ProblemDate],"yyyymm") And
(Format([ClosedDate],"yyyymm")>=[ProbYearMonth]),1,0)) AS [O/S],
Sum(IIf(Format([ProblemDate],"yyyymm")=[ProbYearMonth],1,0)) AS New,
Sum(IIf([ProbYearMonth]=Format([ClosedDate],"yyyymm"),1,0)) AS Closed,
Sum((IIf(Format([ProblemDate],"yyyymm")=[ProbYearMonth],1,0)+IIf([ProbYearM­onth]>Format([ProblemDate],"yyyymm")
And
(Format([ClosedDate],"yyyymm")>=[ProbYearMonth]),1,0))-IIf([ProbYearMonth]=­Format([ClosedDate],"yyyymm"),1,0)) AS Results
FROM Problem, Problem_YrMn
GROUP BY Problem_YrMn.ProbYearMonth
ORDER BY Problem_YrMn.ProbYearMonth;

--
KARL DEWEY
Build a little - Test a little



Opal said:
I have the following select query:
Click to expand...
SELECT DISTINCT (Format([ProblemDate],"mmm"" '""yy")) AS DateOpen,
Count(Problem.ClosedDate) AS CountOfClosedDate,
Count(Problem.ProblemDate) AS CountOfProblemDate
FROM Problem
GROUP BY (Format([ProblemDate],"mmm"" '""yy"));
Click to expand...
and I want to add an expression that will calculate the following:
Click to expand...
previous month's (i.e. Jan) outstanding open problems plus February's
new problems opened minus February's problems closed and so on
so I would get a running number like:
Click to expand...
               O/S      New      closed      Result
Jan 08       4          52           30            26
Feb 08      26         59           48            37
Mar 08      37         80           58            59
Click to expand...
Can someone point me in the right direction to pull this together?- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Thank you Karl, that works great!
 
Use these two queries but you need to incorporate where ClosedDate Is Null
into the SQL in calculating the Closed and Results.
    Problem_YrMn ---
SELECT Format([ProblemDate],"yyyymm") AS ProbYearMonth
FROM Problem
GROUP BY Format([ProblemDate],"yyyymm");
Click to expand...
SELECT Problem_YrMn.ProbYearMonth,
Sum(IIf([ProbYearMonth]>Format([ProblemDate],"yyyymm") And
(Format([ClosedDate],"yyyymm")>=[ProbYearMonth]),1,0)) AS [O/S],
Sum(IIf(Format([ProblemDate],"yyyymm")=[ProbYearMonth],1,0)) AS New,
Sum(IIf([ProbYearMonth]=Format([ClosedDate],"yyyymm"),1,0)) AS Closed,
Sum((IIf(Format([ProblemDate],"yyyymm")=[ProbYearMonth],1,0)+IIf([ProbYearM­­onth]>Format([ProblemDate],"yyyymm")
And
(Format([ClosedDate],"yyyymm")>=[ProbYearMonth]),1,0))-IIf([ProbYearMonth]=­­Format([ClosedDate],"yyyymm"),1,0)) AS Results
FROM Problem, Problem_YrMn
GROUP BY Problem_YrMn.ProbYearMonth
ORDER BY Problem_YrMn.ProbYearMonth;
Click to expand...
Click to expand...

Karl, just having a problem with:

Sum((IIf(Format([ProblemDate],"yyyymm")=[ProbYearMonth],
1,0)+IIf([ProbYearM­onth]>Format([ProblemDate],"yyyymm")
And
(Format([ClosedDate],"yyyymm")>=[ProbYearMonth]),1,0))-
IIf([ProbYearMonth]=­Format([ClosedDate],"yyyymm"),1,0)) AS Results

I get the logic of the statement but I am not getting the desired
results...

I have traced the data as I was previously exporting the table to
excel and running
the calculations there, but want to keep it all in access. It starts
to skew after a couple
of months. I have data from January to September

Query results for above statement Correct data
0 0
2 2
11 11
11 12
22
23
43 44
42 55
58 75
60 100

Can you suppose why this might be happening?
 
I do not see that you incorporated anything to handle the null ClosedDate
records as I said in my post.
--
KARL DEWEY
Build a little - Test a little


Opal said:
Use these two queries but you need to incorporate where ClosedDate Is Null
into the SQL in calculating the Closed and Results.
Problem_YrMn ---
SELECT Format([ProblemDate],"yyyymm") AS ProbYearMonth
FROM Problem
GROUP BY Format([ProblemDate],"yyyymm");
Click to expand...
SELECT Problem_YrMn.ProbYearMonth,
Sum(IIf([ProbYearMonth]>Format([ProblemDate],"yyyymm") And
(Format([ClosedDate],"yyyymm")>=[ProbYearMonth]),1,0)) AS [O/S],
Sum(IIf(Format([ProblemDate],"yyyymm")=[ProbYearMonth],1,0)) AS New,
Sum(IIf([ProbYearMonth]=Format([ClosedDate],"yyyymm"),1,0)) AS Closed,
Sum((IIf(Format([ProblemDate],"yyyymm")=[ProbYearMonth],1,0)+IIf([ProbYearM­­onth]>Format([ProblemDate],"yyyymm")
And
(Format([ClosedDate],"yyyymm")>=[ProbYearMonth]),1,0))-IIf([ProbYearMonth]=­­Format([ClosedDate],"yyyymm"),1,0)) AS Results
FROM Problem, Problem_YrMn
GROUP BY Problem_YrMn.ProbYearMonth
ORDER BY Problem_YrMn.ProbYearMonth;
Click to expand...
Click to expand...

Karl, just having a problem with:

Sum((IIf(Format([ProblemDate],"yyyymm")=[ProbYearMonth],
1,0)+IIf([ProbYearM­onth]>Format([ProblemDate],"yyyymm")
And
(Format([ClosedDate],"yyyymm")>=[ProbYearMonth]),1,0))-
IIf([ProbYearMonth]=­Format([ClosedDate],"yyyymm"),1,0)) AS Results

I get the logic of the statement but I am not getting the desired
results...

I have traced the data as I was previously exporting the table to
excel and running
the calculations there, but want to keep it all in access. It starts
to skew after a couple
of months. I have data from January to September

Query results for above statement Correct data
0 0
2 2
11 11
11 12
22
23
43 44
42 55
58 75
60 100

Can you suppose why this might be happening?
Click to expand...
 
I do not see that you incorporated anything to handle the null ClosedDate
records as I said in my post.
--
KARL DEWEY
Build a little - Test a little



Opal said:
On Sep 29, 6:33 pm, KARL DEWEY <[email protected]>
wrote:
Use these two queries but you need to incorporate where ClosedDate Is Null
into the SQL in calculating the Closed and Results.
    Problem_YrMn ---
SELECT Format([ProblemDate],"yyyymm") AS ProbYearMonth
FROM Problem
GROUP BY Format([ProblemDate],"yyyymm");
SELECT Problem_YrMn.ProbYearMonth,
Sum(IIf([ProbYearMonth]>Format([ProblemDate],"yyyymm") And
(Format([ClosedDate],"yyyymm")>=[ProbYearMonth]),1,0)) AS [O/S],
Sum(IIf(Format([ProblemDate],"yyyymm")=[ProbYearMonth],1,0)) AS New,
Sum(IIf([ProbYearMonth]=Format([ClosedDate],"yyyymm"),1,0)) AS Closed,
Sum((IIf(Format([ProblemDate],"yyyymm")=[ProbYearMonth],1,0)+IIf([ProbYearM­­­onth]>Format([ProblemDate],"yyyymm")
And
(Format([ClosedDate],"yyyymm")>=[ProbYearMonth]),1,0))-IIf([ProbYearMonth]=­­­Format([ClosedDate],"yyyymm"),1,0)) AS Results
FROM Problem, Problem_YrMn
GROUP BY Problem_YrMn.ProbYearMonth
ORDER BY Problem_YrMn.ProbYearMonth;
Click to expand...
Click to expand...
Karl, just having a problem with:
Click to expand...
Sum((IIf(Format([ProblemDate],"yyyymm")=[ProbYearMonth],
1,0)+IIf([ProbYearM­onth]>Format([ProblemDate],"yyyymm")
And
(Format([ClosedDate],"yyyymm")>=[ProbYearMonth]),1,0))-
IIf([ProbYearMonth]=­Format([ClosedDate],"yyyymm"),1,0)) AS Results
Click to expand...
I get the logic of the statement but I am not getting the desired
results...
Click to expand...
I have traced the data as I was previously exporting the table to
excel and running
the calculations there, but want to keep it all in access.  It starts
to skew after a couple
of months.  I have data from January to September
Click to expand...
Query results for above statement       Correct data
0                                                            0
2                                                            2
11                                                         11
11                                                         12
22
23
43                                                         44
42                                                         55
58                                                         75
60                                                        100
Click to expand...
Can you suppose why this might be happening?- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

But where closed date is Null, the record would be open....Sorry, you
confused me?
 
Let us back up. What is the Result column to contain? I thought it would
be the number that would appear in the following month O/S column.

--
KARL DEWEY
Build a little - Test a little


Opal said:
I do not see that you incorporated anything to handle the null ClosedDate
records as I said in my post.
--
KARL DEWEY
Build a little - Test a little



Opal said:
On Sep 29, 6:33 pm, KARL DEWEY <[email protected]>
wrote:
Click to expand...
Use these two queries but you need to incorporate where ClosedDate Is Null
into the SQL in calculating the Closed and Results.
Problem_YrMn ---
SELECT Format([ProblemDate],"yyyymm") AS ProbYearMonth
FROM Problem
GROUP BY Format([ProblemDate],"yyyymm");
Click to expand...
SELECT Problem_YrMn.ProbYearMonth,
Sum(IIf([ProbYearMonth]>Format([ProblemDate],"yyyymm") And
(Format([ClosedDate],"yyyymm")>=[ProbYearMonth]),1,0)) AS [O/S],
Sum(IIf(Format([ProblemDate],"yyyymm")=[ProbYearMonth],1,0)) AS New,
Sum(IIf([ProbYearMonth]=Format([ClosedDate],"yyyymm"),1,0)) AS Closed,
Sum((IIf(Format([ProblemDate],"yyyymm")=[ProbYearMonth],1,0)+IIf([ProbYearM­­­onth]>Format([ProblemDate],"yyyymm")
And
(Format([ClosedDate],"yyyymm")>=[ProbYearMonth]),1,0))-IIf([ProbYearMonth]=­­­Format([ClosedDate],"yyyymm"),1,0)) AS Results
FROM Problem, Problem_YrMn
GROUP BY Problem_YrMn.ProbYearMonth
ORDER BY Problem_YrMn.ProbYearMonth;
Click to expand...
Karl, just having a problem with:
Click to expand...
Sum((IIf(Format([ProblemDate],"yyyymm")=[ProbYearMonth],
1,0)+IIf([ProbYearM­onth]>Format([ProblemDate],"yyyymm")
And
(Format([ClosedDate],"yyyymm")>=[ProbYearMonth]),1,0))-
IIf([ProbYearMonth]=­Format([ClosedDate],"yyyymm"),1,0)) AS Results
Click to expand...
I get the logic of the statement but I am not getting the desired
results...
Click to expand...
I have traced the data as I was previously exporting the table to
excel and running
the calculations there, but want to keep it all in access. It starts
to skew after a couple
of months. I have data from January to September
Click to expand...
Query results for above statement Correct data
0 0
2 2
11 11
11 12
22
23
43 44
42 55
58 75
60 100
Click to expand...
Can you suppose why this might be happening?- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

But where closed date is Null, the record would be open....Sorry, you
confused me?
Click to expand...
 
Let us back up.   What is the Result column to contain?  I thought itwould
be the number that would appear in the following month O/S column.

--
KARL DEWEY
Build a little - Test a little



Opal said:
I do not see that you incorporated anything to handle the null ClosedDate
records as I said in my post.
--
KARL DEWEY
Build a little - Test a little
:
On Sep 29, 6:33 pm, KARL DEWEY <[email protected]>
wrote:
Use these two queries but you need to incorporate where ClosedDate Is Null
into the SQL in calculating the Closed and Results.
    Problem_YrMn ---
SELECT Format([ProblemDate],"yyyymm") AS ProbYearMonth
FROM Problem
GROUP BY Format([ProblemDate],"yyyymm");
SELECT Problem_YrMn.ProbYearMonth,
Sum(IIf([ProbYearMonth]>Format([ProblemDate],"yyyymm") And
(Format([ClosedDate],"yyyymm")>=[ProbYearMonth]),1,0)) AS [O/S],
Sum(IIf(Format([ProblemDate],"yyyymm")=[ProbYearMonth],1,0)) AS New,
Sum(IIf([ProbYearMonth]=Format([ClosedDate],"yyyymm"),1,0)) AS Closed,
Sum((IIf(Format([ProblemDate],"yyyymm")=[ProbYearMonth],1,0)+IIf([ProbYearM­­­­onth]>Format([ProblemDate],"yyyymm")
And
(Format([ClosedDate],"yyyymm")>=[ProbYearMonth]),1,0))-IIf([ProbYearMonth]=­­­­Format([ClosedDate],"yyyymm"),1,0)) AS Results
FROM Problem, Problem_YrMn
GROUP BY Problem_YrMn.ProbYearMonth
ORDER BY Problem_YrMn.ProbYearMonth;
--
KARL DEWEY
Build a little - Test a little
Karl, just having a problem with:
Sum((IIf(Format([ProblemDate],"yyyymm")=[ProbYearMonth],
1,0)+IIf([ProbYearM­onth]>Format([ProblemDate],"yyyymm")
And
(Format([ClosedDate],"yyyymm")>=[ProbYearMonth]),1,0))-
IIf([ProbYearMonth]=­Format([ClosedDate],"yyyymm"),1,0)) AS Results
I get the logic of the statement but I am not getting the desired
results...
I have traced the data as I was previously exporting the table to
excel and running
the calculations there, but want to keep it all in access.  It starts
to skew after a couple
of months.  I have data from January to September
Query results for above statement       Correct data
0                                                            0
2                                                            2
11                                                         11
11                                                         12
22
23
43                                                         44
42                                                         55
58                                                         75
60                                                        100
Can you suppose why this might be happening?- Hide quoted text -
- Show quoted text -
Click to expand...
Click to expand...
But where closed date is Null, the record would be open....Sorry, you
confused me?- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Okay,

The formula I use in excel to manually calculate this goes somthing
like this:

September for example:

# Opened (or new) is a count of a column:

=IF(COUNTIF($H:$H,"9"),COUNTIF($H:$H,"9"),"")

in other words the expression you gave as:

Sum(IIf(Format([ProblemDate],"yyyymm")=[ProbYearMonth],1,0)) AS New,

# Closed is expressed as follows:

=IF(SUMPRODUCT(($J$2:$J$599=9)*($E$2:$E$599=1)*1),SUMPRODUCT(($J$2:$J
$599=9)*($E$2:$E$599=1)*1),"")

in other words the expression you gave as:

Sum(IIf([ProbYearMonth]=Format([ClosedDate],"yyyymm"),1,0)) AS Closed

These values are expressed in cells W2 and W3 on my spreadsheet.

For the results field, the formula is: =(V5+W2)-W3 and is put in cell
W5

Where V5 is equal to the results from August.

Sorry if I confused you as well.....
 
in other words the expression you gave as:
Sum(IIf([ProbYearMonth]=Format([ClosedDate],"yyyymm"),1,0)) AS Closed
These values are expressed in cells W2 and W3 on my spreadsheet.
For the results field, the formula is: =(V5+W2)-W3 and is put in cell W5
Where V5 is equal to the results from August.
Sorry if I confused you as well.....
Click to expand...

I am as lost as ever. I do not want to know about your spreadsheet.
What is the Result column to contain?
Give me a mathmatical formula or boolean expression to work with.
 
in other words the expression you gave as:
Sum(IIf([ProbYearMonth]=Format([ClosedDate],"yyyymm"),1,0)) AS Closed
These values are expressed in cells W2 and W3 on my spreadsheet.
For the results field, the formula is:  =(V5+W2)-W3 and is put in cell W5
Where V5 is equal to the results from August.
Sorry if I confused you as well.....
Click to expand...

I am as lost as ever.   I do not want to know about your spreadsheet.
              What is the Result column to contain?    
       Give me a mathmatical formula or boolean expression to work with.
Click to expand...

Sorry about that... didn't mean to confuse you further.

The results column should contain the total number of the
open issues from the previous month plus all new issues
from the current month minus all closed issues from the
current month.
 
in other words the expression you gave as:
Sum(IIf([ProbYearMonth]=Format([ClosedDate],"yyyymm"),1,0)) AS Closed
These values are expressed in cells W2 and W3 on my spreadsheet.
For the results field, the formula is:  =(V5+W2)-W3 and is put in cell W5
Where V5 is equal to the results from August.
Sorry if I confused you as well.....
Click to expand...
Click to expand...
I am as lost as ever.   I do not want to know about your spreadsheet.
              What is the Result column to contain?    
       Give me a mathmatical formula or boolean expression to work with.
Click to expand...

Sorry about that... didn't mean to confuse you further.

The results column should contain the total number of the
open issues from thepreviousmonthplus all new issues
from the currentmonthminus all closed issues from the
currentmonth.
Click to expand...

Would it be something like:

Sum((IIf(Format([ProblemDate],"yyyymm")=[ProbYearMonth],1,0)+
IIf([ProbYearMonth]>Format([ProblemDate],"yyyymm") =
Format(DateAdd("m",-1,Date()),"yyyymm") And
(Format([ClosedDate],"yyyymm")>=[ProbYearMonth]),1,0))-
IIf([ProbYearMonth]=Format([ClosedDate],"yyyymm"),1,0)) =
Format(DateAdd("m",-1,Date()),"yyyymm") AS Results
 
Back
Top