[AccessD] Help on Complicated record count query

Yeah, but what happens if an account has only deposits or only
withdrawals?  Or how about if the first deposit is dated *after* the
last withdrawal?  Unless there's some other indicator in the record,
there won't be a perfectly reliable way to separate deposits from
withdrawals.  

IIRC, Monarch is a tool that parses text files representing "formatted
reports."   In the "report" there must be some indicator that identifies
deposits, otherwise you wouldn't be able to make sense of it when
reading it.  You should be able to instruct Monarch to capture that
identifying characteristic (even if it contains something meaningless
for withdrawals) and then use that to segregate the transactions.

Don

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
DWUTKA at marlow.com
Sent: Thursday, May 27, 2004 1:28 PM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] Help on Complicated record count query


I don't think you can do what you are trying to do with SQL.  It think
you
are going to have to make a 'change' during import.  From what you are
describing, it sounds like the only indication of whether it's a
withdrawal
or a deposit is the order of the dates.  I think you are getting a
'group
by' deposit type, and sort by type, then date, you're just not getting
the
deposit type field.

Just write an import routine, that reads the data in the same order it
is
dumped.  Set a boolean flag, and a 'temp' date variable.  set the date
variable to the date you looked at previously, and before you import a
record, determine if the date has 'rolled back'.  If it has, then set
your
boolean variable.  Use the boolean variable in the line where you record
the
value, and set it to 0-value if the boolean is set, else, set it to
value.

Make sense?

Drew

-----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