Getting a subset of records from a DAO Recordset in Access 2007
Posted on 2014-01-29
I have a recordset set to an SQL Statement that pulls from a very large Oracle table via OBDC connection. It pulls all the data for one year (has wage and hours detail data for employees)
I need to loop through a local table of employeess and have it calculate total wages per employee, based upon a date range, department, and their employee id. The date range
is needed when we employees who have transferred from one department to another where we use a Term Date to identify when they left a department and a Hire Date which is when they started in that department.
I know that I could query the Oracle table every time I need to calculate the totals but it takes a very long time to pull from it (about 10 seconds per pull) -- which could result in some very long processing times when processing 7000+ employees.
Is it possible to bascially query a recordset query? Here's a pseudo code example:
Main recordset's SQL (set to rsE_PRTIME):
Select * from LAWSON_PRTIME where Pay_Year = 2014
If there is a TERMDATE then
Select ENumber, Sum(Wages) as TotalWages
FROM the above referrenced recordset [rsE_PRTIME]
WHERE Date_Stamp > 1/1/2014 and <= TermDate (a variable)
GROUP BY ENumber
Post TotalWages to local table
Is this even possible? If so, I think it would speed up processing time significantly over