Hale, Jim
Jim.Hale at FleetPride.com
Wed Jun 2 17:06:42 CDT 2004
Append templates did the trick! Each page had a header that said either either "Deposits" or "Withdrawals" I was able to append the word "withdrawals" to the appropriate records and flip the sign. I also figured out an SQL solution but append was the easiest. Many thanks! Jim Hale -----Original Message----- From: James Barash [mailto:James at fcidms.com] Sent: Thursday, May 27, 2004 4:07 PM To: 'Access Developers discussion and problem solving' Subject: RE: [AccessD] Help on Complicated record count query Jim: If you are using Monarch Pro to parse the bank statement, it may be possible to capture deposit or withdrawal as part of the data. Does the bank statement have the word "Deposit" somewhere before the deposit records and "Withdrawals" before the withdrawal records, or some other distictive text that separates the two? If so, you should be able to capture that and add it to each record. You can set up an Append Template to search for specific text and add that to all subsequent records. I've done that in the past with some fairly complicated mainframe reports that we needed to parse and with a little creative trial and error, you can often differentiate records that look identical as long as there is some header information somewhere in the report. A purely Access solution would be to open a recordset and walk through it one record at a time, convert fldDate to a real date and compare that to the fldDate of the previous record. When the new date is earlier than the previous date, you know that record, and all the the rest of the records, are withdrawals and you can update fldAmt to fldAmt * -1.0 . James Barash -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Hale, Jim Sent: Thursday, May 27, 2004 11:41 AM To: Access Developers discussion and problem solving Subject: [AccessD] Help on Complicated record count query Using Monarch I have successfully parsed a bank statement file (6620 records) into Access. Unfortunately deposits and withdrawals are both positive numbers. The problem I am having is determining where withdrawals begin so that I can flip the sign. Below are the relevant fields. SELECT tblBankStmt.fldDate, tblBankStmt.fldAmt, tblBankStmt.fldCustref, tblBankStmt.fldDescr FROM tblBankStmt; fldate is actually a text field with "04/01" - "04/30". Deposits are listed first with 4/1-4/30 in order. The withdrawals start over with 4/1. There is nothing in the table to distinguish where withdrawal records start except that the date changes from 4/30 on the last deposit item back to 4/1 on the first withdrawal item. So I need an SQL criteria (or maybe an iif stmt on the amt field) that counts the number of deposit records and flips the sign on every record after that. I do not khow to do this so any help would appreciated. TIA Jim Hale -- _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com