Access shut down after query

P

ppp

Hello,

I got a problem with this query - access starts some seconds and then it
hangs up (with a Message "found a failure, try to repair and restart").

SELECT T.NName, T.Strasse, T.Postleitzahl, T.Ort, T.Vorname FROM
Teilnehmer AS T WHERE
(((Exists (SELECT * FROM Teilnehmer WHERE Teilnehmer.NName = T.NName AND
Seminar_NR = S-60105-1))<>False) AND
((Exists (SELECT * FROM Teilnehmer WHERE Teilnehmer.NName = T.NName AND
Seminar_NR = S-60105-1))<>False) AND
((Exists (SELECT * FROM Teilnehmer WHERE Teilnehmer.NName = T.NName AND
Seminar_NR = S-60105-1))<>False) AND
((Exists (SELECT * FROM Teilnehmer WHERE Teilnehmer.NName = T.NName
AND Seminar_NR = S-60123-1))=False))
GROUP BY T.NName, T.Strasse, T.Postleitzahl, T.Ort, T.Vorname;

Maybe the query is too complex for Access? I´ve already tried to avoid
the "*" in the select-Statements, but it had no effect.
In addition to this problem I have to say that there should to be a lot
more of these Exists-Statements, these are just the test...

Thanks for some help!
 
D

Douglas J. Steele

It could be that your database has got some corruption. See the Corrupt
Microsoft Access MDBs FAQ that Tony Toews maintains at
http://www.granite.ab.ca/access/corruptmdbs.htm

Incidentally, your query is incorrect. Since Seminar_NR is a text field, you
need to put the value of the field in quotes:

SELECT T.NName, T.Strasse, T.Postleitzahl, T.Ort, T.Vorname FROM
Teilnehmer AS T WHERE
(((Exists (SELECT * FROM Teilnehmer WHERE Teilnehmer.NName = T.NName AND
Seminar_NR = 'S-60105-1'))<>False) AND
((Exists (SELECT * FROM Teilnehmer WHERE Teilnehmer.NName = T.NName AND
Seminar_NR = 'S-60105-1'))<>False) AND
((Exists (SELECT * FROM Teilnehmer WHERE Teilnehmer.NName = T.NName AND
Seminar_NR = 'S-60105-1'))<>False) AND
((Exists (SELECT * FROM Teilnehmer WHERE Teilnehmer.NName = T.NName
AND Seminar_NR = 'S-60123-1'))=False))
GROUP BY T.NName, T.Strasse, T.Postleitzahl, T.Ort, T.Vorname;

Of course, you've got the same subselect there three times. In fact, since
all you're doing is trying to determine those rows where Seminare_NR is a
certain value, the following should be sufficient (and a heck of a lot
faster!):

SELECT T.NName, T.Strasse, T.Postleitzahl, T.Ort, T.Vorname FROM
Teilnehmer AS T WHERE Seminar_NR = 'S-60105-1'
GROUP BY T.NName, T.Strasse, T.Postleitzahl, T.Ort, T.Vorname;
 
A

Allen Browne

There's a couple of things wrong here:

a) From the sample data, it appears Seminar_NR is a Text type field. The
literal values must therefore be delimited with quotation marks.

b) The 2nd field in the subquery's WHERE clause is ambiguous. Include the
table name.

If it helps, you might also get away with DISTINCT instead of GROUP BY, and
you could probably simplify the EXISTS statments and brackets from the way
the Access query designer creates it. Something like this:

SELECT DISTINCT T.NName, T.Strasse, T.Postleitzahl, T.Ort, T.Vorname
FROM Teilnehmer AS T
WHERE EXISTS (SELECT NName FROM Teilnehmer
WHERE (Teilnehmer.NName = T.NName)
AND (Teilnehmer.Seminar_NR = 'S-60105-1'))
AND NOT EXISTS (SELECT NName FROM Teilnehmer
WHERE (Teilnehmer.NName = T.NName
AND Teilnehmer.Seminar_NR = 'S-60123-1'));

Access is generally less stable and less efficient with subqueries, so you
might also consider alternatives. If the values you are searching can't be
duplicated, and you want to test if all 3 exist without using subqueries,
this kind of thing might be worth considering:

SELECT NName, Strasse, Postleitzahl, Ort, Vorname, Count(ID) AS CountOfID
FROM Teilnehmer
WHERE Seminar_NR IN ('S-60105-1', 'S-60105-2', 'S-60105-3')
GROUP BY NName, Strasse, Postleitzahl, Ort, Vorname
HAVING CountOfID = 3;

If you still have problems with Access crashing, make sure you have Name
AutoCorrect turned off:
http://allenbrowne.com/bug-03.html
Then compact/repair the database.
 
Top