Multi expression(different condition) in one queries

[mohsin via AccessMonster.com]

Hi there

My queries as below;

SELECT NetName, Date,Hour, Sum(Total) AS CG0, Sum(Total) AS CG1
FROM totalcc_hour
WHERE ((([CC])="000" And (([CC])="010" Or ([CC])="011" Or ([CC])="013" Or (
[CC])="015" Or ([CC])="017" Or ([CC])="021" Or ([CC])="022" Or ([CC])="024"
Or ([CC])="026" Or ([CC])="034" Or ([CC])="035" Or ([CC])="203" Or ([CC])
="204" Or ([CC])="206" Or ([CC])="00B")))
GROUP BY NetName,Date,Hour;

Here the CG0 only contain "000" - condition 1
and CG1="010" etc.. - condition2

The results of this queries is empty!!!..

It's i'm done the right way?!
or have to do per queries for different condition?

thank you for your help

 

[Michel Walsh]

if CC="000", then the same value CANNOT also be equal to either "010",
"011", ....


WHERE ((([CC])="000" And (([CC])="010" Or ([CC])="011" Or ...


You asked a given value be AND equal to "000" AND equal to something else...
which cannot, FOR THE SAME RECORD.



Hoping it may help,
Vanderghast, Access MVP

 

[mohsin via AccessMonster.com]

Thanks michel,

if CC="000", then the same value CANNOT also be equal to either "010",
"011", ....

WHERE ((([CC])="000" And (([CC])="010" Or ([CC])="011" Or ...

You asked a given value be AND equal to "000" AND equal to something else...
which cannot, FOR THE SAME RECORD.

Hoping it may help,
Vanderghast, Access MVP

Hi there

[quoted text clipped - 19 lines]

thank you for your help

 

[John Spencer]

Your where clause as postied is probably the problem. It says that CC
must be equal to "000" and at the same time CC must be equal to one of
the other values you have listed.

Perhaps you want CC to be equal to any of the values? Then you could use

WHERE CC in ("000","010","011","013","015",...,"00B")

Your text says something about

CG0 = "000" and CG1 = "010" which are Sums of a field.

Perhaps what you want is something like the following

SELECT NetName, [Date], [Hour]
, Sum(IIF(CC="000",Total,0) AS CG0
, Sum(IIF(CC<>"000",Total,0) AS CG1
FROM totalcc_hour
WHERE [CC] In ("000","010","011","013","015","017","021","022",
"024","026","034","035","203","204","206","00B")
GROUP BY NetName,[Date],[Hour];

By the way Date and Hour are both bad choices for field names since they
are both reserved words.


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[mohsin via AccessMonster.com]

Actually the "000","010","011" is a unique number which have a value, for
example, "000"=10000, "010"=1, "011"=3, and so on.
the value presented in field total. and the "000"... in field CC.
The objective here is to group the unique numbers and get the total. so the
result should be;

CG0 CG1
10000 4

Since this only an example of my grouping, I will try to used sum and if
function, and let see the result

Thanks John

Your where clause as postied is probably the problem. It says that CC
must be equal to "000" and at the same time CC must be equal to one of
the other values you have listed.

Perhaps you want CC to be equal to any of the values? Then you could use

WHERE CC in ("000","010","011","013","015",...,"00B")

Your text says something about

CG0 = "000" and CG1 = "010" which are Sums of a field.

Perhaps what you want is something like the following

SELECT NetName, [Date], [Hour]
, Sum(IIF(CC="000",Total,0) AS CG0
, Sum(IIF(CC<>"000",Total,0) AS CG1
FROM totalcc_hour
WHERE [CC] In ("000","010","011","013","015","017","021","022",
"024","026","034","035","203","204","206","00B")
GROUP BY NetName,[Date],[Hour];

By the way Date and Hour are both bad choices for field names since they
are both reserved words.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Hi there

[quoted text clipped - 17 lines]

thank you for your help

 

[mohsin via AccessMonster.com]

This is result of queries was different using sum and if, compare with using
where function

sum(iif(
======
SELECT qrtotalcc_hour.NetName, qrtotalcc_hour.Date, qrtotalcc_hour.Hour, Sum
(IIf([qrtotalcc_hour]![CC]="010" Or "011" Or "013" Or "015" Or "017" Or "021"
Or "022" Or "024" Or "026" Or "034" Or "035" Or "203" Or "204" Or "206" Or
"00B",[total],0)) AS CG1
FROM qrtotalcc_hour
GROUP BY qrtotalcc_hour.NetName, qrtotalcc_hour.Date, qrtotalcc_hour.Hour;

Where
=====
SELECT qrtotalcc_hour.NetName, qrtotalcc_hour.Date, qrtotalcc_hour.Hour, Sum
(qrtotalcc_hour!Total) AS CG1
FROM qrtotalcc_hour
WHERE (((qrtotalcc_hour!CC)="010" Or (qrtotalcc_hour!CC)="011" Or
(qrtotalcc_hour!CC)="013" Or (qrtotalcc_hour!CC)="015" Or (qrtotalcc_hour!CC)
="017" Or (qrtotalcc_hour!CC)="021" Or (qrtotalcc_hour!CC)="022" Or
(qrtotalcc_hour!CC)="024" Or (qrtotalcc_hour!CC)="026" Or (qrtotalcc_hour!CC)
="034" Or (qrtotalcc_hour!CC)="035" Or (qrtotalcc_hour!CC)="203" Or
(qrtotalcc_hour!CC)="204" Or (qrtotalcc_hour!CC)="206" Or (qrtotalcc_hour!CC)
="00B"))
GROUP BY qrtotalcc_hour.NetName, qrtotalcc_hour.Date, qrtotalcc_hour.Hour;


any hints why..

the CG0 is OK (if i compare between sum if and where function), since it's
have only one unique number. But for the CG1(and other's) have diffrent
unique numbers.

thank you

Actually the "000","010","011" is a unique number which have a value, for
example, "000"=10000, "010"=1, "011"=3, and so on.
the value presented in field total. and the "000"... in field CC.
The objective here is to group the unique numbers and get the total. so the
result should be;

CG0 CG1
10000 4

Since this only an example of my grouping, I will try to used sum and if
function, and let see the result

Thanks John

Your where clause as postied is probably the problem. It says that CC
must be equal to "000" and at the same time CC must be equal to one of

[quoted text clipped - 33 lines]

 

[John Spencer]

Your expression must read as follows. You have to do the comparison x =
y for each value.

, Sum
(IIf([qrtotalcc_hour]![CC]="010" Or [qrtotalcc_hour]![CC]="011" Or
[qrtotalcc_hour]![CC]="013" Or [qrtotalcc_hour]![CC]="015" Or
[qrtotalcc_hour]![CC]="017" Or [qrtotalcc_hour]![CC]="021"
Or [qrtotalcc_hour]![CC]="022" Or [qrtotalcc_hour]![CC]="024" Or
[qrtotalcc_hour]![CC]="026" Or [qrtotalcc_hour]![CC]="034" Or
[qrtotalcc_hour]![CC]="035" Or [qrtotalcc_hour]![CC]="203" Or
[qrtotalcc_hour]![CC]="204" Or [qrtotalcc_hour]![CC]="206" Or
[qrtotalcc_hour]![CC]="00B",[total],0)) AS CG1


You probably can shorten that by using the in operator as noted in
earlier posting.

By the way, did you try the earlier query I posted? Did it work? It
should have. The where clause restricted the returned values to only
the ones you were interested in and the IIF expression summed either the
records from those that had "000" or the others that had one of the
values in the where clause.

The method I posted should be faster than this second method.



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

This is result of queries was different using sum and if, compare with using
where function

sum(iif(
======
SELECT qrtotalcc_hour.NetName, qrtotalcc_hour.Date, qrtotalcc_hour.Hour, Sum
(IIf([qrtotalcc_hour]![CC]="010" Or "011" Or "013" Or "015" Or "017" Or "021"
Or "022" Or "024" Or "026" Or "034" Or "035" Or "203" Or "204" Or "206" Or
"00B",[total],0)) AS CG1
FROM qrtotalcc_hour
GROUP BY qrtotalcc_hour.NetName, qrtotalcc_hour.Date, qrtotalcc_hour.Hour;

Where
=====
SELECT qrtotalcc_hour.NetName, qrtotalcc_hour.Date, qrtotalcc_hour.Hour, Sum
(qrtotalcc_hour!Total) AS CG1
FROM qrtotalcc_hour
WHERE (((qrtotalcc_hour!CC)="010" Or (qrtotalcc_hour!CC)="011" Or
(qrtotalcc_hour!CC)="013" Or (qrtotalcc_hour!CC)="015" Or (qrtotalcc_hour!CC)
="017" Or (qrtotalcc_hour!CC)="021" Or (qrtotalcc_hour!CC)="022" Or
(qrtotalcc_hour!CC)="024" Or (qrtotalcc_hour!CC)="026" Or (qrtotalcc_hour!CC)
="034" Or (qrtotalcc_hour!CC)="035" Or (qrtotalcc_hour!CC)="203" Or
(qrtotalcc_hour!CC)="204" Or (qrtotalcc_hour!CC)="206" Or (qrtotalcc_hour!CC)
="00B"))
GROUP BY qrtotalcc_hour.NetName, qrtotalcc_hour.Date, qrtotalcc_hour.Hour;


any hints why..

the CG0 is OK (if i compare between sum if and where function), since it's
have only one unique number. But for the CG1(and other's) have diffrent
unique numbers.

thank you

Actually the "000","010","011" is a unique number which have a value, for
example, "000"=10000, "010"=1, "011"=3, and so on.
the value presented in field total. and the "000"... in field CC.
The objective here is to group the unique numbers and get the total. so the
result should be;

CG0 CG1
10000 4

Since this only an example of my grouping, I will try to used sum and if
function, and let see the result

Thanks John

Your where clause as postied is probably the problem. It says that CC
must be equal to "000" and at the same time CC must be equal to one of

[quoted text clipped - 33 lines]

thank you for your help

 

[mohsin via AccessMonster.com]

Hi

the <> is not applicable for my used, because i have other numbers as well to
get a different group..

Your expression must read as follows. You have to do the comparison x =
y for each value.

, Sum
(IIf([qrtotalcc_hour]![CC]="010" Or [qrtotalcc_hour]![CC]="011" Or
[qrtotalcc_hour]![CC]="013" Or [qrtotalcc_hour]![CC]="015" Or
[qrtotalcc_hour]![CC]="017" Or [qrtotalcc_hour]![CC]="021"
Or [qrtotalcc_hour]![CC]="022" Or [qrtotalcc_hour]![CC]="024" Or
[qrtotalcc_hour]![CC]="026" Or [qrtotalcc_hour]![CC]="034" Or
[qrtotalcc_hour]![CC]="035" Or [qrtotalcc_hour]![CC]="203" Or
[qrtotalcc_hour]![CC]="204" Or [qrtotalcc_hour]![CC]="206" Or
[qrtotalcc_hour]![CC]="00B",[total],0)) AS CG1

You probably can shorten that by using the in operator as noted in
earlier posting.

By the way, did you try the earlier query I posted? Did it work? It
should have. The where clause restricted the returned values to only
the ones you were interested in and the IIF expression summed either the
records from those that had "000" or the others that had one of the
values in the where clause.

The method I posted should be faster than this second method.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

This is result of queries was different using sum and if, compare with using
where function

[quoted text clipped - 48 lines]

[quoted text clipped - 33 lines]
thank you for your help