Average interpurchase times

Table is organized like this:

Date            Purchase Quantity
01/02/2010         12
01/23/2010         45
05/04/2010         10
07/06/2010         5
....

How do I calculate the average time between purchase?

Thank you for any help you can provide.

Drew Yallop

-- 
Drew Yallop
0
Utf
4/14/2010 3:33:02 PM
access.queries 6343 articles. 1 followers. Follow

5 Replies
1043 Views

Similar Articles

[PageSpeed] 50

SELECT Avg([Date]-DMin("[Date]","Yallop")) AS AvgDaysBetweenPurchases
FROM Yallop;

BTW: Date is a very bad name for a field or table. It's a reserved word and 
can cause problems if you forget to put the [ ] around it. Read more about 
reserved words at:

http://support.microsoft.com/kb/286335/
-- 
Jerry Whittle, Microsoft Access MVP 
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"thomasDrew" wrote:

> Table is organized like this:
> 
> Date            Purchase Quantity
> 01/02/2010         12
> 01/23/2010         45
> 05/04/2010         10
> 07/06/2010         5
> ...
> 
> How do I calculate the average time between purchase?
> 
> Thank you for any help you can provide.
> 
> Drew Yallop
> 
> -- 
> Drew Yallop
0
Utf
4/14/2010 3:56:05 PM
hi Thomas,

On 14.04.2010 17:33, thomasDrew wrote:
> Table is organized like this:
>
> Date            Purchase Quantity
> 01/02/2010         12
> 01/23/2010         45
> 05/04/2010         10
> 07/06/2010         5
> ...
>
> How do I calculate the average time between purchase?
Use this helper query to calculate the previous date:

SELECT
   O.[Date],
   (SELECT TOP 1 Max(I.[Date])
    FROM yourTable I
    WHERE I.[Date] < O.[Date]
    ORDER BY Max(I.[Date])
   ) AS PreviousDate
FROM yourTable O
ORDER BY O.[Date];

You should consider renaming your [Date] column.


mfG
--> stefan <--
0
Stefan
4/14/2010 4:02:51 PM
Try this:

SELECT AVG(Interval) 
AS AverageInterval 
FROM
    (SELECT P1.Date, P1.Date -
        (SELECT MAX(P2.Date)
         FROM Purchases as P2
        WHERE P2.Date < P1.Date) AS Interval
    FROM Purchases AS P1);

where Purchases is the table name.

Note the caveats the others have expressed regarding the use of Date as a
column name. PurchaseDate would be better.

Ken Sheridan
Stafford, England

thomasDrew wrote:
>Table is organized like this:
>
>Date            Purchase Quantity
>01/02/2010         12
>01/23/2010         45
>05/04/2010         10
>07/06/2010         5
>...
>
>How do I calculate the average time between purchase?
>
>Thank you for any help you can provide.
>
>Drew Yallop
>

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

0
KenSheridan
4/14/2010 5:50:53 PM
Thank you for your quick reply Unfortunately my question was ill-formed.

The table has an additional field - ID. So the table looks like this:

ID    NewDate            Purchase Quantity
1     01/02/2010         12
1     01/23/2010         45
1     05/04/2010         10
1     07/06/2010         5
2     03/020/2010       6
2     05/01/2010         8

I want to calculate iaverage nterpurchase times for each ID.

Best,
Drew Yallop

1
-- 
Drew Yallop


"Jerry Whittle" wrote:

> SELECT Avg([Date]-DMin("[Date]","Yallop")) AS AvgDaysBetweenPurchases
> FROM Yallop;
> 
> BTW: Date is a very bad name for a field or table. It's a reserved word and 
> can cause problems if you forget to put the [ ] around it. Read more about 
> reserved words at:
> 
> http://support.microsoft.com/kb/286335/
> -- 
> Jerry Whittle, Microsoft Access MVP 
> Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
> 
> 
> "thomasDrew" wrote:
> 
> > Table is organized like this:
> > 
> > Date            Purchase Quantity
> > 01/02/2010         12
> > 01/23/2010         45
> > 05/04/2010         10
> > 07/06/2010         5
> > ...
> > 
> > How do I calculate the average time between purchase?
> > 
> > Thank you for any help you can provide.
> > 
> > Drew Yallop
> > 
> > -- 
> > Drew Yallop
0
Utf
4/15/2010 5:29:01 AM
SELECT ( MAX(newDate)-MIN(newDate) ) / (COUNT(*) - 1) , id
FROM table
GROUP BY id


Indeed, the number of date between purchases would be like:      (a_2 - a_1) 
+  (a_3 - a_2)  + ... + (a_n - a_n-1)     /   (  n-1 )
which become, after simplification:   ( a_n - a_1 )  / (n-1)


Vanderghast, Access MVP



