Need_Excelhelp
New Member
- Joined
- Oct 14, 2014
- Messages
- 22
Hi,
I want to sum amounts between uneven timestamps. My current query is set up as follows:
Column 1-3 are from one query and column 4 is from another.
<tbody>
</tbody>
I want to create a query/ or somehow need to find a way to total the amounts between two cutoff times. For example, in row 2 of my expressions, I want have sum of amounts if the timestamp is > 1/1/14 4:59:59 PM and <=1/2/14 3:44:59 PM. Also, in row 1 of my expression, I want to sum all the amounts that are >12/31/14 11:59:59 PM and <=1/1/14 4:59:59 PM.
Is this even possible in Access? I first tried Totals and group by sum after I put the date in criteria. However, that means I have to create a query for every single date range. I have lots of date ranges for me to do that manually. I also tried creating a new table by adding a "second" to the cutoff time through the DateAdd function. Then, I copied that calculated time and pasted it in the new table as "numbers" in one row below--(b/c it would be the beg. time of the following transaction). However, when I converted it to date and time, the time vanished and only date remained. I did something similar in excel workshet--having cutoff time in one column and beginning time in another column. Following is my formula in Excel:
=SUMIFS($D$2:$D$7981,$B$2:$B$7981,">="&G2,$B$2:$B$7981,"<="&H2)
Is it possible to do something like this in Access? Or even subtracting between two rows of a single field?
I would appreciate all the help that I can get!
Regards,
N_EH
I want to sum amounts between uneven timestamps. My current query is set up as follows:
Column 1-3 are from one query and column 4 is from another.
TransactionID | timeStamp | amount | cutofftime |
1 | 1/1/14 2:09:00 PM | $68 | 1/1/14 4:59:59 PM |
2 | 1/1/14 3:48:09 PM | $76 | 1/2/14 3:44:59 PM |
3 | 1/1/14 6:16:32 PM | $95 | 1/3/14 4:14:59 PM |
4 | 1/2/14 3:22:57 AM | $54 | 1/4/14 3:14:59 PM |
<tbody>
</tbody>
I want to create a query/ or somehow need to find a way to total the amounts between two cutoff times. For example, in row 2 of my expressions, I want have sum of amounts if the timestamp is > 1/1/14 4:59:59 PM and <=1/2/14 3:44:59 PM. Also, in row 1 of my expression, I want to sum all the amounts that are >12/31/14 11:59:59 PM and <=1/1/14 4:59:59 PM.
Is this even possible in Access? I first tried Totals and group by sum after I put the date in criteria. However, that means I have to create a query for every single date range. I have lots of date ranges for me to do that manually. I also tried creating a new table by adding a "second" to the cutoff time through the DateAdd function. Then, I copied that calculated time and pasted it in the new table as "numbers" in one row below--(b/c it would be the beg. time of the following transaction). However, when I converted it to date and time, the time vanished and only date remained. I did something similar in excel workshet--having cutoff time in one column and beginning time in another column. Following is my formula in Excel:
=SUMIFS($D$2:$D$7981,$B$2:$B$7981,">="&G2,$B$2:$B$7981,"<="&H2)
Is it possible to do something like this in Access? Or even subtracting between two rows of a single field?
I would appreciate all the help that I can get!
Regards,
N_EH
Last edited: