Summing distance data between 2 dates that vary

I have a table that contains the dates, distances and times of all my 
training runs and races.  (An extract is shown below). I want to run a query 
to calculate the aggregate distance run in periods of 1 week, 1 month and 3 
months before each race date. Any thoughts on the best way of tackling this?

Date	Event	               Distance
28-Jul-09	Bridge Inn 5k	5.00
26-Jul-09	Conkwell Killer	13.40
23-Jul-09	Training	                9.18
21-Jul-09	Coaching	                8.37
07-Jul-09	Coaching	                7.00

0
Utf
11/29/2009 2:24:01 PM
access 16762 articles. 3 followers. Follow

1 Replies
736 Views

Similar Articles

[PageSpeed] 9

You will need three sub-queries to get the solution.  You may need to redefine 
the ranges depending on hojw you are defining the period before each race date

SELECT [Date], Event
, (SELECT Sum(Distance) FROM [Table] as T2
    WHERE T2.[Date] >= T1.[Date]-6 and
          T2.[Date] < T1.[Date]) as WeekTotal
, (SELECT Sum(Distance) FROM [Table] as T2
    WHERE T2.[Date] >= DateAdd("m",-1,T1.[Date]) and
          T2.[Date] < T1.[Date]) as Month_1Total
, (SELECT Sum(Distance) FROM [Table] as T2
    WHERE T2.[Date] >= DateAdd("m",-3,T1.[Date]) and
          T2.[Date] < T1.[Date]) as Month_3Total
FROM [Table] as T1

If there are more than one event per day and you want the numbers to include 
the other races on that date, then you will need a way to distinguish the 
order of the events on the date (storing the date and time in the same field 
would work for that).

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

JB wrote:
> I have a table that contains the dates, distances and times of all my 
> training runs and races.  (An extract is shown below). I want to run a query 
> to calculate the aggregate distance run in periods of 1 week, 1 month and 3 
> months before each race date. Any thoughts on the best way of tackling this?
> 
> Date	Event	               Distance
> 28-Jul-09	Bridge Inn 5k	5.00
> 26-Jul-09	Conkwell Killer	13.40
> 23-Jul-09	Training	                9.18
> 21-Jul-09	Coaching	                8.37
> 07-Jul-09	Coaching	                7.00
> 
0
John
11/29/2009 3:16:28 PM
Reply:

Similar Artilces:

How to perform sum sum sum...
How to perform sumation within that particular item but the item is not unique...means item 1 has its own quantity and same goes to item 2...but in the same table... -- Message posted via http://www.accessmonster.com On Tue, 17 Apr 2007 05:28:12 GMT, "EMILYTAN via AccessMonster.com" <u33296@uwe> wrote: >How to perform sumation within that particular item but the item is not >unique...means item 1 has its own quantity and same goes to item 2...but in >the same table... Group By the item. For a more detailed answer, please post a more detailed question (with a des...

Old CRM SQL data files
We run CRM 3.0 on server 2003 with SQL 2000 server. A while back the MSCRM and METABASE files were detached from SQL 2000 and saved on a different server while the exisiting server went through an overhaul. With clean installs and current upgrades in place we now need to introduce the MSCRM and METABASE data back to CRM. We detached the empty 'new' files and re-attached the old files into SQL 2000 but now appear to have a problem and can't get CRM to access the data. It comes up with an Authentication Error stating 'Microsoft CRM could not log you on to the system. Mak...

Summing up user defined results
Hi all - I'm new to VBA programming in Excel and so any help i'd totally love! I'm currently writing a function. Objective: user can select rows (do not have to be sequential). User clicks on button. UserForm appears with summed results from ONLY rows that he selected. What I have now, well it doesn't work: Sub Button6_Click() Dim i As Integer Dim totalNumbers As Integer Dim aRange As range For Each a In Selection.Areas 'MsgBox "Area " & i & " of the selection contains " & _ ' a.Rows.Count & " rows." &...

Multiple variables to sort and sum, return values<0 with sum refer
Can anyone help me with this one please? I have this table of data. I'd like to be able to write a formula(s) which sums the No according to date and code, but then only returns a sum value (with the code and date in the two adjacent cells) if there is a value greater than 0. Code Ref No Date 1 G/032/05/999 400600 212 19/03/2010 2 G/032/03/001/999 400500 50 19/03/2010 3 G/032/02/001/001 400400 170 19/03/2010 4 G/032/05/999 400600 315 19/03/2010 5 G/032/03/001/999 400500 300 19/03/2010 6 G/032/05/999 400600 202 19/03/2010 7 G/032/03/001/002 40...

