Solved

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

Posted on 2014-07-30
2
210 Views
Last Modified: 2014-07-30
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
Comment
Question by:NNiicckk
2 Comments
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 40230877
try this

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

Author Closing Comment

by:NNiicckk
ID: 40230947
Thanks
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

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

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

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now