"thomasDrew" <thomasDrew@discussions.microsoft.com> wrote in message 
news:DF02259C-847E-49C1-97E7-5C0820402CDE@microsoft.com...
> Thank you for your quick reply Unfortunately my question was ill-formed.
>
> The table has an additional field - ID. So the table looks like this:
>
> ID    NewDate            Purchase Quantity
> 1     01/02/2010         12
> 1     01/23/2010         45
> 1     05/04/2010         10
> 1     07/06/2010         5
> 2     03/020/2010       6
> 2     05/01/2010         8
>
> I want to calculate iaverage nterpurchase times for each ID.
>
> Best,
> Drew Yallop
>
> 1
> -- 
> Drew Yallop
>
>
> "Jerry Whittle" wrote:
>
>> SELECT Avg([Date]-DMin("[Date]","Yallop")) AS AvgDaysBetweenPurchases
>> FROM Yallop;
>>
>> BTW: Date is a very bad name for a field or table. It's a reserved word 
>> and
>> can cause problems if you forget to put the [ ] around it. Read more 
>> about
>> reserved words at:
>>
>> http://support.microsoft.com/kb/286335/
>> -- 
>> Jerry Whittle, Microsoft Access MVP
>> Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
>>
>>
>> "thomasDrew" wrote:
>>
>> > Table is organized like this:
>> >
>> > Date            Purchase Quantity
>> > 01/02/2010         12
>> > 01/23/2010         45
>> > 05/04/2010         10
>> > 07/06/2010         5
>> > ...
>> >
>> > How do I calculate the average time between purchase?
>> >
>> > Thank you for any help you can provide.
>> >
>> > Drew Yallop
>> >
>> > -- 
>> > Drew Yallop 

0
vanderghast
4/15/2010 12:35:26 PM
Reply:

Similar Artilces:

run-time error 3075
I have the following message when clicking on a command button designed to print a report based on the current record in a tabbed form. Syntax error (missing operator) in query expression '(MachNumber=2G-CH01)'. The following code is on the "click" event of the button: Private Sub MachTag_Click() DoCmd.OpenReport "M-Equipment Tag", acPreview, , "MachNumber = " & Me!MachNumber End Sub The 1st tab of the form is pulling the MachNumber data directly from the table. The 2nd tab form (is a subform) and is pulling the data from a second table. I do...

Serial #'s and workorders
I sell alot of computer parts that are serialized and what ticks me off is that if I scan in parts & serial #'s and create a workorder it'll save the serial #'s, but if I make any change at all to the workorder all the serial #'s go bye bye! Also, when ringing up the the sale and finalizing the workorder it asks for serial #'s and if the customer decides to add another item to the sale I need to cancel completing the sale and go back to the work order screen and add in the product, but again this will loose all the serial #'s. Anyways, I'm sick of havin...

time quick entry
I would like to do quick entry for times to the hundredth of a second (e.g., m:ss.00). I've used Chip Pearson's method for time quick entry as a starting point, and edited the Cases, but it's not working for me. I've included the code I've tried below. Thanks for any help, ~ Horatio Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim TimeStr As String On Error GoTo EndMacro If Application.Intersect(Target, Range("A1:A10")) Is Nothing Then Exit Sub End If If Target.Cells.Count > 1 Then Exit Sub End If If Target.Value = "" Then ...

Run-time error '2147217900 (80040e14)
I have a tree list control I want to populate with Categories based on a Client. The client number is input into a text box & then the update event populates the tree based on the selection. I get a syntax error when it tries to open the recordset based on the SQL. Two tables are invloved (Client & Categories; in a 1-to-many relationship on pID field). Here'e the code: Dim rstCategory As New ADODB.recordset, rstComponent As New ADODB.recordset Dim rstSubComponent As New ADODB.recordset Dim tvwTree As Object Dim nodX As Node Dim I As Integer Dim blnAllRecs As B...

time to decimal
I have numbers like 283 hours 13 min 7 sec 283:13:07 in an excel work sheet. I want to convert this to decimal minutes. What formula can accomplish this? I hope someone can help because I have lots of them to do. Thanks for any help, =A1*24*60 Format as General or number, not as Time, which is what Excel does automatically -- Kind regards, Niek Otten Microsoft MVP - Excel "Louie" <me@my.com> wrote in message news:ePZ%23UeIIIHA.4296@TK2MSFTNGP04.phx.gbl... |I have numbers like 283 hours 13 min 7 sec 283:13:07 in an excel work | sheet. | | I want to conve...

how do I record times from track meets and find averages
I need toot been able keep track of athletes time and find averages. I have not been able to figure it out. go over to debra's site: http://www.contextures.com/excelfiles.html some files have been provided for download. check what fits you requirement -- Message posted from http://www.ExcelForum.com ...

