Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Blacktown, Sydney, New South Wales, Australia
    Posts
    175
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Transaction Processing ?? (AC XP)

    I have inherited the job of Secretary for a small organisation. I have created a database containing tblMembers, which has a numeric key. The members of this organisation pay monthly dues, and it is my job to sent out a monthly account to each member, showing their balance c/f; the amount of the monthly dues, and the amount now owing. I am trying to plan how I might do this in Access so that I can create a Word Mail Merge using the database as the source record.
    From this and other forums, I have read that I should not create a 'balance owing' field, but calculate the figure each time I need it. This has led to a need for transaction processing. I had thought to use a transaction which has apart from the members Id, a transaction code to indicate a payment or an amount due for this month. To solve the possibility of things going wrong, I figured I would also need an 'Adj' transaction to correct errors. The calculation for the amount owing, would then be the balance as per the last adjustment plus due amounts, less amounts paid.
    Whew, this is getting complicated, because I would also need a 'make table' query to calculate the months dues, and an append query to update the transaction table.

    Someone out there must have done something similar, and despite my searches, I have not been able to find anything, except a tip on Inventory stock on hand, which kinda points me in the right direction.

    I am therefore appealing to anyone who might be able to point me in a better direction, or confirm that I am on the right track, but point out any pitfalls in my appraoch.

    As I am still in planning phase, I don't need to undo anything as yet.


    TIA

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Transaction Processing ?? (AC XP)

    Your situation may well be one where you want to ignore the rule about storing calculated values. For purposes of auditability, you may want to carry a running balance in your transaction table, showing the amount billed (or credited ) for the transaction, the previous balance, and the new balance. If you don't do that, then each time you run a report, you have to go back to the initial transaction record, presumably a special transaction that sets the initial balance, and calculate forward all the way to the current transaction. With a stored balance, all you have to do is get the most current balance. Others may have differing views, but we've used this approach successfully several times.
    Wendell

  3. #3
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Blacktown, Sydney, New South Wales, Australia
    Posts
    175
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Transaction Processing ?? (AC XP)

    Hmmmm OK
    What you are saying is that Rules are OK, but there are some situations where they should be ignored.
    If I create a transaction for the Months dues which has a blank field for payments and a calculated carried forward field I should be OK. All I need to do is record the payment against the current months dues record, and the c/f is recalculated.

    Will ponder this further.

    Thanks Wendell

  4. #4
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Blacktown, Sydney, New South Wales, Australia
    Posts
    175
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Transaction Processing ?? (AC XP)

    OK for those who are interested in the solution to my problem.
    I followed Wendell's hint and created a test database (which is attached).
    I created a Financial Records table (tblFinancials) and an abreviated Members Table (tblMembers)
    I then created an 'Append' query to create Financial transactions for the new month, using the 'DateAdd' function to create the new date.
    On running the query, you are promted for last month's date to use as a select criteria, and the new date is created from there.

    Thanks again Wendell for a simple solution, that breaks the rules.
    Attached Files Attached Files

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Transaction Processing ?? (AC XP)

    Not all tables in a database are fully normalized and it is perfectly legitimate to use a "snapshot" table in applications like accounting packages because you need to be able to go back to the snapshot after other transactions have made recalculation difficult or impossible.
    Charlotte

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Transaction Processing ?? (AC XP)

    Glad it worked for you - Charlotte's comments are entirely appropriate for your situation. Hope it works well for you and the organization.
    Wendell

  7. #7
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Blacktown, Sydney, New South Wales, Australia
    Posts
    175
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Transaction Processing ?? (AC XP)

    I re-read your reply after reading Wendell's last post.
    YES!
    The Light went on.
    I can now query the tblFinancials table as at a given date to get a 'snapshot' of the balance at that date.

    Note to all posters: Read replies from experts a number of times. You may miss the significance the first time!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •