Query to sum transaction amounts for an account within the past 24 hours

[JSvar]

I am a new user of Access and am trying to query a database to sum the dollar amount of all transactions for each account within a 24 hour period. I have the fields (acct number, trans_date_time, trans_amount). I need this to be a true 24 hour period and not just on the same date.

Thank you.

[alansidman]

Here is the syntax for an aggregate query

http://www.techonthenet.com/access/f...umeric/sum.php

[JSvar]

Thank you for the response but this does not really accomplish what I am looking to do. The key point is that the sum needs to be constrained by whether or not the transactions have taken place within a 24 hour period. If their is only one transaction for an account within 24 hours the total would be the amount for that one transaction. If there are multiple transactions within a 24 hour period then the total should be the sum of all of these transactions. I am looking for the syntax to constrain the sum query in this manner.

Thank you.

[alansidman]

Using the same aggregation format, set the criteria in the appropriate field to indicate what period you wish to return data for.

Are you familiar with Select Queries? Setting criteria in Queries?

If you would like to upload your db for analysis that would speed up providing you with a viable solution.

[JSvar]

ID	acct_num	trans_datetime	trans_amt
1	70000	8/3/2013 1:53:45 AM	$946.34
2	70000	8/3/2013 1:55:07 AM	$2,122.48
3	70000	8/3/2013 5:01:34 AM	$946.34
4	70000	8/3/2013 11:46:05 AM	$199.81
5	70000	8/4/2013 4:19:38 PM	$133.21
6	70000	8/9/2013 1:30:39 PM	$134.21
7	70000	8/16/2013 6:40:20 AM	$397.03
8	70000	6/11/2013 4:56:06 AM	$49.95
9	70000	6/11/2013 6:06:33 PM	$49.95
10	70010	7/11/2013 8:30:19 AM	$14.95
11	70010	7/11/2013 5:07:08 AM	$14.95
12	70010	8/10/2013 8:30:29 AM	$14.95
13	70010	8/10/2013 4:47:53 AM	$14.95
14	70010	9/9/2013 8:30:36 AM	$14.95
15	70010	9/9/2013 4:49:48 AM	$14.95
16	70010	10/9/2013 8:30:40 AM	$14.95
17	70010	10/9/2013 4:50:39 AM	$14.95
18	70020	9/30/2013 11:58:47 PM	$1.99
19	70020	10/1/2013 4:57:34 AM	$1.99
20	70020	6/23/2013 12:02:18 PM	$60.21
21	70030	8/4/2013 11:12:56 AM	$35.98
22	70030	8/5/2013 2:15:57 PM	$23.00
23	70030	8/5/2013 5:00:26 AM	$36.10
24	70030	8/5/2013 5:00:26 AM	$23.07
25	70030	8/6/2013 4:02:21 PM	$20.69
26	70030	8/6/2013 4:50:43 AM	$20.65
27	70030	8/7/2013 1:57:59 PM	$16.09
28	70030	8/7/2013 4:51:38 AM	$16.08
29	70030	8/10/2013 4:59:31 AM	$13.17
30	70030	8/10/2013 6:58:08 AM	$13.05
31	70030	8/10/2013 7:16:56 AM	$538.26
32	70030	8/10/2013 8:42:01 PM	$38.59

[JSvar]

It looks like only my table showed up in the reply and not my explanation so here it is again.

As I said, I am a brandnew user of Access so I am not very familiar with much. Your reply makes it soundlike setting a criteria between two specific time stamps which still does notsound like what I am looking for exactly. I cannot upload my actual database buthave created an example with the three fields I am interested in for you tolook at. The fields are acct_num, trans_datetime, and trans_amt.

What I am really interested in is getting a report that displays the number oftimes each acct attempts transactions totalling over $500.00 within a 24 hourperiod. Since each new transactions represents a new 24 hour period some transactions can be a part of more than one 24 hour group. For each new transaction the total transaction amount for the new 24 hour period would be summed and if it exceeds $500.00 this transaction would be marked as a blocked transaction. I would like to see in my report a) the number of blocked transactions for each account and b) the total number of blocked transactions.

