Solved

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

Posted on 2014-07-30
2
215 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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

839 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