Improve query performance?

[colintis]

From the below SQL statement, can anyone tell me any methods to improve it? As this query has keep running without results in my latest try, I have to break the process as it takes too long (waited 30 mins) to run.

SELECT A.USERNAME
, A.ALARMDATE
, Mid(A.ALARMDETAILS,(InStr(1,A.ALARMDETAILS,"User",1)+4),(InStr(InStr(1,A.ALARMDETAILS,"User",1),A.ALARMDETAILS,"(",1))-(InStr(1,A.ALARMDETAILS,"User",1)+4)) AS SC

FROM alarmManfASQueryA AS A, Employees AS E

WHERE (((A.USERNAME) Not Like '') 
AND ((A.ALARMDATE) In (SELECT Max(B.ALARMDATE) FROM alarm AS B where (A.USERNAME = B.USERNAME) GROUP BY B.USERNAME) 
And (A.ALARMDATE)>=DateSerial(Year(Date()),Month(Date()),Day(Date())-240)) AND ((A.ALARMDETAILS) Like "*Access Granted*"));

The alarmManfASQuer yA is another query that generates around 110k records from an external database, but this takes only a few minutes to run.

The selected field "SC", is the key field required in further codings, its simply getting a number from A.ALARMDETAILS with below example.

Chall 1 (XXX) Access Granted with Region: Section 17 (Exit), Region 1 (region 1) (Jackie Chan, User 218)

The Mid(InStr(InStr ))function will get the number 218 for SC.

Anyone can help would greatly appreciate. Thanks.


Update: I spend an hour time waiting to its completion, and yes the result appears 1 hour later. But this shouldn't be the time taken for this result, and Access crashed up when I tried to scroll down the list...

[code green]

Sub-queries are usually the culprit but it looks like you are returning a huge recordset.
Start by converting this cartesian JOIN to a more specific one

FROM alarmManfASQueryA AS A, Employees AS E

Using the syntax

FROM alarmManfASQueryA AS A JOIN Employees AS E ON (A.field = E.field)

Then check how many records and the execution time of each sub-query, to see if that is as expected.