SpeedThink
MIS
Currently, I have an Excel Spreadsheet that I plan to populate via a MS Query that "pulls" data from an
Oracle Database (version 8.1.7). In essence, the spreadsheet is setup to provide a "dynamic" Variance
Report that will be used to monitor underpaid customer accounts. The report will be refreshed daily.
The format of the columns is as such:
Acc--OrigBillDt--DtOf1stPymt--DtofLastPymt--ExpPymt--TotPymt
X25--12435-------01-Dec-2004--10-Dec-2004--$1000.00--$250.00
X25--12455-------05-Dec-2004--15-Dec-2004--$2000.00--$150.00
Additional columns on the main worksheet would be as follows:
DateFirstDisplayedOnMainWksht-----N
fDaysOnMainWorksheet
15-Dec-2004-----------------------14 days
20-Dec-2004-----------------------9 days
Note, the "DateFirstDisplayedOnMainWksht" would be the date that the Oracle database was first updated with the initial customer payment.
Currently, the MS Query is configured so that only accounts that have a ratio of TotPymts to ExpPymts of less than 75% will be displayed on the main worksheet. What I would appreciate is two additional worksheets - Worksheet2 to display all accounts that "dropped off" of the main worksheet during the prior week, two weeks, etc. as a result of additional payments received from the customer(s) and Worksheet3 to display all accounts that were added to the main worksheet during the prior week.
WorkSheet2 and Worksheet3 should contain a column that displays the date that the account was "dropped" or "added" from/to the main worksheet.
Additional criteria desired in the SQL script for the MS Query is as follows:
"Balance > 0"
I assume that the worksheet_change event would be used.
Any idea as to the VBA coding needed that will populate worksheets 2 and 3??
Is this possible?
Thanks in advance.
Oracle Database (version 8.1.7). In essence, the spreadsheet is setup to provide a "dynamic" Variance
Report that will be used to monitor underpaid customer accounts. The report will be refreshed daily.
The format of the columns is as such:
Acc--OrigBillDt--DtOf1stPymt--DtofLastPymt--ExpPymt--TotPymt
X25--12435-------01-Dec-2004--10-Dec-2004--$1000.00--$250.00
X25--12455-------05-Dec-2004--15-Dec-2004--$2000.00--$150.00
Additional columns on the main worksheet would be as follows:
DateFirstDisplayedOnMainWksht-----N
fDaysOnMainWorksheet15-Dec-2004-----------------------14 days
20-Dec-2004-----------------------9 days
Note, the "DateFirstDisplayedOnMainWksht" would be the date that the Oracle database was first updated with the initial customer payment.
Currently, the MS Query is configured so that only accounts that have a ratio of TotPymts to ExpPymts of less than 75% will be displayed on the main worksheet. What I would appreciate is two additional worksheets - Worksheet2 to display all accounts that "dropped off" of the main worksheet during the prior week, two weeks, etc. as a result of additional payments received from the customer(s) and Worksheet3 to display all accounts that were added to the main worksheet during the prior week.
WorkSheet2 and Worksheet3 should contain a column that displays the date that the account was "dropped" or "added" from/to the main worksheet.
Additional criteria desired in the SQL script for the MS Query is as follows:
"Balance > 0"
I assume that the worksheet_change event would be used.
Any idea as to the VBA coding needed that will populate worksheets 2 and 3??
Is this possible?
Thanks in advance.
