Query for chart

[carlos]

Hi,

I have created a chart from chart wizard and modified it a
bit in the SQL design.
TRANSFORM Count(tblCAR.CAR) AS CountOfCAR
SELECT tblCAR.Owner
FROM tblCAR
GROUP BY tblCAR.Owner
PIVOT IIf([SignOffDate] Is Null,'Outstanding','Closed');

I want to present the bar chart in DESCENDING order by
CountOfCAR.

Any help, please advise. Thanks in advance.

Carlos

 

[Duane Hookom]

SELECT Owner, Sum(Abs(IsNull(SignOffDate))) As Outstanding,
Sum(IsNull(SignOffDate)+1) As Closed
FROM tblCar
GROUP BY Owner
ORDER BY Sum(Abs(IsNull(SignOffDate)));

 

[carlos]

Dear Duane

Excellent! I'm poor in math, explanation would be much
appreciated if you have spare time.

Thanks a lot.

Carlos

-----Original Message-----
SELECT Owner, Sum(Abs(IsNull(SignOffDate))) As Outstanding,
Sum(IsNull(SignOffDate)+1) As Closed
FROM tblCar
GROUP BY Owner
ORDER BY Sum(Abs(IsNull(SignOffDate)));

--
Duane Hookom
MS Access MVP

Hi,

I have created a chart from chart wizard and modified it a
bit in the SQL design.
TRANSFORM Count(tblCAR.CAR) AS CountOfCAR
SELECT tblCAR.Owner
FROM tblCAR
GROUP BY tblCAR.Owner
PIVOT IIf([SignOffDate] Is Null,'Outstanding','Closed');

I want to present the bar chart in DESCENDING order by
CountOfCAR.

Any help, please advise. Thanks in advance.

Carlos

.

 

[Duane Hookom]

IsNull(SignOffDate) returns -1 for Null values and 0 for non-null values.
This suggests that summing the value is similar to counting specific values.

--
Duane Hookom
MS Access MVP

Dear Duane

Excellent! I'm poor in math, explanation would be much
appreciated if you have spare time.

Thanks a lot.

Carlos

-----Original Message-----
SELECT Owner, Sum(Abs(IsNull(SignOffDate))) As Outstanding,
Sum(IsNull(SignOffDate)+1) As Closed
FROM tblCar
GROUP BY Owner
ORDER BY Sum(Abs(IsNull(SignOffDate)));

--
Duane Hookom
MS Access MVP

Hi,

I have created a chart from chart wizard and modified it a
bit in the SQL design.
TRANSFORM Count(tblCAR.CAR) AS CountOfCAR
SELECT tblCAR.Owner
FROM tblCAR
GROUP BY tblCAR.Owner
PIVOT IIf([SignOffDate] Is Null,'Outstanding','Closed');

I want to present the bar chart in DESCENDING order by
CountOfCAR.

Any help, please advise. Thanks in advance.

Carlos

.