query to find previous record fields

[auerd]

MS Access 2003

I need to have a query that shows a record with 2 fields from a previous record where the MOrder field is the same and Adate is greater than current records Adate.
This is an example of the data in the tApprovals table


Morder Adate ApprAmt
abc123 1/1/2010 2:12:34 100
abc123 2/6/2011 5:07:4 900
def456 1/3/2010 4:02:27 37
def456 1/4/2008 0:12:34 847
hij789 1/5/2010 2:12:52 7783
abc123 1/6/2010 9:12:20 87


I need the query to look like this. I would rather not use sub-queries due to the sloooow ness of them. I tried and it is painfully slow.
For each Morder I need the previous Adate and ApprAmt with the Difference between the two

Morder Adate ApprAmt PreviousADat PreviousApprAmt Diff
abc123 1/1/2010 2:12:34 100
abc123 1/6/2010 9:12:20 87 1/1/2010 2:12:34 100 13
abc123 2/6/2011 5:07:4 900 1/6/2010 9:12:20 87 -813
def456 1/3/2010 4:02:27 37
def456 1/4/2008 0:12:34 847 1/3/2010 4:02:27 37 -810
hij789 1/5/2010 2:12:52 7783

 

[dhookom]

Sub-queries aren't always slow. Do you have Morder and Adate both indexed? How many records do you have in the table?

Duane
Hook'D on Access
MS Access MVP

 

[auerd]

133000 records. It is slow and I am unable to use the query in a report. Any ideas on how to remedy?

 

[dhookom]

Can you answer

Do you have Morder and Adate both indexed?

Can you also provide the SQL view of the query? Does the query run slow or just the report?

Duane
Hook'D on Access
MS Access MVP