Iteration

[hyrican]

Hello, I have a script that is designed to find the most previous TimeAndDate for each record. I need to run this script for thousands and/or millions of DataLoggerKeys. The way the script runs now, it runs out of resources pretty quickly. Can somebody offer a suggestion as to how I can edit the script so that it will iterate over the various DataLoggerKeys? I believe that this it the best way to keep the query from being overloaded. Any other suggestions appreciated to keep the query efficient.

Thanks in advance,
Mike

[FONT=Arial][SIZE=2]select target.MemoryDataKey, target.DataLoggerKey, target.DateCreated, target.timeAndDate, target.Occupied, target.Lights, target.Microphone, target.PrimaryKey, max(before.timeAndDate) as previousTimeAndDate into TimeSeriesMDT
from MDT before, MDT target
where before.timeAndDate < target.timeAndDate
and before.DataLoggerKey = target.DataLoggerKey
group by   target.MemoryDataKey, target.DataLoggerKey, target.DateCreated, target.timeAndDate, target.Occupied, target.Lights, target.Microphone, target.PrimaryKey
order by  target.DataLoggerKey, target.timeAndDate [/SIZE][/FONT]

 

[DCrake]

If you are dealing will millions of records and processing this over a network then I strongly suggest you migrate this data into a SQL database this will be more efficient and perform the query with greater speed.

Access is fine if the data is sat on your machine and you have a good processor but network traffic will slow down the process dramatically.