Solved

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

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
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…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

770 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