Query Speed

[Steve]

I have a query that users execute on demand to pull data from two linked MS
Access 2000 tables together and form a new local table that they then
perform further action. The two linked tables are basically TEMP table that
contains a list of component information for 7100 records. The RECEIPTS
table contains receipt history on the component (itm_glmoco)and has 22,000
records. The query updates a field in the TEMP table with a supplier name
for the latest date field from the RECEIPTS table. This query takes several
hours and processes maybe two to three bars in the query status bar window.

Here's the SQL:

UPDATE TEMP INNER JOIN RECEIPTS ON
[TEMP].[COMPONENT]=[RECEIPTS].[ITM_GLMOCO] SET TEMP.LAST_INVOICE_SUPPLIER =
[RECEIPTS].[VEND_NAME], TEMP.LAST_DATE = [RECEIPTS].[IVC_DATE]
WHERE ((([RECEIPTS].[IVC_DATE])=DMax("[IVC_DATE]","[RECEIPTS]","[ITM_GLMOCO]
= '" & [TEMP].[COMPONENT] & "'")));

The COMPONENT and ITM_GLMOCO fields are both index with duplicates allowed.

Anything I have wrong or could do differently so this query runs much
faster? It does seem like a very large amount of records so I was expecting
the query to perhaps take very little time.

thanks

 

[Guest]

UPDATE TEMP SET TEMP.LAST_INVOICE_SUPPLIER
[RECEIPTS].[VEND_NAME], TEMP.LAST_DATE = [RECEIPTS].[IVC_DATE
WHERE ((([RECEIPTS].[IVC_DATE])=DMax("[IVC_DATE]","[RECEIPTS]","[ITM_GLMOCO
= '" & [TEMP].[COMPONENT] & "'")))

It should be faster... I hope...