Recordsets, Queries...how should I do this?

[ChrisCalvert]

I have two tables.
Account with the (relevant) fields
ACNTNO - Primary Key, the account number
OPNDT - Date/Time, Date account opened
EFFDT -Date/Time, (will explain)
and
Sales
ACTNO- account number
RPTDT - Date/Time, Timestamp for this Volume (I.e. 6/1/04 is June'04 sales for this account)

Now the part that is causing me grief. I need to figure the best way to create code or queries to update EFFDT like this:
The EFFDT is the 'effective date of open'. This is created when, in the first two months - if there was consecutively a volume of less than 10- the open date is effectively moved up one month. HOWEVER, if the third month has no sales the EFFDT reverts back to the original OPNDT. Example: Opened in 6/04, no 6/4 Volume: EFFDT = 7/4, No 7/4 Volume: EFFDT=7/4. BUT this only happens as long as the <10 volumes are consecutive. If there is volume in the 1st month, but not the 2nd or 3rd (or whatever) then the EFFDT will never move from the OPNDT. If there is <10 volume in month one, but month two has >10 volume, then EFFDT gets pushed up for the 1st month, but never again. .....WHEW.

So my first solution was just to loop through the 'Sales' as an ADO recordset, and with a loop inside that find and appropriately update the corresponding 'Account' record. But this is taking a very long time.
A second Idea I have is to loop through the 'Account' table, and then use SQL to open a recordset of the matching 'Sales' and update as needed. I don't know if this would be any faster, as I don't know the (time) consequences of opening up a new recordset for every entry in 'Account'. This is a proceedure that would be run monthly (after new sales records are in and before a report that uses EFFDT is run). Anyway, I know this is pretty complicated, and I am not sure I explained it that well. Any high level suggestions on what is a good way to approach this would be much appreciated.

 

[RickSpr]

I'm not entirely clear on your algorithm. Would this be equivalent?
Open recordset rstAccount over Account table
Open recordset rstSales over Sales table
Do While Not rstAccount.EOF
datEffective = rstAccount!OPNDT
rstSales.MoveFirst
rstSales.Find "ACTNO=" & rstAccount!ACNTNO
Do While Not rstSales.EOF
If DatePart("m", rstSales!RPTDT) = DatePart("m", rstAccount!OPNDT) _
And rstSales!Volume >= 10 Then
datEffective = rstAccount!OPNDT
Exit Do
ElseIf DatePart("m", rstSales!RPTDT) = DatePart("m", rstAccount!OPNDT) + 1 _
And rstSales!Volume >= 10 Then
datEffective = DateAdd("m", 1, rstAccount.OPNDT)
Exit Do
End If
mark = rstSales.Bookmark
rstSales.Find "ACTNO=" & rstAccount!ACNTNO, 1, adSearchForward, mark
Loop
If rstAccount!EFFDT <> datEffective Then
rstAccount!EFFDT = datEffective
End If
rstAccount.MoveNext
Loop
Close recordsets
(Note: I do not generally use ADO, so some corrections may be in order.)

If this is correct, it would be faster than your second solution because the Sales recordset doesn't need to be opened repeatedly. It also only updates EFFDT when necessary, which I'm not sure your second solution did.

For that matter, if your second solution gave the correct results but was just slow (which it would be), you can simply restructure it to open the entire Sales table at the start and use MoveFirst and Find to find records. (There should be an index on Sales.ACTNO.)

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[ChrisCalvert]

Thanks for taking a look at it Rick.
That's pretty close to my algorithim. Do you think this would be faster with a DAO method?

Otheriwise I will just stick with my first solution, I suppose.


Thanks again,
Chris

 

[RickSpr]

I don't know whether DAO would be faster. I don't normally use ADO, as I said, and there are too many variables. You'd probably have to try it and see.

Please do make sure Sales.ACTNO is indexed.

Another thing you could do to speed it up is to only consider Account rows where EFFDT > DateAdd("m", -2, Date()). That is, the effective date is within the last two months. Anything with an older effective date must necessarily be correct already, and I'd assume that is true for the majority of rows in Account.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein