Calculating transactions by time

Here's my problem, I have customer transaction data that shows deposits and 
withdrawals for a month.  My data includes the customer ID, type of trans, 
date of trans, time of trans and amount.  I am trying to come up with a way 
to count the number of times a customer makes a deposit then immediately 
makes a withdrawal.  Obviously this will occur on the same date and within a 
minute or less of the deposit.  I can pull a query showing the two relevent 
transactions together in a table but how do I count the number of 
occurrences.  Any help is appreciated.  Thanks!
0
Utf
4/28/2010 2:58:01 PM
access 16762 articles. 3 followers. Follow

4 Replies
950 Views

Similar Articles

[PageSpeed] 42

SELECT COUNT()
Look it up in Help.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they 
eat for a lifetime".


"sabunim" wrote:

> Here's my problem, I have customer transaction data that shows deposits and 
> withdrawals for a month.  My data includes the customer ID, type of trans, 
> date of trans, time of trans and amount.  I am trying to come up with a way 
> to count the number of times a customer makes a deposit then immediately 
> makes a withdrawal.  Obviously this will occur on the same date and within a 
> minute or less of the deposit.  I can pull a query showing the two relevent 
> transactions together in a table but how do I count the number of 
> occurrences.  Any help is appreciated.  Thanks!
0
Utf
4/28/2010 4:14:01 PM
Have you checked to see if the Totals query can do the calculating you need? 
Try the Count aggregation...

Regards

Jeff Boyce
Microsoft Access MVP

-- 
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"sabunim" <sabunim@discussions.microsoft.com> wrote in message 
news:7256CDCA-C376-4EDE-9392-8639C38EF912@microsoft.com...
> Here's my problem, I have customer transaction data that shows deposits 
> and
> withdrawals for a month.  My data includes the customer ID, type of trans,
> date of trans, time of trans and amount.  I am trying to come up with a 
> way
> to count the number of times a customer makes a deposit then immediately
> makes a withdrawal.  Obviously this will occur on the same date and within 
> a
> minute or less of the deposit.  I can pull a query showing the two 
> relevent
> transactions together in a table but how do I count the number of
> occurrences.  Any help is appreciated.  Thanks! 


0
Jeff
4/28/2010 4:16:06 PM
You'll need to join two instances of the table (CurrentAccounts  below) on
the account holder being the same (same AccountNumber), the transaction's not
being the same transaction (different TransactionID), one being a deposit
(Credit > 0), the other a withdrawal (Debit > 0) and the transaction times
being close (5 minutes or less in the example below)

SELECT A1.AccountNumber, COUNT(*) AS ImmediateWithdrawals
FROM CurrentAccounts As A1, CurrentAccounts AS A2
WHERE A1.AccountNumber = A2.Accountnumber
AND A1.Credit > 0 AND A2.Debit > 0
AND A1.TransactionID <> A2.TransactionID
AND DATEDIFF("n",A1.TransactionDate+A1.TransactionTime, 
A2.TransactionDate+A2.TransactionTime) BETWEEN 0 AND 5
GROUP BY A1.AccountNumber;

Bear in mind that if one deposit and two withdrawals are made within a 5
minute period this would count as two occurrences, not one.

Ken Sheridan
Stafford, England

sabunim wrote:
>Here's my problem, I have customer transaction data that shows deposits and 
>withdrawals for a month.  My data includes the customer ID, type of trans, 
>date of trans, time of trans and amount.  I am trying to come up with a way 
>to count the number of times a customer makes a deposit then immediately 
>makes a withdrawal.  Obviously this will occur on the same date and within a 
>minute or less of the deposit.  I can pull a query showing the two relevent 
>transactions together in a table but how do I count the number of 
>occurrences.  Any help is appreciated.  Thanks!

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access/201004/1

0
KenSheridan
4/28/2010 5:13:22 PM
Excellent!  I followed your thought process, tweaked it a bit and got it work 
for me.  Thank you!

"KenSheridan via AccessMonster.com" wrote:

