Don't Skip Day of a Month

[mikejaytlabustro]

Hello Access Database Programmers!!!
I just cant figure out how to fix this problem, I need your help please.
Here's the scenario:

Date Amount
------------------------
Jan-01-2017 100.00
Jan-01-2017 100.00
Jan-03-2017 100.00
Jan-03-2017 100.00
Jan-05-2017 100.00
Jan-05-2017 100.00
Jan-07-2017 100.00
Jan-09-2017 100.00
------------------------

I want a result like this, (Running Sum) but not skipping any date in a month

Date Amount
------------------------
Jan-01-2017 200.00
Jan-02-2017 200.00
Jan-03-2017 400.00
Jan-04-2017 400.00
Jan-05-2017 600.00
Jan-06-2017 600.00
Jan-07-2017 700.00
Jan-08-2017 700.00
Jan-09-2017 800.00
------------------------
How many table should I have and what method i should use in a query?

Thank you!!

 

[CJ_London]

you need a table to list the individual days

1/1/2017
2/1/2017
3/1/2017
etc

then base your running sum on that

something like

SELECT tblDates.dt, Sum(tblamounts.amt) AS SumOfamt
FROM tblDates LEFT JOIN tblamounts ON tblDates.dt >= tblamounts.dt
GROUP BY tblDates.dt;

Note this sql cannot be displayed in a query window, only the sql window

 

[mikejaytlabustro]

Thank you! Problem solved. I could really use this one on my database programming.