Why are you ignoring my advice? As I initially said, you've got a cartesian
product, so of course your calculations will be wrong.
There appears to be no reason why you need qryProblemYrMn
The only query you should need is:
SELECT Format([ProblemDate],"yyyymm") AS ProbYearMonth,
Count(*) AS [# Opened],
Sum(IIf(Format([ProblemDate], "yyyymm") = Format([ClosedDate], "yyyymm"), 1,
0)) AS [# Closed]
FROM Problem
GROUP BY Format([ProblemDate],"yyyymm")
ORDER BY Format([ProblemDate],"yyyymm")
(There was an error in my initial SQL: I had a extraneous closing
parenthesis that's been corrected above)
--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no private e-mails, please)
Did you try the SQL I already suggested? If so and it didn't give you
what
you wanted, what was the error?
--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no private e-mails, please)
On Oct 18, 8:11 am, "Douglas J. Steele"
You've got what's known as a cartesian product. Because you haven't
specified the relation between Problem and qryProblemYrMn, you're
going to
end up with the number of rows in Problem times the number of rows in
qryProblemYrMn.
I don't understand the point of qryProblemYrMn though.
I'm assuming that ProblemDate and ClosedDate are both fields in table
Problem. You should be able to use simply:
SELECT Format([ProblemDate],"yyyymm") AS ProbYearMonth,
Count(*) AS [# Opened],
Sum(IIf(Format([ProblemDate], "yyyymm") = Format([ClosedDate],
"yyyymm"),
1,
0)) AS [# Closed])
FROM Problem
GROUP BY Format([ProblemDate],"yyyymm")
ORDER BY Format([ProblemDate],"yyyymm")
--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no private e-mails, please)
I am trying to create a chart that shows not only open and closed
issues, but also a target line and can't quite get the query to
work the way I want.
I am using the following SQL statement to show the
# of Open and the # of Closed issues:
SELECT DISTINCTROW qryProblemYrMn.ProbYearMonth AS Expr1,
Sum(IIf(Format([ProblemDate],"yyyymm")=[ProbYearMonth],1,0)) AS[#
Opened],
Sum(IIf([ProbYearMonth]=Format([ClosedDate],"yyyymm"),1,0)) AS [#
Closed]
FROM Problem, qryProblemYrMn
GROUP BY qryProblemYrMn.ProbYearMonth
ORDER BY qryProblemYrMn.ProbYearMonth;
the qryProblemYrMn is the following:
SELECT Format([ProblemDate],"yyyymm") AS ProbYearMonth
FROM Problem
GROUP BY Format([ProblemDate],"yyyymm");
I have a table which shows the indvidual targets for each area
but I just can't seem to get the statement right. I end up with too
much data and the values in the # Open and # Closed get
corrupted:
Sum(IIf(Format([TargetMonth],"yyyymm")=[ProbYearMonth],
Nz([NWProd],0)+Nz([SWProd],0)+Nz([PreProd],0)+Nz([NWEng],0)+
Nz([SWEng],0)+Nz([PreEng],0)+Nz([NWMtc],0)+Nz([SWMtc],0)+
Nz([PreMtc],0)+Nz([EMatEng],0)+Nz([EMatProd],0),0)) AS TargetAll
Can anyone help out?- Hide quoted text -
- Show quoted text -
Hi Doug,
I am following someone else's suggestion in order to create count
of my data, but I think I will go back to the beginning and try again..
Unfortunately your suggestion, above, led to more errors.
I have a table where problems are collected. Each problem has
a ProblemDate (when entered) and once they are closed, they will
also have a ClosedDate. Not all problems have been closed. So
we need to track our progress. I need to be able to show
all Problems opened each month, all closed that month, a running
sum of open problems over the last 10 months as well as a target
line as each group is expected to input so many problems each month.
Then I need to be able to show this in a graph for the whole shop as
well
as filter graphs for each group within the shop. It has been quite a
task
and I am struggling to find some direction and feel I need to start
with the
queries. I had a query that ran like this for the count of opened and
closed:
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"));
Can you help point me in the right direction?- Hide quoted text -
- Show quoted text -
I am getting reserved word argument name missing or misspelled or
punctuation errors....trying to find out why....- Hide quoted text -
Okay got the punctuation problem, but having trouble [# Closed]
values - they are incorrect.
I am getting the count of the problems that were opened in and closed
in October as my total count for October only. I need all problems
that
were closed in October, even if they were opened in September or
August....
I need the data this way in order to track productivity. Another user
suggested that I create two queries:
"qryProbYearMonth"
SELECT Format([ProblemDate],"yyyymm") AS ProbYearMonth
FROM Problem
GROUP BY Format([ProblemDate],"yyyymm");
"qryProblem"
SELECT DISTINCTROW qryProblemYrMn.ProbYearMonth AS Expr1,
Sum(IIf(Format([ProblemDate],"yyyymm")=[ProbYearMonth],1,0)) AS [#
Opened],
Sum(IIf([ProbYearMonth]=Format([ClosedDate],"yyyymm"),1,0)) AS [#
Closed]
FROM Problem, qryProblemYrMn
GROUP BY qryProblemYrMn.ProbYearMonth
ORDER BY qryProblemYrMn.ProbYearMonth;
That worked fine for half my problem, but not for the second half,
including a target
line, a running sum of open problems and filtering the charts by shop
and group.
I am really stumped!- Hide quoted text -
- Show quoted text -
