I have a table tblFldetail which contains
fields:
ID (sequential auto numbered field),
DESC (text description of record)
ACCTNO (link field from master record named CUST_NUM)
TDATE (date of each transaction)
TTYPE (single code for type of each record, 'S'(ale), 'P'(ayment), 'B'(alance Forward) from previous update)
TAMOUNT (amount of each transaction plus or minus)
TAX (tax amount)
At month end, I need to sum up transactions (where sum not = 0) and
replace previous transactions with a ID, 'Balance Forward' record, date, and
sum of old transactions.
The new record would contain
ID (new auto-number)
DESC (with text 'Balance Forward')
TDATE (current date),
TTYPE ('B')
TAMOUNT (balance amount)
TAX ('0')
I'd like to be able to do this with a single query/update, but would be satisfied
with two steps, one to produce a new table with only the Balance Forward
records and the second to remove the old table and replace it with the
new table.
Any help would be greatly appreciated.
David Wright Sr.