> You'll need to join two instances of the table (CurrentAccounts  below) on
> the account holder being the same (same AccountNumber), the transaction's not
> being the same transaction (different TransactionID), one being a deposit
> (Credit > 0), the other a withdrawal (Debit > 0) and the transaction times
> being close (5 minutes or less in the example below)
> 
> SELECT A1.AccountNumber, COUNT(*) AS ImmediateWithdrawals
> FROM CurrentAccounts As A1, CurrentAccounts AS A2
> WHERE A1.AccountNumber = A2.Accountnumber
> AND A1.Credit > 0 AND A2.Debit > 0
> AND A1.TransactionID <> A2.TransactionID
> AND DATEDIFF("n",A1.TransactionDate+A1.TransactionTime, 
> A2.TransactionDate+A2.TransactionTime) BETWEEN 0 AND 5
> GROUP BY A1.AccountNumber;
> 
> Bear in mind that if one deposit and two withdrawals are made within a 5
> minute period this would count as two occurrences, not one.
> 
> Ken Sheridan
> Stafford, England
> 
> sabunim wrote:
> >Here's my problem, I have customer transaction data that shows deposits and 
> >withdrawals for a month.  My data includes the customer ID, type of trans, 
> >date of trans, time of trans and amount.  I am trying to come up with a way 
> >to count the number of times a customer makes a deposit then immediately 
> >makes a withdrawal.  Obviously this will occur on the same date and within a 
> >minute or less of the deposit.  I can pull a query showing the two relevent 
> >transactions together in a table but how do I count the number of 
> >occurrences.  Any help is appreciated.  Thanks!
> 
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access/201004/1
> 
> .
> 
0
Utf
4/29/2010 8:08:06 PM
Reply:

Similar Artilces:

Chart in time phase
I need to develop a line chart which includes 2 columns of data. Say for instance the data looks as follows: 5 6 15 9 20 12 50 25 Columns with the 5 and the 6 are for March. Columns with the 15 and the 9 are for February. So as we go up, the month increases. When I make my graph, the 5 and the 6 are first plotted points, the 15 and the 9 are the second, etc. In other words, it is plotting March, February, January, instead of January, February, March. How can I make the chart plot in a logical time phased manner? That is, Jan, Feb, Mar, etc? Thanks. jzis Instead of plain text &qu...

Wrong time on incoming mail messages
Hi. I'm working in Outlook Express 6, and even though the clock on my computer is correct, the times listed for received messages is wrong. Any idea how to change this? Thanks. By How Much are they wrong! Do they have the correct date? -- Regards, Pat Garard Australia Anne & Pat Garard. apgarardATbigpondDOTnetDOTau _______________________________________________ Yes, the date is right, but they're off by about an hour. What I can't figure out is that I thought it went by my computer clock, which is right! Thanks. >-----Original Message----- >By How Much are t...

excel
I'm looking to set up a formula so that times in the source cell are rounded down to the nearest hour. eg Source Cell A1 is 10:50am....the formula in cell B1 rounds that down to 10am. Source Cell A2 is 10:55....the formula in cell B2 rounds that down to 10am. The info in column B is then collated in a Pivot table giving a per hour (10:00:00 thru 10:59:59) volume value. In this case 2. TIME(HOUR(A1),0,0) will give you 10am in cell B1, though I can't speak to your further operations with the pivot table. On Wed, 27 Aug 2003 23:47:11 -0700, "nick" <nick.morriso...

Returns Transaction Entry Error
I'm trying to do a Return. Just a plain Return not Return w/Credit. I'm getting the error message: "There are no available quantities for this item or vendor item. Please select a different item or vendor item." The PO has been received but not invoiced. There are quantities on-hand for this item but many have been sold. Why am I getting this error? Thanks. Michelle Michelle, Apparently, the quantity that was received for this item/vendor combination have been sold or otherwise consumed. Do you buy this item from more than one vendor? Could...

