New to complex charts & struggling

O

Opal

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

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")
 
O

Opal

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

Douglas J. Steele

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 MVP

(no private e-mails, please)


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

Opal

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)


You've got what's known as a cartesian product. Because you haven't
specified the relation between Problem and qryProblemYrMn, you're goingto
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")
news:c393599a-bb68-4a80-be23-b0b248c0d390@l62g2000hse.googlegroups.com....
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....
 
O

Opal

Did you try the SQL I already suggested? If so and it didn't give you what
you wanted, what was the error?
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 -
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 -

- Show 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!
 
D

Douglas J. Steele

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 MVP

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

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

Opal

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 -
- Show 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 -

Doug, I am not ignoring your advice. The problem with your SQL is
that the values for [# Closed] are not what I want. That statement
only gives me the problems that were opened and closed in October.
I want all problems closed in October, even if they were opened in
July or August or September. For example, there were 38 problems
closed in October to date, but the SQL statement you provided shows
only 10 were closed for October. Those 10 were also opened in October
the other 28 were opened in other months. I need the return value in
the query to be 38 for October and not 10.
 
D

Douglas J. Steele

You're right: that will only show you the count of problems closed in a
given month if they were opened in that same month.

I think you're going to need two separate queries: one to get the number
opened in the month, and one to get the number closed in the month.

SELECT Format([ProblemDate],"yyyymm") AS ProbYearMonth,
Count(*) AS [# Opened]
FROM Problem
GROUP BY Format([ProblemDate],"yyyymm")
ORDER BY Format([ProblemDate],"yyyymm")

SELECT Format([ClosedDate],"yyyymm") AS ProbYearMonth,
Count(*) AS [# Closed]
FROM Problem
GROUP BY Format([ClosedDate],"yyyymm")
ORDER BY Format([ClosedDate],"yyyymm")

Now, joining them could be tricky, as it's conceivable that you could have
months where no problems were opened, but some were closed, and vice versa.
If we assume that the first query is qryOpened and the second query is
qryClosed, you'd want:

SELECT qryOpen.ProbYearMonth, qryOpen.[# Opened], qryClosed.[# Closed]
FROM qryOpen LEFT JOIN qryClosed
ON qryOpen.ProbYearMonth = qryClosed.ProbYearMonth
UNION
SELECT qryClosed.ProbYearMonth, qryOpen.[# Opened], qryClosed.[# Closed]
FROM qryClosed LEFT JOIN qryOpen
ON qryOpen.ProbYearMonth = qryClosed.ProbYearMonth
ORDER BY 1

The fact that you're using UNION and not UNION ALL means that duplicate rows
will be eliminated.


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


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 -
- Show 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 -

Doug, I am not ignoring your advice. The problem with your SQL is
that the values for [# Closed] are not what I want. That statement
only gives me the problems that were opened and closed in October.
I want all problems closed in October, even if they were opened in
July or August or September. For example, there were 38 problems
closed in October to date, but the SQL statement you provided shows
only 10 were closed for October. Those 10 were also opened in October
the other 28 were opened in other months. I need the return value in
the query to be 38 for October and not 10.
 
O

Opal

You're right: that will only show you the count of problems closed in a
given month if they were opened in that same month.

I think you're going to need two separate queries: one to get the number
opened in the month, and one to get the number closed in the month.

SELECT Format([ProblemDate],"yyyymm") AS ProbYearMonth,
Count(*) AS [# Opened]
FROM Problem
GROUP BY Format([ProblemDate],"yyyymm")
ORDER BY Format([ProblemDate],"yyyymm")

SELECT Format([ClosedDate],"yyyymm") AS ProbYearMonth,
Count(*) AS [# Closed]
FROM Problem
GROUP BY Format([ClosedDate],"yyyymm")
ORDER BY Format([ClosedDate],"yyyymm")

Now, joining them could be tricky, as it's conceivable that you could have
months where no problems were opened, but some were closed, and vice versa.
If we assume that the first query is qryOpened and the second query is
qryClosed, you'd want:

SELECT qryOpen.ProbYearMonth, qryOpen.[# Opened], qryClosed.[# Closed]
FROM qryOpen LEFT JOIN qryClosed
ON qryOpen.ProbYearMonth = qryClosed.ProbYearMonth
UNION
SELECT qryClosed.ProbYearMonth, qryOpen.[# Opened], qryClosed.[# Closed]
FROM qryClosed LEFT JOIN qryOpen
ON qryOpen.ProbYearMonth = qryClosed.ProbYearMonth
ORDER BY 1

The fact that you're using UNION and not UNION ALL means that duplicate rows
will be eliminated.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no private e-mails, please)


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)
"Opal" <[email protected]> wrote in message
On Oct 18, 4:27 pm, "Douglas J. Steele"
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 -
- Show 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 -

Doug, I am not ignoring your advice.  The problem with your SQL is
that the values for [# Closed] are not what I want.  That statement
only gives me the problems that were opened and closed in October.
I want all problems closed in October, even if they were opened in
July or August or September.  For example, there were 38 problems
closed in October to date, but the SQL statement you provided shows
only 10 were closed for October.  Those 10 were also opened in October
the other 28 were opened in other months.  I need the return value in
the query to be 38 for October and not 10.- Hide quoted text -

- Show quoted text -

Thanks Doug and sorry for the delay in responding...things have gotten
hectic
and this is the first chance I have had to get back at this....it
looks good,
but....I have 92 "problems" that are not closed yet so I have one line
in the
query result with nothing in the "ProbYearMonth" field. Should this
be handled
with an IIF statement? Should I be able to use the same logic to
bring in
the target values? And can you point me in the right direction to
write the statement that will produce a running figure on all open
problems by month?
 
D

Douglas J. Steele

If ClosedDate contains Null for those problems that aren't closed, you could
try:

SELECT Format(Nz([ClosedDate], Date),"yyyymm") AS ProbYearMonth,
Count(*) AS [# Closed]
FROM Problem
GROUP BY Format(Nz([ClosedDate], Date),"yyyymm")
ORDER BY Format(Nz([ClosedDate], Date),"yyyymm")

which will treat the unclosed entries as this month's, or

SELECT IIf(IsNull([ClosedDate], "Open", Format([ClosedDate],"yyyymm")) AS
ProbYearMonth,
Count(*) AS [# Closed]
FROM Problem
GROUP BY IIf(IsNull([ClosedDate], "Open", Format([ClosedDate],"yyyymm"))
ORDER BY IIf(IsNull([ClosedDate], "Open", Format([ClosedDate],"yyyymm"))

which will put "Open" beside them.

As to "And can you point me in the right direction to write the statement
that will produce a running figure on all open problems by month?", you put
a text box on the report and set its ControlSource to =1. Go to the text
box's RunningSum property, and set it to either Over Group or Over All,
depending on which you want.

Not sure what you mean by "Should I be able to use the same logic to bring
in the target values?"


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


You're right: that will only show you the count of problems closed in a
given month if they were opened in that same month.

I think you're going to need two separate queries: one to get the number
opened in the month, and one to get the number closed in the month.

SELECT Format([ProblemDate],"yyyymm") AS ProbYearMonth,
Count(*) AS [# Opened]
FROM Problem
GROUP BY Format([ProblemDate],"yyyymm")
ORDER BY Format([ProblemDate],"yyyymm")

SELECT Format([ClosedDate],"yyyymm") AS ProbYearMonth,
Count(*) AS [# Closed]
FROM Problem
GROUP BY Format([ClosedDate],"yyyymm")
ORDER BY Format([ClosedDate],"yyyymm")

Now, joining them could be tricky, as it's conceivable that you could have
months where no problems were opened, but some were closed, and vice
versa.
If we assume that the first query is qryOpened and the second query is
qryClosed, you'd want:

SELECT qryOpen.ProbYearMonth, qryOpen.[# Opened], qryClosed.[# Closed]
FROM qryOpen LEFT JOIN qryClosed
ON qryOpen.ProbYearMonth = qryClosed.ProbYearMonth
UNION
SELECT qryClosed.ProbYearMonth, qryOpen.[# Opened], qryClosed.[# Closed]
FROM qryClosed LEFT JOIN qryOpen
ON qryOpen.ProbYearMonth = qryClosed.ProbYearMonth
ORDER BY 1

The fact that you're using UNION and not UNION ALL means that duplicate
rows
will be eliminated.

Thanks Doug and sorry for the delay in responding...things have gotten
hectic
and this is the first chance I have had to get back at this....it
looks good,
but....I have 92 "problems" that are not closed yet so I have one line
in the
query result with nothing in the "ProbYearMonth" field. Should this
be handled
with an IIF statement? Should I be able to use the same logic to
bring in
the target values? And can you point me in the right direction to
write the statement that will produce a running figure on all open
problems by month?
 
O

Opal

If ClosedDate contains Null for those problems that aren't closed, you could
try:

SELECT Format(Nz([ClosedDate], Date),"yyyymm") AS ProbYearMonth,
Count(*) AS [# Closed]
FROM Problem
GROUP BY Format(Nz([ClosedDate], Date),"yyyymm")
ORDER BY Format(Nz([ClosedDate], Date),"yyyymm")

which will treat the unclosed entries as this month's, or

SELECT IIf(IsNull([ClosedDate], "Open", Format([ClosedDate],"yyyymm")) AS
ProbYearMonth,
Count(*) AS [# Closed]
FROM Problem
GROUP BY IIf(IsNull([ClosedDate], "Open", Format([ClosedDate],"yyyymm"))
ORDER BY IIf(IsNull([ClosedDate], "Open", Format([ClosedDate],"yyyymm"))

which will put "Open" beside them.

As to "And can you point me in the right direction to write the statement
that will produce a running figure on all open problems by month?", you put
a text box on the report and set its ControlSource to =1. Go to the text
box's RunningSum property, and set it to either Over Group or Over All,
depending on which you want.

Not sure what you mean by "Should I be able to use the same logic to bring
in the target values?"

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no private e-mails, please)


You're right: that will only show you the count of problems closed in a
given month if they were opened in that same month.
I think you're going to need two separate queries: one to get the number
opened in the month, and one to get the number closed in the month.
SELECT Format([ProblemDate],"yyyymm") AS ProbYearMonth,
Count(*) AS [# Opened]
FROM Problem
GROUP BY Format([ProblemDate],"yyyymm")
ORDER BY Format([ProblemDate],"yyyymm")
SELECT Format([ClosedDate],"yyyymm") AS ProbYearMonth,
Count(*) AS [# Closed]
FROM Problem
GROUP BY Format([ClosedDate],"yyyymm")
ORDER BY Format([ClosedDate],"yyyymm")
Now, joining them could be tricky, as it's conceivable that you could have
months where no problems were opened, but some were closed, and vice
versa.
If we assume that the first query is qryOpened and the second query is
qryClosed, you'd want:
SELECT qryOpen.ProbYearMonth, qryOpen.[# Opened], qryClosed.[# Closed]
FROM qryOpen LEFT JOIN qryClosed
ON qryOpen.ProbYearMonth = qryClosed.ProbYearMonth
UNION
SELECT qryClosed.ProbYearMonth, qryOpen.[# Opened], qryClosed.[# Closed]
FROM qryClosed LEFT JOIN qryOpen
ON qryOpen.ProbYearMonth = qryClosed.ProbYearMonth
ORDER BY 1
The fact that you're using UNION and not UNION ALL means that duplicate
rows
will be eliminated.

Thanks Doug and sorry for the delay in responding...things have gotten
hectic
and this is the first chance I have had to get back at this....it
looks good,
but....I have 92 "problems" that are not closed yet so I have one line
in the
query result with nothing in the "ProbYearMonth" field.  Should this
be handled
with an IIF statement?  Should I be able to use the same logic to
bring in
the target values?  And can you point me in the right direction to
write the statement that will produce a running figure on all open
problems by month?- Hide quoted text -

- Show quoted text -

Hmmm...

SELECT Format(Nz([ClosedDate], Date),"yyyymm") AS ProbYearMonth,
Count(*) AS [# Closed]
FROM Problem
GROUP BY Format(Nz([ClosedDate], Date),"yyyymm")
ORDER BY Format(Nz([ClosedDate], Date),"yyyymm")

Is looking for a Date parameter and still shows me the 92 problems
that are not closed...

SELECT IIf(IsNull([ClosedDate], "Open", Format([ClosedDate],"yyyymm"))
AS
ProbYearMonth,
Count(*) AS [# Closed]
FROM Problem
GROUP BY IIf(IsNull([ClosedDate], "Open",
Format([ClosedDate],"yyyymm"))
ORDER BY IIf(IsNull([ClosedDate], "Open",
Format([ClosedDate],"yyyymm"))

has a syntax error....missing operator in expression....trying to
figure that one
out...

as for running total, since this was going to be in a chart, I was
hoping to
accomplish this in the query.... As for the target, I also have a
target per
month per group.....

As I suspected this is too convuluted for a query to handle....I am
working
on a transfer to excel and using the excel charting abilities to
handle it all
instead......thanks....
 

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

Running sum expression 2

Top