Re: [MS_AccessPros] Subtracting 2 dates on the same column

 

Hi Tim,

When you reply please include the relevant portions of the previous thread.

Try this SQL using some subqueries which will require a table name change. I added a typical Cumulative which doesn't actually match your sample.

SELECT tblTim.TransDate, [TransDate]-(SELECT TOP 1 TransDate FROM tblTim T where T.TransDate < tblTim.TransDate ORDER BY T.TransDate DESC) AS DaysSince, tblTim.Amount, [Amount]+(SELECT TOP 1 Amount FROM tblTim T where T.TransDate < tblTim.TransDate ORDER BY T.TransDate DESC) AS Cumulative, (SELECT SUM(Amount) from tblTim T WHERE T.Transdate <=tblTim.TransDate) AS CumulativeB

FROM tblTim;

Duane Hookom

Minnesota

---

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of timdbui@gmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Friday, November 17, 2017 2:10 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Subtracting 2 dates on the same column

 

Hi Duane,

Thank you for replying. I have a table in Access with 2 columns: TransDate (col A) and Amount (col B).

For respective columns A and B, I would have rows like these:

TransDate	Amount
1/1/2017	$150
1/15/2017	$75
2/13/2017	$78
3/14/2017	$130

I would like to write a query to calculate the number of days from the previous date and the cumulative value. The outcome of the query should look like this:

TransDate	DaysSince	Amount	Cumulative
1/1/2017		$150
1/15/2017	14	$75	$225
2/13/2017	29	$78	$153
3/14/2017	29	$130	$208

Thank you for your help, Duane!

Tim

__._,_.___

---

Posted by: Duane Hookom <duanehookom@hotmail.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (4)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___