Report error Run Time error 2427
I have combo box where I have list of months and years. When I choose January 2009. It gives me a 2427 run time error. When I debug it takes me to: Private Sub Detail_Format(Cancel As Integr, FormatCount As Integer) If Me.somefield>0 Then Me.Detail.BackColor = 14408667 Else Me.Detail.BackColor = 16777215 If me.[anotherfield] = "Followup" Then Me.Label22.Visible = False End Sub However, when I choose any other month I get my report in view form. Can someone please explain and help me out?? Thank you. I would try: Private Sub Detail_Format(Cancel As Integr, F...

Reporting on "Details" to a transaction
I've been adding details (Shift+F9) to some transactions at the POS so I could come back later and make changes or address specific transactions where necessary. However, now that I'm trying to go back and find the transaction with details associated, I can find them. Is there an easy way to list all of the transactions that have comments associated with them. Or to search by reference number since you are asked to create a reference number when addeding the "details". Please help ! Thanks, Kevin Are the transactions done as quotes, workorders or what? I belie...

Delay Calculation of A Cell
I have a cell with the following calculation =IF(AND(C62-C56>0,C70-C66>0),"Yes - Loan Should be Approved",IF(AND(C62-C56<0,C70-C66<0),"No - Speak to your Advisor.","Possible - Depending on Detailed Application, speak to your Banker")) Now it works ok but I want to stop it calculating until the "user" requests an answer. The second part is how do i tell the spreadsheet to remove all data in "unprotected cells"? The idea the user opens the spreadsheet completes a series of cells then selects answer to display the...

Calculated fields in the Sub Grand Totals
How come these don't alays work? ...

Addition of Buttons in Toolbar at run time
hi All, I am a newbie for Tool bars. I want to provide options to the user to add new Buttons(every thing, including Bitmap etc) in the tool bar while working on it. is there any way to do this?? "Ashish" <aashish83in@gmail.com> ha scritto nel messaggio news:1192617554.299345.145370@e9g2000prf.googlegroups.com... > I am a newbie for Tool bars. I want to provide options to the > user to add new Buttons(every thing, including Bitmap etc) in the tool > bar while working on it. is there any way to do this?? You may find the following CodeProject arti...

Overtime Calculation based on multiple hourly pay codes.
The system presently is just not designed to calculate overtime based on multiple hourly pay codes. If you enter 20 hours in one hourly paycode and 25 hours worked in another, these would all need to be entered for the same hourly pay code in order for 5 overtime hours to auto-calculate. The system should have the ability to check all the hourly paycodes for a given pay-period and then automatically calculate overtime if need be. Thanks Girish ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this s...

date and time stamp on command
Hi. I have the formula in which if I update a cell in a row, column A will time stamp the change. Private Sub Worksheet_Change(ByVal Target As Range) Range("A" & Target.Row) = Now() End Sub However, I would like to have a time stamp in column A, whenever the same row (column B) is updated with a "Y". I am keeping a daily log and want to be able to control the date and time stamped by placing a Y in column B. I want to know what time a Y was placed on the row. In short terms, Y = date/time stamp, no Y, no date/time stamp. Example below: A ...

RPC over HTTP Outlook too long time
Hi, Here is the configuration: Exchange 2k3 (single server, configured as RPC-HTTP Back-end server) in a 2k3 AD environment where DC is GC. I configured RPC over HTTP according to an external article. It works (no SSL) but it takes quite long time to establish connection to the server for any job. What might be going on or where to debug? Thanks Yba On Sun, 9 Jul 2006 07:29:02 -0700, yba02 <yba02@discussions.microsoft.com> wrote: >Hi, >Here is the configuration: >Exchange 2k3 (single server, configured as RPC-HTTP Back-end server) in a >2k3 AD environment where DC is G...

calculations not coming out right... ASAP!
i have a form that does not multiply correctly. i enter Quantity, UnitPrice, check Expendable (if needed and this adds 10% to the UnitPrice). then the UnitPrice and the 10% are added together.. everything is fine. I then multiple the UnitPrice (which goes to NetAmount) by the Quantity which is then put in TotalAmount but is off by roughly 2 cents. It only happends when i check the Expendable box. Here are my formulas.... Private Sub expen_AfterUpdate() expen = expen / 100 End Sub Private Sub Expendable_AfterUpdate() If Me!Expendable = True Then Me!Netamount = Nz(Me!UnitPrice, 0) * [ex...

Exchange 2007 And Daylight Savings Time Update
Is there a patch I need for Day light savings time and Exchange 2007? Thanks! No CDO patch required (no equivalent of KB 926666). However, the Windows Server 2003 OS will required KB 931836 time zone update and Calendar-items in 2007-hosted mailboxes created using old DST 2006 rules will need to be rebased. -- Bharat Suneja MVP - Exchange www.zenprise.com NEW blog location: exchangepedia.com/blog ---------------------------------------------- "D303M" <D303M@discussions.microsoft.com> wrote in message news:A3132ABD-4614-402F-9654-61EA72501112@microsoft.com... > I...

Insert transaction in register?
I am using Money 2006, advanced register. When transcribing entries from my checkbook register to money, I will occasionally skip a transaction. If I want to maintain the same order as in the checkbook, I then have to delete all the following transactions, enter the one I missed, and then reenter the ones I deleted. Is there a way to insert a missed transaction between existing transactions in the register? How are you sorting transactions? If sorting by 'Date', they should follow checkbook entries (assuming checkbook entries are in date order). Choosing View on the menu bar...

URGENT: IF Function in Pivot Table Calculated Field
I am having trouble with an IF function call in the formula of calculated field in a pivot table. The pivot table has a calculated field called 'SDLT Tapes Required containing the following formula: =IF('Backup Media Type'="SDLT",'Total Backup Capacit Requirement'/220,0) where 'Backup Media Type' and 'Total Backup Capacity' are fields in th pivot table. 'Backup Media Type' contains text values such as "SDLT and "DLT 20/40". The field always displays a result of 0 even though there are rows i which the 'Backup Medi...

Sales Transaction Distribution Entry
Transaction --> Sales --> Transaction Entry : When credit card/cash is selected to show the revenue the distribution tab will show the following details : Debit Credit Sale A/c 1250.00 Credit Card A/c 1250.00 Account Receivable A/c 1250.00 Account Receivable A/c 1250.00 Why is it showing the "Accounts Receivable" when the transaction has been done by Credit Card? Do you have Separate Payment Distributions checked in Company Setup Options? (I...

Entering current date+time as numeric value
Is there a simple way to enter the current date+time as a static numberic value? I know I can use ctrl+; and ctrl+:, but these return text, not numeric. The best I can come up with is to enter "=now()" in a cell, then copy it to itself and select "Values only" from copy drop down list. This works, but is tedious. Is there a one-key way (like ctrl+;) to accomplish the same thing? Which version of Excel are you using? My 2003 and 2007 versions do not enter a text value with the shortcut keys. After ctrl + ; to enter date, format to General and see a number. 40525 which ...

calculating time with IF statement
I have the following: A1 is formated as time = 7:30 AM A2 is formated as time = 6:00 PM A3 is formated as time and contains =sum(A2 - A1) and gives 10:30 That part I've got down. Now, I want to subtract 30 minutes so I added: A4 is formated as time and contains =sum(A3-30/1440) and gives 10:00 Worked, thanks to those that have posted these questions. Now the problem: I only want to subtract the 30 minutes if cell A3 is greater than 7:00 How do I write that IF statement? Thanks for the help, Mark You could have used: =a2-a1 =a3-30/1400 =sum() doesn't really add anything to...

Formula/calculation help for medical charting
Hi, I am trying to develop an anesthesia record chart for the vet clinic I work at. I have A8 through A15 labeled for time, temperature, heart rate, etc. The other columns (B8-B15, C8-C15, etc) I have set aside for later entries in the above categories as we take values every 5 min. or so. What I would like to do is have a formula that would show numerical values in red if they are above or below the normal value range (in black). For instance, a dog's normal temperature can range from 99.5 degrees F to 102.5 degrees F. If it is 98 degrees or 103 degrees I want these values to s...

Calculation Speed
For a particularly large excel workbook, the recalulation time is quite long. Any suggestions to reduce calculation times? The following are some things I'm thinking about. Please feel free to comment on their plusses/minuses or add to the list. 1. Use Manual Recalc 2. Break workbook into smaller workbooks 3. Get more CPU 4. Allocate more CPU to Excel. 5. more? Thank you. Mark Hi Mark, I have a web page on the subject. Slow Response, Memory Problems, and Speeding up Excel http://www.mvps.org/dmcritchie/excel/slowresp.htm Charles Williams has a website on the subject, one...

If Calculation
Hello to all, I�m a new member at the forum. A question that i have, I want to calculate the following: Suppose that in cell A1 is number 1, cell B1 is number 2 and cell C1 i number 100. On cell A2 is number 1, on cell B2 is number 3 and on cel C2 is 100. Now i want the calcutation to cell D1 to returns if values on the cell A1=1 and B1=2 then C1*5%. For the second row if A2=1 and B2=3 the C2*6%. As it is a big file i want to make one calculation so when someon makes copy-paste to do the calculation automatically. I hope that you can understand me. Thanks a lot -- Adiorthoto ----------...

Will not accept transaction
I have 2 transactions which downloaded and matched. I click on accept and nothing happens. I right click on the transaction and click on accept and nothing happens. I did both a quick repair and a level 2 repair, but I still can not accept the 2 transactions This has happened to me a few times, once after upgrading and another time for no apparent reason, I deleted the transaction and entered it again, and the next time the bank downloaded I was able to accept the transaction. There is most likely an internal error with the transaction. Bill <ordnance1@comcast.net> wrote in m...

PM/RM Batch--Transaction Threshold?
1) Does anyone have insight as to the practical limits on the number of transactions which either a PM or RM batch can hold/process prior to presenting a problem. 2) We have had problems in past scenarios where high-transaction volume batches (200+) have caused system problems with posting to the G/L and the Sub-ledgers of either RM or PM. Any way to avoid this w/o reducing the number of transactions contained in the batch? ...

Date Calculation 02-20-08
A co-worker gave me a database (Paradox) that he needs converted to Access. The database is an overtime equalization database that allows supervisors to keep track of voluntary worked overtime. Here is my dilemma: The work schedule which is used is a rotating schedule. The schedule is called a 6-2 (Work 6 and off 2). You start out with a Monday/Tuesday as you days off. The following week you have Tuesday/Wednesday. Then you have Wednesday/Thursday off. Then Thursday/Friday off. When you get to the weekend you have Friday/Saturday/Sunday. The following week you have Saturday/Sun...