[AccessD] Help on Complicated record count query

That will work.  I'll let you know how it goes. Thanks!
Jim Hale

-----Original Message-----
From: DWUTKA at marlow.com [mailto:DWUTKA at marlow.com]
Sent: Thursday, May 27, 2004 3: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