Simplify and improve processing of Access Query

[Flopper]

Greetings,

The following query takes a wee bit too long to process so i was wondering whether a SQL guru out there could offer their assistance. I need to join on dates in order to retrieve each date whetehr a record exists in qryFSC_Calendar or not.

Many thanks

SELECT 
A.INCIDENT_ID, A.INCIDENT_REF, B.DATE, A.INC_SERIOUS_SC, A.LKUP_DATA_ID, A.LKUP_DATA_SC, A.RAG_ID, A.RAG_SC, A.CALLBACK_RMK

FROM 
qryFSC_Calendar AS A INNER JOIN [DATE] AS B ON (B.DATE+#00:00:00# >= DateValue(A.U_DATE4)+#00:00:00#) AND (B.DATE+#00:00:00#<= DateValue(A.U_DATE6)+#23:59:59#)

ORDER BY 
A.INCIDENT_REF, B.DATE;

 

[Golom]

Try this

FROM 
qryFSC_Calendar AS A RIGHT JOIN [DATE] AS B 
      ON Int(B.[DATE]) =  Int(DateValue(A.U_DATE4))

Assuming that qryFSC is a query, you may also consider running it as a make-table query and building an index on the U_DATE4 field to pick up some speed.

 

[Flopper]

Thanks Golom,

However you seem to have omitted the U_DATE6 part of the join... is this possible?

Cheers