Run-time error'3078'
Hi, I had an issue with data not syncing,because it kept referencing the old spreadsheet, so I went into tools, database utilities, linked manager to update the linked file, now I'm getting: The record source 'Select tbl_JobTracking.[job tracking id], tbl_JobTracking.WMS,'tbl_JobTracking.Discipline, tbl_jJobTracking.SvcCtr, tbl_JobTra...' specified on this form or report does not exist. You misspelled the name, or it was deleted or renamed in the current database, or it exist in a different database. In the Form or Report's Design view, display the property sheet ...

average line needed from one single cell
in B3 to M3 (=12cells) I enter data for 12 months in N3, I calculate the average of the 12months How will I get an average line in a graph from that single cell N3? Thanks Hi, Tushar has an example of using a named range. http://www.tushar-mehta.com/excel/charts/straight_lines/index.html Cheers Andy Norbert wrote: > in B3 to M3 (=12cells) I enter data for 12 months > in N3, I calculate the average of the 12months > How will I get an average line in a graph from that single cell N3? > Thanks -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info Hi Andy, thanks for tha...

Start and End times to show in Apptointments
I have recently migrated from Lotus Notes which on the Calendar displays the Start and End time of an Appointment on views such as the daily or weekly view. I know in Outlook it displays these times if you hover onto a specific appt but is there any way so it displays all the time on all the appts ? you can turn it on for month view - right click on the calendar and choose other settings, show end time. Day and week views don't show times unless the appointment is not within the currently selected scale. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-t...

Run-time error after screen lock in XP
In several Excel files I noticed the following error. VBA code works fine until screen is locked by screensaver or pressing Ctrl-Alt-Del and choosing Lock Computer. After unlocking VBA code that worked fine before, now brings the run-time error like run-time error 380. We have OfficeXp SP3 installed. Any solution? ...

All day event (Birthday) start time setting for a WORK day, not full day.
Hello, this problem is literally robbing my sleep. I was using Outlook 2003 and now updated to Vista and Outlook 2007. If I create a new contact and enter his/her birthday, Outlook will create a new all day event for this birthday. This is being synched to my Windows Mobile PDA. So far so good. In OL 2003 this birthday "all day event" would remind me 15 minutes before I start working at 8 am. After the Upgrade 2007 now starts an all day event at 0:00 am. This means, my PDA now reminds me at 11:45 in the night about a birthday. How can I set this back? Or how can I tell Outloo...

chart a time frame
I have a spreadsheet that contains a list of projects where each project has a start date and an end date. I need to chart these projects all into one chart showing the start date and end date for each different project in a monthly calander type of format with the months going across the top of the chart from left to right. I'm having trouble finding a chart that will plot these dates as point "A" and point "B" with a line in between for each different project. What type of chart should I use and how can I get the results that I'm looking for? Thank you ...

Current Time subtract a specific amount every second
I have a total in A1 and B2 I want B2 to be subtracted from A1 every second of the day.. Based on the Current Time in D4 D4 contains =now() Is this what you want: =a1-b2*mod(d4,1)*86400 Format as number Regards, Fred "Jman" <Jman@discussions.microsoft.com> wrote in message news:E1759EFC-38AF-443D-A72A-8ED946580876@microsoft.com... >I have a total in A1 and B2 > > I want B2 to be subtracted from A1 every second of the day.. Based on the > Current Time in D4 > D4 contains =now() > "Fred Smith" wrote: > Is thi...

Time-stamping a chart
I am querying a database and charting the info which I want to publish on a web page. I'd like to time and date of the latest query result to appear on the chart so that users know how current the information is. Maybe even embed the last update info in the chart title? That would work... Something like... "Average Time - Last updated 01/08/2004 9:16 AM EST" Any ideas? How is the query executed? Is it through custom VBA code? Or through native XL capabilities? If the former, use the code to put the desired title in a cell. Link the cell to the chart title. For ...

Sum by range of time
Hello, I have a column of values (A) and a column of times (B). The times fall between 8:00am and 10:00am, but I'm only interested in the values between 9:00am and 10:00am. I want to sum all values in column A whose time values in column B are >=9:00 and <=9:59. I've tried sumif, nested if, or() and and() functions, and I can't get anything to work properly. Can anyone help? Thanks! Hi One way =SUMPRODUCT(($B$2:$B$100>=TIME(9,0,0))*$B$2:$B$100<=TIME(9,59,0))*$A$2:$A$100) -- Regards Roger Govier "rocketD" <darahx@gmail.com> w...

Microsoft CRM 3.0 ignoring british summer time
Hello, We've got a installation of CRM 3.0 with rollup update 2 installed. Win 2k3 EE SP1 as guest on ESX 2.5 host. The guest servers are on BST (GMT+1), however any dates get saved on GMT on the database. This throws reporting off which is a serious concern around here. MS seemed to have pulled rollup update 3, however I went through the list of individual updates and none seem relevant Any ideas? Anybody suffering from the same thing? TIA The Crm database allways stores time in GMT. The conversion to and from local time is done in the user interface. The CRM database does...

