Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2016
    Location
    Chatsworth, GA (Northwest corner of state)
    Posts
    23

    Replacing multiple transaction records as Balance Forward with sum of previous transactions

    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.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Why? Saving aggregate data is usually a bad design. Normally, aggregate data should be calculated when needed from raw data records. Review http://allenbrowne.com/AppInventory.html. I definitely would not 'replace' transaction records with calculated data.

    Every time I've tried to run an update query to save aggregate values I get error 'must be an editable query'. Use VBA to open and manipulate recordsets. One recordset of the aggregate data and one recordset of the destination table. Loop through fields of the aggregate recordset and save to the destination recordset. I would not code deletion and creation of table as this is modifying database design.

    Also, suggest not replacing the record but saving each month's data so history is maintained and reporting can be replicated for any past month.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Join Date
    Oct 2016
    Location
    Chatsworth, GA (Northwest corner of state)
    Posts
    23
    Thanks. I'm not sure I understand it all now, but will try to figure it out.

    I don't need to save historical records other than as a Balance Forward record if it exists on the next cycle. If it is =0 at then end of a cycle, then there is no Balance Forward record to be carried forward. It is <>0 then it is preserved and any new transactions are added to the collection for the next cycle along with the previous Balance Forward record.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I finally got an SQL for INSERT of aggregate value to work. Example:

    INSERT INTO tblFldetail(TDate, TTYPE, Desc, TAmount, Tax) SELECT [input date] AS MED, TTYPE, "Balance Forward" AS D, Sum(TAmount) AS ST, 0 AS T FROM tblFldetail GROUP BY TTYPE;
    Last edited by June7; 04-16-2017 at 07:40 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Join Date
    Oct 2016
    Location
    Chatsworth, GA (Northwest corner of state)
    Posts
    23
    I can't get this to work. Could it be because I am using Access 2000 instead of 2010?

    Also, I am not sure why 'Desc' is not in the same order as it is in the table where it
    comes at the end and why there are some missing fields. ID,ACCTNO, TDate, TTYPE, TAmount, Tax, Desc in the Insert command.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    What does "can't get this to work" mean - error message, wrong results, nothing happens? Post your attempted statement for analysis. Why should it matter what order the fields are in the SQL statement? They need to be same order as the data in the SELECT subquery. Adjust as needed for the fields you need to GROUP BY. You didn't show ACCTNO in the output example of original post so I missed it.

    INSERT INTO tblFldetail(TDate, ACCTNO, TTYPE, Desc, TAmount, Tax) SELECT [input date] AS MED, ACCTNO, TTYPE, "Balance Forward" AS D, Sum(TAmount) AS ST, 0 AS T FROM tblFldetail GROUP BY ACCTNO, TTYPE;

    Then if you really want to delete transaction records:

    DELETE FROM tblFldetail WHERE Desc <> "Balance Forward"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    Desc is a reserved word - it is used for defining the direction of a sort. You should change the field name to something more meaningful.

    I agree with June - storing a value like that is only going to cause problems - what if a previously summed record is changed or deleted, or another added?

    Could it be because I am using Access 2000 instead of 2010?
    No difference. It's a fact of access sql. However strongly recommend you update to a later version of access. 2000 stopped being supported in 2013 (i.e. it cannot be opened in 2013) so you will need an upgrade path to at least 2003 for now but only for a couple more years. If you don't do anything about it you will be in the situation where no upgrade path is available and your app will need to be recreated from scratch.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 03-26-2017, 02:41 AM
  2. Carried forward balance
    By fisseha in forum Access
    Replies: 9
    Last Post: 02-09-2017, 03:25 AM
  3. Carry Forward / Opening Of Balance
    By aamer in forum Access
    Replies: 4
    Last Post: 03-05-2016, 12:28 PM
  4. Replies: 7
    Last Post: 02-10-2016, 10:00 AM
  5. carry forward of balance
    By iamvenu in forum Access
    Replies: 1
    Last Post: 02-07-2016, 07:56 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums