[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