changeing the time CRM sends emails
Hello I have set up various workflow rules that send an email reminder for us a couple of weeks before a contract expires. Is there a way to set the time that these email reminders are sent? At the moment they are coming through at 00.00 which is a pain if you have a blackberry by the bed and they do not get actioned staright away. Any advice would be greatly appreciated Thanks ...

Rounding up Time Values
Hi I am using the formula below to round time values up,in this case t the next five minute increment =(TIME(HOUR(BB14),CEILING(MINUTE(BB14),BB13),0)) BB13 = 5 BB14 = 16:00:02 This formula only rounds up to 16:05:02 if the time is 16:01:00 o greater,i would prefer it if the formula could make the time round u as soon as one second has passed, 16:00:01 and not when one minute ha passed. Can this be done Ad -- Message posted from http://www.ExcelForum.com What is in BB14? You can just use =CEILING(BB14,5/1440) will do what you want Or if there are dates as well you can just format...

2nd time posting this problem, pls help
Dear Kindly solve my problem table t1 has fields pictures, name in design view pictures field is ole object and name field is text. now in table, i have inserted some jpeg pictures in field pictures, correspondingly written some name in name field if we open table t1 to view. pictures name package n1 package n2 package n3 package n4 package n5 package n6 now i have written a small query as select t1.pictures from t1 where name = x; x is given in run time, like any thing as n1 /n2/n3/n4 /.... let say x is n2 now. i a...

Receive email in real-time
We have several PCs in our company that are receiving Exchange email messages in real-time, whereas others have to click Send/Receive within MS Outlook to receive their email. How do I setup Exchange where all users can receive their email instantaneously on any machine in the organization onto which they logon. Thanks in advance, Mervin Williams Hi, You can't configure Exchange to not deliver emails instantly to Outlook users. This is an Outlook setting and will have to be configured here. Leif "Mervin Williams" <mwilliams@innovasolutions.net> skrev i en meddelel...

Free busy time O2003 / Anonymous user in 2007
Hello, How do I disable the free/busy time in the calendar for Exchange/Outlook 2003? So other users can't see the appointments of myself. In Outlook 2007 you will set this due to change the sharing permissions of the calendar, and change the default user permission to none. Any idea how you will get the anonymous user with none permissions back in O2007? What's btw the extra functionality of this user. Any help is appreciated, Thanks, Peter ...

time over 1 hour #2
I have a cell that displays teh amount of time a person waited at a certain place. For example, 1:30 for 1 hour, 30 min. I need to have another cell that will tell me the time above one hour, in this case, :30 Thanks Excel counts one day as 1. Therefore 1 hour = 1/24. Try something like =A1-(1/24) Rgds, Andy With the time in A1 =MOD(A1,1/24) or if it 02:30 should be 01:30 over one hour =A1-TIME(1,,) format as hh:mm -- Regards, Peo Sjoblom "johnfli" <john@here.com> wrote in message news:e670PO%23nDHA.2772@TK2MSFTNGP12.phx.gbl... > I have a cell that ...

Changing text/color after a time period?
I hope some of the brilliant Excel people here can help me out. What I am looking to do, is enter a date in a certain cell (if it helps conceptualize, the date that I ship something to someone). Is it possible to have that cell with the "ship date" in it, either change colors, or change the font size, or something, after 30 days have gone by? My reasoning is that the product I ship out, has to be back to me in 30 days, or a penalty will incur to the other party. I just want these fields that have exceeded the 30 day mark, to stick out, so I can easily identify them. Thanks for any h...

Employee Time card form
I have a table containing two fields - Employee_ID and Time_Stamp. I've already created a form that scans in Employee_IDs; and once any Employee_ID is scanned in, the ID and scanned time is recorded to the table. In addition to what it already does, the form needs to populate the employee's punch time records for the current week. For example: (current date and time is Wednesday 9:02 am) (Form view before update) Employee ID: ___________________ (Form view after update) Employee ID: _1001 (Enter)_______ ________________________________ Employee ID: 1001 Mon: ...

Indiana to Eastern Time Zone Exchange Appointment fixer scripts
Attached are some server-side VBScripts for Exchange 2003 (2000 was not tested) that effectively change appointments created in the Indiana Time Zone to Eastern Time Zone. It fixes both the time zone tags and start times where appropriate. Recurring meetings are dealt with properly, without creating exceptions. In fact, recurrences and exceptions adjust automatically. It also fixes single occurrence appointments for Apr 2 - Oct 29, 2006 ONLY. All fixed meetings have their subject lines appended with the original time and date of the item. There are additional scripts for fixing Pu...