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.