sum subform to form
=Sum([Forms]![frmCamPledgeList].[frmCamPledgeListSub].[Amount Pledged]) Form (frmCamPledgeList) with subform frmCamPledgeListSub). Subform has a field named Pledge Amount. I want to put a field on the form that gives me a total of the subform's Amount Pledged. I have tried the above, but I get an #error in the unbound text box. Do you need any more info? Any thoughts on why this does not work? Thanks in advance, Scott -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200912/1 The proper syntax is =Sum([Forms]![frmCa...

Share Workbooks and Comments #2
Can anyone help me with this one... I have a simple spreadsheet which is shared and resides on a server. Apprx 6 people access and modify the sheet and save their modifications back to the server. No 2 people ever change the same cell so there are never any conflicts. The problem I have is that often the comments in a cell do not upload to the server. The user is unaware of this util he closes and re-opens the workbook to find that all of his changes have been saved but none of his comments. I have been getting the users to make their own copy at the end of the day and if they e-mail that c...

How to Combine Data from Several Columns?
Hi Everyone I have attached a file which contains what I need to resolve Basically, I need to combine the data in several columns together int one column. Is there a way I can do it with formulas or vb code? would really appreciate if any experts out there can help me. Thanks and Regards Kelvi Attachment filename: book1.xls Download attachment: http://www.excelforum.com/attachment.php?postid=46105 -- Message posted from http://www.ExcelForum.com Hi Kelvin! Probably the answer will be something like: =A1&" "&B1&" "&C1 ...

Creating a Worksheet with date
Hi, I'm trying to creat a wroksheet that will have two weeks of date at the top row to be printed every two weeks and each time printing, it shows the next two weeks automatically. I'm very new on doing this, please help if there is anyway of doing this. Thank you in advance for your help. Regards, MD Thanks Paul, It worked. MD >-----Original Message----- >Md, how about putting the first date in A1 and then this formula in B1 and >copy accross to as many columns as needed, then you will only have to change >the date in A1, this could be changed automatically wh...

Importing data and time
Does anyone know what format the source data needs to be in in order to import a date/time field. For example for the date/time, the format is data | time (10/12/2006 | 9:00) Thanks for the help! ...

Bubble chart with duplicate data points
I have a list with duplicate data points that I would like represented only once. i.e. - right now I get 5 Mon when I would like them all to be plotted on the same Mon. Day (x) Time (y) Count (z) Mon 0:00 1 Mon 9:00 1 Mon 11:00 1 Mon 15:00 1 Mon 22:00 1 Tues 0:00 1 Tues 1:00 2 Tues 2:00 1 Tues 14:00 1 Tues 15:00 1 Tues 16:00 1 Wed 0:00 1 Wed 14:00 2 Wed 22:00 8 Thanks! ...

