DCount expression

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?
 
K

KARL DEWEY

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;
 
O

Opal

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

Opal

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?
 
K

KARL DEWEY

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...
 
O

Opal

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?
 
K

KARL DEWEY

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...
 
O

Opal

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.....
 
K

KARL DEWEY

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.
 
O

Opal

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.
 
O

Opal

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
 

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

Similar Threads

Sumproduct Problem 4
How to get the array like this? 6
SUMPRODUCT - MATCH CALL DETAILS 2 FIND RELEVANT EXTN - URGENT PLS! 0
TIME CALCULATIONS 2
Record Click on Error Indicator 2
who can help please with table query ? 1
SumProduct Help 1
Remove lines from text file prior to importing in Excel 2

Top