Query Performace Problem

Question

Using Access 2003

Query

SELECT t1.PERSONID       ,t1.CARDEVENTDATE
       ,MIN(t2.CARDEVENTTIME) AS Intime
       ,MAX(t3.CARDEVENTTIME) AS Outtime,
Min(t3.Cardeventtime) as BreakOut,
Max(t4.CardEventTime) as BreakIn 

FROM ( ( (  SELECT PERSONID
                , CARDEVENTDATE
                         FROM T_CARDEVENT ) as t1
 LEFT OUTER JOIN  (SELECT PERSONID
                                 ,CARDEVENTDATE,  FUNCTION
                                  , CARDEVENTTIME
                      FROM T_CARDEVENT  WHERE  (FUNCTION = 'A'))  as t2
 ON t1.PERSONID = t2.PERSONID 
           AND t1.CARDEVENTDATE = t2.CARDEVENTDATE)  LEFT OUTER JOIN  (SELECT PERSONID
                                 ,CARDEVENTDATE,  FUNCTION
                                  , CARDEVENTTIME
                      FROM T_CARDEVENT  WHERE  (FUNCTION = 'D'))  as t3
 ON t1.PERSONID = t3.PERSONID 
           AND t1.CARDEVENTDATE = t3.CARDEVENTDATE )  LEFT OUTER JOIN  (SELECT PERSONID
                                 ,CARDEVENTDATE,  FUNCTION
                                  , CARDEVENTTIME
                      FROM T_CARDEVENT  WHERE  (FUNCTION = 'B'))  as t4
 ON t1.PERSONID = t4.PERSONID 
           AND t1.CARDEVENTDATE = t4.CARDEVENTDATE )  LEFT OUTER JOIN  (SELECT PERSONID
                                 ,CARDEVENTDATE,  FUNCTION
                                  , CARDEVENTTIME
                      FROM T_CARDEVENT  WHERE  (FUNCTION = 'C'))  as t5
 ON t1.PERSONID = t5.PERSONID 
           AND t1.CARDEVENTDATE = t5.CARDEVENTDATE 

           GROUP BY t1.PERSONID, t1.CARDEVENTDATE 

When i run the above query, it was taking too much time to view the result, so times it was displaying error message as "not enough temp space in a memory"

There is any alternative way to make a query like this in access.

Need query help

Answer 1

Your query is a mess ... I'm not sure what you intend to do .. so here are some wild guesses, in the form of an answer instead of comments .. but I can't format comments ..

since all your joins are left outer joins ..

I suggest you try and play with this:

select FUNCTION
     , PERSONID
     , min(cardeventdate)
     , max(cardeventdate)
  from T_CARDEVENT 
 where FUNCTION in ('A','B','C','D')
group by FUNCTION,PERSONID

PS: a group by on CARDEVENTDATE and min/max on same field don't make much sense (to me).