Look up or matching data
Hi I have a formula that works well for transferring data but I need it t recognise another cell (it has the month) Attached is a spreadsheet of basically what I'm looking to do. In Sheet2 when a tech # is entered that matches the data in sheet1 the the result of what I'm looking for appears in Sheet2 B14 to D27. The only problem now is that these results should be listed accordin to the data in Sheet2 G2 (Month dropdown) as well, which refers t Column H in Sheet1 The formula currently in use in the results area is =IF(ROW()-13>COUNTIF(Sheet1!$A$2:$A$30,$D$2),"",OFF...

Rows of data, using status columns to sort data to pie chart on another worksheet tab
Hi there, 1. I have 50 rows of data. There are many columns, but, there is one status column that I use to group the rows of data into relationships. I would like to build individual charts based on the status column. So, if I say these items are part of "Group A", information from those rows that are "Group A" will automatically show up on a chart on the "Group A" worksheet tab pie chart. I will have "Group A", "Group B", "Group C" pie charts on seperate worksheet tabs. Now, I want to move an item from "Group A" to ...

Can I do this? #2
The formula I am using is =I17-40. What I want is for the cell to show a zero if the result of the formula is less than zero. In other words, show a zero if the result of the formula is in the negative. Can I do this. Please e-mail Lee at lee.davenport@nashcountync.gov if you can help. Thanks. =if(I17-40>0,I17-40,0) "Lee and Rebecca" wrote: > The formula I am using is =I17-40. > What I want is for the cell to show a zero if the result of the formula is > less than zero. In other words, show a zero if the result of the formula is > in the negative. Can I do this...

there should be a modified date of a single cell modifacation
there should be a modified date of a single cell modifacation ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/community/en-us/default.mspx?mid=0b09c9ca-2596-45ed-8529-3d4198bd8ed5&dg=microsoft.public.excel.newusers ??? ...

Export data to Excel
I am trying to export data from the web to excel, but excel has truncated all leading zeros of the data, i.e 0001 to 1. The default template is always general. If I change the template of excel to make all cell to text, I am able to get all leading zeros from copy and paste. Any thoughts of how to change the default excel template from the browser? Thanks! ...

Automatic filling of cells within a certain date range
Can someone please help, I have been stuck on this for the past two days... Column 'AB' has 'Start Date' and column 'AD' has 'Finish date' Row 19, cells 'AI' to 'DW' have the months written as 'J' 'F' 'M' 'A' etc.....from 2008 to 2015. I want to be able to enter a 'start date' in column 'AB' i.e. 1 January 2008 and a 'Finish date' in column 'AD' i.e. 1 April 2015. Then i would like the cells 'J - A' on row 19 to be filled with '1' in each month.... I...

Calculate sum of a field within the query
I have a query that returns the details for a failure record during a specified time period. Where if I search between 1/1/08 and 1/31/08 the query returns the records of all failure records during that period. The row shows the part number and the total parts failed for that record. I need the query to then sum the total part failures for the previous 12 months. So I would see the following: Record # | part # | parts failed | Origination Date| Total Parts failed past 12 months 44444 | x | 4 | 1/2/08 | 200 44445 | Y |1 ...

variable allocation accounts #2
i want to setup and use variable allocation accounts for my company and on the help menu it says to go to cards>>financial>>variable allocation.... but i don't have that option in cards>>financial... is it something seperate i have to buy or a security setting or...???? thanks for you help! Josh, it's standard functionality in GP. Probably a security setting needs be changed so you can see it on the menu. Frank Hamelly, MCP-GP NOVA Solutions LLC Melbourne, FL ...

how to make this work if sum=5+n2 then sum becomes the value of s.
I want to get or create a formula in excel or access that allows me to keep a running total of my supplies ie... I have 2 pens, remove one and receive 2. answer in the cell becomes 3 pens then if I zero out the received cell the inventory cell still remembers that I have 3 pens not 4 pens (because I received 2 more pens) and visa versa when I zero out the received cell it remembers I have 3 pens not 1 pen(because I removed 1 pen) I believe the method you suggest is flawed. You have no audit trail. If the number on the sheet doesn't match your actual inventory, how will you figure out...

Date ranges
Hi I have a list of predefined dates in column B in colum C formula to show todays date. (i.e. if today = 24-6-06) What I want is D1 to to show B2 (19-7-06) and D2 to show B1 (12-6-06) A B C D 1 1 12-6-06 =today() next date = 2 2 19-7-06 previous date = 3 3 24-8-06 4 4 30-9-06 Any help appreciated. Regards Paul T Paul T wrote: > I have a list of predefined dates in column B in colum C formula to show > todays date. (i.e. if today = 24-6-06) > What I want is D1 to to show B2 (19-7-06) an...

chart data from same cell on multiple sheets
how can i chart data from the same cell on multiple sheets? when i enter sheet1:sheet5!a5 i get a invalid reference error. The values for a chartseries must all come from a single chart. You need to create a summary range on a worksheet to hold the values. This page has a few hints: http://peltiertech.com/Excel/ChartsHowTo/ChartFromDiffSheets.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ drhamann wrote: > how can i chart data from the same cell on multiple sheets? when i enter >...

yahoo! #2
recently i heard, that free yahoo uswers can configure their account via outlook. many users seems to have achieved it, while i tried with the instructions, it seems to give some errors! Following are the instructions: 1.. Select Tools -> Email accounts from the main menu 2.. Select Add a new email account and click o�n Next 3.. Select POP3 as the Server Type. Click Next 4.. Enter your username and full Yahoo! email address under 'User Information' 5.. Enter your Yahoo! Login id and password under 'Logon Information' 6.. Enter 'localhost' as your Incomi...

Macro Not Working #2
Guys, many thanks for your help and suggestion my Macro now working. Having said that the same code works on my home PC but for some strang reason not on my Works PC - very Odd !! Thanks again Rob ; -- robertgu 44 Married two kid ----------------------------------------------------------------------- robertguy's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=596 View this thread: http://www.excelforum.com/showthread.php?threadid=26381 ...

Inventory Site/LocationCode Order Lines on MSCRM1.2 to GPS8.0
We are currently running MSCRM 1.2 and GPS 8.0, with the MS Integration between them. Our Sales force uses various Inventory locations when placing orders within our GPS system. Because of business reasons we can't consolidate into a single Inventory Location as the Integration documentation suggests. The documentation allow says that it only supports the use of one Inventory location, which you set in the "Settings and Administration" tool. It appears that you can modify the BizTalk Mapping for the SalesOrderDetail channel to route a value from a custom field in MSCRM...

Data entry & Formula in same cell
Is it possible to have a value entered into the same cell that the formula resides. For example... in cell A1 you enter 1 (for 1 foot)and this same cell calulates how many inches (A1 equals 12) One addition question is there a way to have a cell round up to the nearest even number? Thank you so much for your help. Hi Bridge 1. without VBA code it is not possible to enter a value and a formula at the same time into one cell. 2. If you number is stored in A1 use the formula =EVEN(A1) HTH Frank Bridge wrote: > Is it possible to have a value entered into the same cell > that the for...