Results 1 to 3 of 3
  1. #1
    vtaurusv is offline Novice
    Windows XP Access 2010 64bit
    Join Date
    Jun 2013
    Posts
    21

    VBA Code Sum value from multiple seq lines for one record

    Hello, I am wondering if there is a code that adds number values from a field in a table on multiple lines for one account ,but also knows how many to add based on the number of lines(Sequences).

    Example (Before) from MAIN_tbl

    SEQ Account# Name Total
    1 001 John 100.00
    2 001 John 50.00
    3 001 John -50.00
    1 005 Jane 100.00

    After
    Name Account# Total
    John 001 100.00
    Jane 005 100.00


    I am able to do this now, but I am doing it the very hard way. Basically I am creating multiple tables for each sequence and then relating by account# and placing an account on each line. I use a macro to run each step, but now I am running into where a record can have over 40 sequences.


    Example.

    Tables:
    MAIN_tbl (Has all info and I can see how many sequences are in the table to create my tables from queries (SEQ1 , 2 ,3)
    SEQ1


    SEQ2
    SEQ3

    Then MAIN_tbl + SEQ1 = Main 2

    Main 2 + SEQ2 = Main 3


    The final Table looks like this....

    Account# Name 1 2 3
    001 John 100 50 -50
    005 Jane 100



    And then I create a table from a query to get the totals.

    Account# Name Total: Nz([1],0)+Nz([2],0)+Nz([3],0)



    Any help is greatly appreciate.

  2. #2
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    You can do this easily with an aggregate query. Look at this link on aggregate queries.

    http://www.techonthenet.com/access/f...umeric/sum.php

  3. #3
    vtaurusv is offline Novice
    Windows XP Access 2010 64bit
    Join Date
    Jun 2013
    Posts
    21
    I feel like a dummy lol. Something so simple, it didnt work before on that method since I had other data conflicting; now I know what I need to do. Thanks for pointing out a flaw in my processes. Solved

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

Similar Threads

  1. Split one record into multiple lines
    By Sally in forum Access
    Replies: 1
    Last Post: 03-11-2013, 07:03 PM
  2. Replies: 5
    Last Post: 08-23-2012, 11:20 AM
  3. Creating a String in VBA over multiple lines
    By Mazdaspeed6 in forum Programming
    Replies: 13
    Last Post: 01-03-2011, 12:32 PM
  4. Creating Multiple Lines per Member
    By SecretGeek in forum Access
    Replies: 2
    Last Post: 08-20-2010, 06:19 AM
  5. Multiple lines per record: Report?
    By cjbuechler in forum Queries
    Replies: 7
    Last Post: 07-30-2009, 08:32 AM

Tags for this Thread

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