SQL Server Performance Forum – Threads Archive

ACC2k: SQL Statemt imposs./endless loop?

Hello friends, I am having a little problem with an ASP I’m just progging.
Background: I query an Acc2k database for employees who speak certain languages. This is a typical SQL statement as created during runtime (output by response.write): SELECT * FROM Personen_Sprachen
WHERE RecID IN
(
(SELECT RecID FROM Tab_Sprachen WHERE Lang=’Englisch’ AND Level>=4),
(SELECT RecID FROM Tab_Sprachen WHERE Lang=’Französisch’ AND Level>=2)
) The query doesn’t return an error in Access, but it doesn’t produce a data output either (data window won’t even open). "Tab_Sprachen" is also the underlying main table of the query "Personen_Sprache", so there might be a sort of circular reference causing problems… The ASP produces "an exception has occured" error at the conn.execute statement. After that, the database is locked by the ASP, I cannot delete the LDB and only a restart of my computer will unlock it! I am posting here, since it is very obviously the SQL statement, that’s causing the problems. Can you help me with this? Thanks & Best wishes,
Andy "Never lose your cool – even the most honest finder couldn’t give it back to you…" – Unknown

---

I am not access expert, but I think you should also post Personen_Sprachen query to be helped.

---

Thanks for the quick response mmaroovic!<br /><img src=’/community/emoticons/emotion-1.gif’ alt=’‘ /><br />Nothing spectacular in query Personen_Sprachen, except, as mentioned, the dependency on Tab_Sprachen:<br /><br /><pre id="code"><font face="courier" size="2" id="code">SELECT S.RecID AS Pers, P.Name, P.Vorname, P.Abteilung, P.Office, P.Telefonnummer, P.Sonstiges, S.Lang, S.Level<br />FROM Tab_Personen P INNER JOIN Tab_Sprachen S ON P.ID = S.Person_ID;</font id="code"></pre id="code"><br /><br />"Never lose your cool – even the most honest finder couldn’t give it back to you…" – Unknown

---

Until an Access expert responds, let’s try this code:
SELECT * FROM Personen_Sprachen
WHERE RecID IN (SELECT RecID
FROM Tab_Sprachen
WHERE (Lang=’Englisch’ AND Level>=4)
or (Lang=’Französisch’ AND Level>=2)
) From performance point of view query with union instead of ‘or’ operator might be faster, but I really don’t have Access experience to judge.

---

I tried this before and: That is exactly the problem! It doesn’t return the records I want…
It returns those people to whom at least one of the two criterions apply. However, I need an AND coniguration. So perhaps the UNION would be it..
I’ll give it a try. Need to leave now. Thanks a bunch for that UNION hint, though!
If I don’t make it back tomorrow:
Have a good start into New Year! Cheers,
Andy "Never lose your cool – even the most honest finder couldn’t give it back to you…" – Unknown

---

How about: SELECT * FROM Personen_Sprachen
WHERE RecID IN (SELECT RecID
FROM Tab_Sprachen
WHERE Lang=’Englisch’ AND Level>=4
)
and RecID IN (SELECT RecID
FROM Tab_Sprachen
WHERE Lang=’Französisch’ AND Level>=2
) Select distinct *
from Personen_Sprachen ps
join … would work too, but I don’t know Access join syntax… … and Happy New Year!

---

Access Syntax is pretty much like SQLServer’s.
That join idea sounds really good!
I’ll try both. Thanks for all your help so far! Cheers,
Andy "Never lose your cool – even the most honest finder couldn’t give it back to you…" – Unknown

---

Got it!
I used your …IN() AND IN()… structure from above and corrected another mistake on my side:
[shame]
I need to use Person_ID as criteria, of course, not the unique RecId
[/shame]
;o) For anyone stumbling across this thread in need of something similar,
So here’s one typical final SQL with superfluous records outfiltered: SELECT * FROM Personen_Sprachen
WHERE Person_Id IN
(
SELECT Person_Id
FROM Tab_Sprachen
WHERE Lang=’Englisch’ AND Level>=3
)
AND Person_Id IN
(
SELECT Person_Id
FROM Tab_Sprachen
WHERE Lang=’Französisch’ AND Level>=2
)
AND Lang IN
(
‘Englisch’, ‘Französisch’
)
ORDER BY [NAME] ASC, [LEVEL] DESC Once again big thanks and a good start into 2005!
ANdy "Never lose your cool – even the most honest finder couldn’t give it back to you…" – Unknown

---

]]>