SumIff amounts between uneven timestamps and referring to previous rows

[Need_Excelhelp]

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.

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

 

[Need_Excelhelp]

Though I would still like to know if the above approach is possible or not, I tried something simple. I just created the table with beginning and ending date in Excel and imported to Access.
Then, I did couple of subqueries and finally got to the sum query. The layout and everything else looks fine, but my sum is off by $100-$200 than the one I had calculated in Excel. I know my excel one is right and all the data in Access is right. I checked the # of rows in Excel and Access, and randomly compared couple of rows from excel with Access (including the beginning and ending time). They all are the same. Then why are the totals different? I would appreciate your help!

Thanks in advance.

Regards,

N_EH

 

[xenou]

If you want to have different date ranges (and not write a query for each one) I guess you'll need to either use a parameterized query or have some other way of putting in a start and end date at runtime. A parameterized query would let you enter the dates when you run the query. Otherwise you can put the dates into a form that you can use when you run the query - the query can use the dates on the form.

Summing is very simple so rest assured that if you get different results in Excel vs. Access you have done something wrong, not something wrong with Access. For instance, you said you did a couple of subqueries but I have no idea why subqueries would be needed here at all. You probably just need help writing your queries properly.


Parameterized Query

• In the ribbon under query design find the parameters button
• Type in the parameter name and type (ex.: Parameter: [Start Date] Date Type: Date/Time)
• Close the parameter dialog
• In your query, enter the exact same parameter name(s) in the query WHERE criteria (ex: >=[Start Date] AND <=[End Date]
  
• Now when you run the query you will be prompted for the criteria

Using a form for criteria

• Create a new form with two textboxes.
• In your query reference the form: SELECT * FROM Table Where TransDate >=CDate([Forms]![Form1]![Text0]) and TransDate<=CDate([Forms]![Form1]![Text2])
• In practice you may want to use better names for the textboxes than the defaults such as text0 or Text2. I would also recommend an after update event to validate and/or pretty print the dates in a good format. Or you could bind the form to a simple temp table with underlying date data types.

 

[Need_Excelhelp]

If you want to have different date ranges (and not write a query for each one) I guess you'll need to either use a parameterized query or have some other way of putting in a start and end date at runtime. A parameterized query would let you enter the dates when you run the query. Otherwise you can put the dates into a form that you can use when you run the query - the query can use the dates on the form.

Summing is very simple so rest assured that if you get different results in Excel vs. Access you have done something wrong, not something wrong with Access. For instance, you said you did a couple of subqueries but I have no idea why subqueries would be needed here at all. You probably just need help writing your queries properly.


Parameterized Query

• In the ribbon under query design find the parameters button
• Type in the parameter name and type (ex.: Parameter: [Start Date] Date Type: Date/Time)
• Close the parameter dialog
• In your query, enter the exact same parameter name(s) in the query WHERE criteria (ex: >=[Start Date] AND <=[End Date]
  
• Now when you run the query you will be prompted for the criteria

Using a form for criteria

• Create a new form with two textboxes.
• In your query reference the form: SELECT * FROM Table Where TransDate >=CDate([Forms]![Form1]![Text0]) and TransDate<=CDate([Forms]![Form1]![Text2])
• In practice you may want to use better names for the textboxes than the defaults such as text0 or Text2. I would also recommend an after update event to validate and/or pretty print the dates in a good format. Or you could bind the form to a simple temp table with underlying date data types.

Thank you so much for your help! The parameter query sounds like an easier option. I am a complete newbie with Access, and don't really know what you mean by form. But I will do some research and play around Access file and see what it is.

Regarding the numbers, I figured it out where I went wrong. Yup, it was an error in subquery. And now, there is a difference of about +/- $.01-$.05 in some cases. And I am assuming that's because of the rounding and continuous formulas.

Thanks once again for your help. Have a great weekend!