I hope that I have explained this well enough for you to understand what I am looking for.

[alansidman]

Attached is my attempt to give you something close to what you are looking for. I have given you counts of transactions by Account by date segregated by greater than and less than 500 dollars. I don't have a clue how to get you to looking at each 24 hour segment based upon each transaction. I am guessing that will entail some complex VBA testing each record in your recordset (read table). Maybe someone else will jump in with a solution on that basis. In the meantime, here is my attempt attached.

[JSvar]

Thank you for this example. As you said it still does not handle the 24 hours correctly. It also does not sum the transaction amounts within the day. If an account has four transactions that are each under $500.00 but to total for the day is greater than $500.00 these need to get flagged. Do you have any suggestions on how to modify what you have here to account for this?

[JSvar]

I figured out how to get my sum of the transactions for each day that total greater than $500.00. If anyone has suggestions on how to change this from a calendar day to any rolling 24 hour period, it would be much appreciated.

[alansidman]

Since this forum is about sharing and learning, it would be nice if you provided your solution for the part to total all transactions to $500 so that others could benefit if they run into a similar situation.

[JSvar]

I'm sorry. From now on I will remember to post any solutions I find. In this case I modified the solution that you had suggested and did a series of three queries. The first returns the sum of all transactions on each day by acount number.




The second query is performed on the results of the first query and returns only the days that had transactions totaling more than $500.00



The third query is run on the result of the second and returns a total count of days that the total of the transactions exceeded $500.00 for each account.

[Dal Jeanis]

Basically, int(trans_datetime) gives you just the date part of the timestamp. If you wanted to get everything from 6AM on Day1 to 6AM on Day2, you could subtract 6/24 of a day from trans_datetime before you take the Int, then add it back afterwards.

int(trans_datetime - (6/24)) + 6/24

which is the same as

int(trans_datetime - 0.25) + .25

Those will give you a "date" that is the starting point of the period, and reads something like 10/31/2013 06:00:00 AM.

You don't actually have to add it back, instead you can just remember that whatever percentage of a day you subtracted, and remember that the report will start at that time of day. For example, if you subtracted 22/24 of a day, then the date that says 10/31/2013 is actually all the transactions from 10/31/2013 10:00:00 PM to 11/01/2013 10:00:00 PM.

[JSvar]

Ok, This appears to be getting closer. Let me throw an example set of data at you and see what you think.

ID trans_datetime trans_amt
1 10/31/2013 08:00:00AM $125.00
2 10/31/2013 08:24:00AM $200.00
3 10/31/2013 12:30:00PM $50.00
4 10/31/2013 06:45:00PM $75.00
5 11/01/2013 01:00:00AM $230.00
6 11/01/2013 08:15:00AM $60.00

On record 1 I want to start with subtracting 24 hours from the time 08:00:00AM and sum all of the transactions that occurred in that 24 hour period. Since there are no previous transactions this would return a total of $125.00.

Moving to record 2 I would do the same thing starting at time 08:24:00AM which would return a total that includes the first two transactions or $325.00.

With record 3 the time would begin at 12:30:00PM so the returned total would include the first three transactions or $375.00

Record 4 would begin the time at 06:45:00PM and return a total that includes the first 4 transaction or $450.00.

Record 5 would begin the time at 01:00:00AM and return a total that includes the first 5 transactions or $680.00.

Then at record 6 the time begins at 08:15:00AM so the first record falls out of the 24 hour period and the total would include the transactions for records 2-6 or $615.00.

This process would continue through the complete table.

Is there a way to use the datetime of the current record as the starting point and subtract the 24 hours from that? If we could do that I could envision getting duplicat "Date" names using the Int function. What do yo

[Dal Jeanis]

Are you trying to get a running sum of the last 24 hours as/of the time of each transaction?

[JSvar]

Yes, That is exactly what I want.