Solved

Access 2010 Create a query that sums up totals but splits by date

Posted on 2014-07-30
205 Views
I have the fields Account, Due Date, Principal, interest, Fees. (Due Date is Month Year - January 2014)

When a payment is received in January for 100.00 the entry is made. Then if an adjustment is made or a payment is returned in February but is January's payment it will be line 2

Account      Due date              Paid Date      Principal      Interest      Fees
12345      January 2014      01/12/2014      80.00      20.00
23456      January 2014      01/15/2014      50.00      5.00
34567      January 2014      01/27/2014      100.00      25.00
34567      January 2014      02/04/2014      -100.00      -25.00
34567      January 2014      02/04/2014                  25.00

I want the account totals on one line, like this:

Account      Due date             Principal      Interest      Fees
12345      January 2014      80.00      20.00
23456      January 2014      50.00      5.00
34567      January 2014      0.00              0.00               25.00
0
Question by:NNiicckk

LVL 119

Accepted Solution

Rey Obrero earned 500 total points
try this

select Account, [Due date],sum(Principal), sum(Interest),sum(Fees)
from tableX
group by Account, [Due date]
0

Author Closing Comment

Thanks
0

Join & Write a Comment Already a member? Login.

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!