subquery - not enough memory (1 Viewer)

[illusionek]

Hello

I got this query with a subquery and it takes couple seconds to run.

However if I try to go to the last record, export it to Excel via ExternalData tab or Make Table, my Access stops working and then I get an error message there is not enough memory.

I am getting lost here as in the end I need to link Excel to this query.

Any idea why it happens?

SELECT qryCalculations.[Item Number], qryCalculations.[Week Commencing], qryCalculations.ClosingInventory, (Select qry2.ClosingInventory from qryCalculations as qry2 where qry2.[Item Number] = qryCalculations.[Item Number] and qry2.[Week Commencing] = qryCalculations.[Week Commencing] - 7) AS PreviousClosingInventory
FROM qryCalculations;

 

[Minty]

Approximately how many records are involved / should be returned?

 

[illusionek]

I cannot check how many records are actually returned by this query but I would expect 7k-10k.

 

[arnelgp]

how about using a join:

SELECT qryCalculations.[Item Number], qryCalculations.[Week Commencing], qryCalculations.ClosingInventory, qry2.ClosingInventory As PreviousClosingInventory
FROM qryCalculations LEFT JOIN qryCalculations AS qry2 ON qryCalculations.[Item Number] = qry2.[Item Number]
WHERE qry2.[Week Commencing] = qryCalculations.[Week Commencing] - 7;

 

[illusionek]

Unfortunately now it does not run at all with this join, I got error message about not enough memory.

 

[Simon_MT]

Write a separate query forcing Item Number into the Query with a Left Join and setting the ClosingInventory using NZ or is null = 0.

Simon

 

[illusionek]

Hi guys

Many thanks for looking into this post. Unfortunately since then I discovered that I have a more fundamental issue with this subequery than just a performance. For some reason I cannot get it right :banghead:

I posted sample data in post #24 in the below thread if anyone would like to look at it and help me

http://www.access-programmers.co.uk/forums/showthread.php?t=286678&page=2