This one is a tough one...

[markx]

Hi guys,

May I have your help on this?



In my database I have a list of errors occurring since inception.

For every "case" (field 1, not a unique key), I can have "1" to "n" errors
(every error takes one record, so if there are 5 errors, the case will have
5 different records).

There are about 200 different errors possible.

There are about 35000 records within the database.



I would like to get out from this database the cases where the following
conditions are met:



Error "n1" occurs only once

AND/OR

Error "n2" occurs only once



(it means that the errors "n1" and "n2" can occur together, but every of
them only once)



AND

The errors listed above are the only ones that occur (i.e. there are no
other errors within the case, like "n3", "n4" etc.)



(The quantity of records for every case should be then less or equal 2, and
it should equal the sum of frequency of errors n1, n2, given that every of
these two errors can occur not more than once)



To put it simplier, I want the following conditions met:



n1 + n2 = total errors (eT)

n1 =< 1

n2 =< 1

eT >0

eT =<2



Sorry if I'm not clear in my explanations, I can always precise my question
if needed.

Thanks in advance for any helpful hints and comments!



mark

 

[Michel Walsh]

Hi,


SELECT case, errorNumber
FROM myTable
WHERE errorNumber = n1
GROUP BY case
HAVING COUNT(*)=1



or, graphically, bring your table, click on the summation button on the
toolbar, to get a new line, total, in the grid. Bring the field case in the
grid, keep the proposed GROUP BY. Bring the field ErrNumber in the grid,
change the GROUP BY to WHERE, add the desired error number you want in the
criteria line. Type COUNT(*) in the first line, change the GROUP BY to
Expression, add 1 in the criteria line.


Hoping it may help,
Vanderghast, Access MVP

 

[markx]

Forgive me if I misinterpret the query you suggested (probably I've
committed somewhere an error I'm not aware of), but the query

SELECT Sheet1.Case, Count(*) AS Expr1
FROM Sheet1
WHERE (((Sheet1.Error)=50))
GROUP BY Sheet1.Case
HAVING (((Count(*))=1));

gives me simply the cases where error 50 appear once, no matter how many
other errors are in the same case.
Besides that, it doesn't list the cases where I have one error n1 (=50 in my
case), one error n2 (the other one, =60 in my case) and no other errors.

Could you tell me where have I done wrong?
Thanks,
Mark

 

[Michel Walsh]

Hi,

The query was designed to return the case where error 50 occurred JUST ONCE,
for that case, indeed, irrelevant of any other error having arrived, or not,
for that case.

You want case where ONLY error 50 occurred, eventually many time? Then, try

SELECT DISTINCT case
FROM sheet1 As a
WHERE 50 = ALL( SELECT b.error
FROM sheet1 As b
WHERE b.case = a.case)


That won't list cases where error 50 and error 55 did occurred, for that
case.

SELECT DISTINCT case
FROM sheet1 As a
WHERE 50 = ALL( SELECT b.error
FROM sheet1 As b
WHERE b.case = a.case)
OR
60 = ALL( SELECT b.error
FROM sheet1 As b
WHERE b.case = a.case)



will list those cases where only error 50 occurred, or where only error 60
occurred, but NOT those where no other error code than in (50, 60) occurred.
IE. either all errors are 50 or all errors codes are all 60, but not a mix
of 50 and 60.


If you want cases where error codes are only a mix of 50 or of 60, and no
other error code, I would try:


SELECT DISTINCT case
FROM sheet1 As a
WHERE NOT EXISTS( SELECT *
FROM sheet1 As b
WHERE b.case = a.case
AND b.error NOT IN(50, 60) )




Is it what you expected? or there is still a problem?



Hoping it may help,
Vanderghast, Access MVP

 

[markx]

Many thanks Mike!
.... and sorry for bothering you for so little..

In fact, I'm looking for a combination of 3 of your queries)
Let me give you an exemple (hope it will not exhaust you:-D):
------------
Case Error
0001 50
0001 50
0001 55
....
Don't want this case, because 50 appears more than once

0002 60
0002 60
....
Don't want this case, because 60 appears more than once

0003 50
0003 55
Don't want this case (even if 50 appears only once), because there are also
another errors in this case

THE FOLLOWING CASES (0004, 0005 and 0006) ARE OK:
0004 50
0005 60
Want to extract both cases (0004 and 0005), because the errors 50 (and 60,
respectively) are the only errors in these cases, and they appear only once

0006 50
0006 60
Would like to extract also this case, because 50 and 60 appear only once,
and they are the only errors in case no 0006 (there are no more records for
this case)

BUT the case 0007 (below) would NOT be o.k.:
0007 50
0007 60
0007 50
.... because 50 appears more than once.
-------------
So, the following conditions must be satisfied:
"50" no more than once
"60" no more than once
and no other errors (i.e. "55") in the case.

Is there any possibility to combine these three conditions in one query, or
do I have to make several queries?
Once again, I appreciate very much your input!
Cheers,
Mark

 

[Michel Walsh]

The query:

SELECT DISTINCT case
FROM sheet1 As a
WHERE 50 = ALL( SELECT b.error
FROM sheet1 As b
WHERE b.case = a.case)
OR
60 = ALL( SELECT b.error
FROM sheet1 As b
WHERE b.case = a.case)


if cases of just one error, either 50 either 60, can be expanded to an
arbitrary large number of error codes like this:

SELECT DISTINCT case
FROM sheet1 As a
WHERE a.error = ALL( SELECT b.error
FROM sheet1 As b
WHERE b.case = a.case)
AND a.error IN( 50, 60, 70 )


as example. In fact, it may be preferable (for speed of execution) to write
the IN list criteria before the =ALL criteria.




Hoping it may help,
Vanderghast, Access MVP

 

[Michel Walsh]

Hi,


The first solution that comes to mind is through a crosstab...

TRANSFORM COUNT(*)
SELECT case
FROM caseError
GROUP BY case
PIVOT SWITCH(ErrorCode=50,"V50",ErrorCode=60,"V60",True,"Other");


Saved under the name XTerrorCode, the query we need is then:


SELECT *
FROM XTErrorCode
WHERE Other Is Null
AND Nz(V50,0)<=1
AND Nz(V60,0)<=1;


The last query states the logic: no other error code than 50 or 60, and
each of these error code should not appear more than once.



Hoping it may help,
Vanderghast, Access MVP

 

[markx]

Thanks Mike for what seems to be a brilliant solution!
I will try to understand how it works (probably it will take me at least 2
weeks, given my current level of SQL :-D), and then, if I have still any
doubts, will get back to you.
Thanks a lot!