Calculate difference in rows in a query

I have a query that calculates how much revenue is claimed by month per job 
but then I have to take the new month less the prior month.  The problem is 
the data is in row format.  I don't know how to subtract February from 
January, March from but don't know how. I know how to do it in Excel but not 
Access. Please help...

Example:
Order	Month	JTD Clm	Variance
101026521	January	$511,525 	$0 
101026521	February	$511,525 	$0 
101029438	January	$1,238 	$0 
101029438	February	$3,713 	$2,475 
101033168	January	$21,465 	$0 
101033168	February	$51,460 	$29,995 
101034011	January	$47,524 	$0 
101034011	February	$48,407 	$883 

0
Utf
3/2/2010 11:44:01 PM
access.queries 6343 articles. 1 followers. Follow

2 Replies
2467 Views

Similar Articles

[PageSpeed] 47

Access will need to distinguish which record to use when subtracting so 
instead of month you need a DateTime field.
Then try this query --
SELECT Order, OrderDate, [JTD Clm], (SELECT YourTable.[JTD Clm] - [XX].[JTD 
Clm] FROM YourTable AS [XX] WHERE YourTable.Order = [XX].Order AND      
Format(YourTable.OrderDate, "yyyymm") = Format(DateAdd("m", -1, 
[XX].OrderDate),"yyyymm")) AS  Variance
FROM YourTable 
ORDER BY Order, OrderDate;

-- 
Build a little, test a little.


"Glenna" wrote:

> I have a query that calculates how much revenue is claimed by month per job 
> but then I have to take the new month less the prior month.  The problem is 
> the data is in row format.  I don't know how to subtract February from 
> January, March from but don't know how. I know how to do it in Excel but not 
> Access. Please help...
> 
> Example:
> Order	Month	JTD Clm	Variance
> 101026521	January	$511,525 	$0 
> 101026521	February	$511,525 	$0 
> 101029438	January	$1,238 	$0 
> 101029438	February	$3,713 	$2,475 
> 101033168	January	$21,465 	$0 
> 101033168	February	$51,460 	$29,995 
> 101034011	January	$47,524 	$0 
> 101034011	February	$48,407 	$883 
> 
0
Utf
3/3/2010 12:28:02 AM
As you don't include the year in a column this suggests that the query only
returns data for one calendar year.  Assuming this to be the case you'll then
have to force the month values to return a true date/time value of the first
of each month to compare them.  Try this, which is based on your current
query, called YourQuery in this example:

SELECT [Order], [Month], [JTD Clm], NZ([JTD Clm] -
   (SELECT [JTD Clm]
     FROM [YourQuery] AS Q2
     WHERE Q2.[Order] = Q1.[Order]
     AND CDATE("1 " & [Month]) =
        (SELECT MAX(CDATE("1 " & [Month]))
          FROM [YourQuery] AS Q3
          WHERE Q3.[Order] = Q2.[Order]
          AND CDATE("1 " & Q3.[Month]) < 
          CDATE("1 " & Q2.[Month]))),0)
AS Variance
FROM [YourQuery] As Q1
ORDER BY [Order], CDATE("1 " & [Month]);

Ken Sheridan
Stafford, England

Glenna wrote:
>I have a query that calculates how much revenue is claimed by month per job 
>but then I have to take the new month less the prior month.  The problem is 
>the data is in row format.  I don't know how to subtract February from 
>January, March from but don't know how. I know how to do it in Excel but not 
>Access. Please help...
>
>Example:
>Order	Month	JTD Clm	Variance
>101026521	January	$511,525 	$0 
>101026521	February	$511,525 	$0 
>101029438	January	$1,238 	$0 
>101029438	February	$3,713 	$2,475 
>101033168	January	$21,465 	$0 
>101033168	February	$51,460 	$29,995 
>101034011	January	$47,524 	$0 
>101034011	February	$48,407 	$883

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

0
KenSheridan
3/3/2010 12:32:43 AM
Reply:

Similar Artilces:

Crosstab query 06-22-07
Hi, All I have a table with following fields: IssDate IssTime Tick Badge Locat Viol 6/18/07 15:30 102003 999 MAIN 141 6/19/07 16:30 102023 999 MAIN 151 6/22/07 17:30 102022 999 MAIN 146 Is it posible in the crosstab query show summary for the every day of the week with actual date assign like "Sunday 6/17/07" and have as many columns as 7 for every day? Please, help How can i produce produce following result: Badge Sunday Monday Tuesday Wednesday Thursday ...... 6/17/07 ...

Problem of calculation with excel
Hi everybody! I have the following problem with VBA and Excel. I have built a function with an array as input and returning another array. The function works properly when being used in one sheet. But when I go to another sheet, recalculate (F9) and then come back to the first sheet, all my values disappear and I have 0 everywhere instead of the right values. This problem only occurs when many columns use my used defined function, so it may be a memory pb but I don t know Can anybody tell me where this problem comes from? Thanks Alex Here is the code of my function : Function MaxDrawdo...

When I send a spreadsheet via e-mail they see it differently?
I created a spreadsheet which contains some merged cells. When I send the same spreadsheet to my coworker in Florida via email he sees all the lines within the merged cells making it really hard to read. How can I fix this? The gridlines should not be visible within merged cells when viewed i Excel, but might be seen in some viewers. What product/version is bein used in Florida to view your file -- Bryan Hesse ----------------------------------------------------------------------- Bryan Hessey's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2105 View this ...

Why is Access VBA syntax different from Excel syntax?
I just started using Access VBA 2003 and I came across 2 very odd things: Why is the syntax different from Excel? In my Excel macros I use the Microsoft ActiveX Data Objects 2.8 Library. Here is a code sample in Excel: Dim cnn As New Connection Dim rst As New Recordset Dim strSQL As String cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=test.mdb" Set rst = Nothing strSQL = "SELECT * FROM Table1" rst.Open strSQL, cnn When I tried this in Access it didn't like the "New" qualifier and Open was not a function of the recordset class. W...

Export rows in Excel to lutiple Word files
I have an excel (or access if easier) doc that contains rows of data. I would like to create a separate word doc from each row. The clomuns are subject,number,info I would love the title of each to be "<Number>-<Subject>" with the content of the doc itself made up from the Info row. Is there a way to do this - I know a bit of perl but was hoping for an easy solution....... Try this. Select the row Copy to the clip board Switch to MS Word Use either Paste to paste the clipboard contents as a table or Paste Special with the Link Option selected. Perhaps specify as...

Can Query-Based Distribution Groups within Exchange 2003 be used to email all the members of a (non mail enabled) security group?
Can Query-Based Distribution Groups within Exchange 2003 be used to email all the members of a (non mail enabled) security group? It would be really handy for me as a system admin to email all the members of a security group who use certain applications very occasionally, without having to mail enable the group. I was wondering if you can do this using the QBDGs feature. I have just played with it, and it did not work - but it is entirely possible that I did something wrong? Incidentally, my domain is 2000, even though the Exchange server is running W2K3 and Exchange 2K3. Any thoughts? Thank...

Hide subsequent rows based on pull down list choice
I have a spreadsheet that has 9 resource concerns listed. Next to each concern is a cell with a drop down list (data validation list) where the user can choose high, medium or low. Below each resource concern are rows that have questions associated to the concern that they need to answer if they choose high or medium. I need a macro that will unhide the rows below the each concern if high or medium is chosen in the drop down list, but keep the rows hidden if the user chooses low. It needs to update each time the list is pulled down, so that if they initially choose low, but...

Server Query
We migrated to another exchange server after merging with another company. We took the old server off-line and some users are still trying to query that server and it locks there system up. I have removed the profiles; rebooted and then recreated the profile and email account and still get the same issue. Has anyone else seen this or know how to fix it. There is no pointer in a host file either. Thanks. ...

duplicate numbers being given different rankings
Have list of percentages which have been sourced from another sheet within an excel document - when i rank these some duplicates are given the same ranking and some are given different rankings eg. 2 results of 6.16% were both ranked 13 while 2 results of 6.11% were ranked 10 & 11 Increase the number of decimals to see whether the cells contents are exactly same or is rounded off and displayed as 6.11 -- Jacob "Mantis" wrote: > Have list of percentages which have been sourced from another sheet within an > excel document - when i rank these some dup...

Help with query 01-22-10
I have a table (joined to another) to maintain record of the pieces changed in an instrument. The table contains the following fields; InsID, Pos1, Pos 2…..Pos 6 and Date Installed. An example of the information in the table is as follow: InsID Pos 1 Pos 2 Pos 3 Pos 4 Pos 5 Pos 6 Date Installed 1 QA2 QA5 QA6 QA8 QA9 QA3 01/01/09 1 N/A QA11 N/A N/A QA20 N/A 02/15/09 2 QA40 QA25 QA30 QA12 QA22 QA35 02/20/09 2 ...

IRR the same for very different cash flows
I am computing the IRR for two time series of cash flows. For the first 24 months, the cash flows are identical. After that, the longer one continues with only large positive values for 18 more years. Strangely, I am getting the same IRR for both cash flows. This does not seem intuitively correct, since the NPV of the two cash flows is quite different. Can anyone shed some light on this? Thanks. On Nov 7, 9:51=A0am, JG Scott <jgsco...@bellsouth.net> wrote: > I am computing the IRR for two time series of cash flows. =A0For the > first 24 months, the cash flows are identical. =...

How do we make each row of data into its own chart?
We have a spreadsheet where each row of data is about a separate company. We need to make each company its own chart showing the data in the row. How do we create multiple charts with one source document? It is a large amount of data and we do not want to create a chart 400 times. Pivot tables are faairly good at this additionally you could make a look up atble which would lookup each company one at a time and plot the look-up table row. "MGalbreth" wrote: > We have a spreadsheet where each row of data is about a separate company. We > need to make each company its ...

Creating a macro commad to shift selected rows to the right next c
Pls Help.... The command for cutting and pasting a single row looks likes this Range("A746:H746").Select Selection.Cut Range("B746").Select ActiveSheet.Paste There again for another row i need to do the same thing as what i had did for the pervious. Range("A748:H748").Select Selection.Cut Range("B748").Select ActiveSheet.Paste Range("C749").Select And i had more than hundred thousand of selected rows to be shift to the right, cause is a data collected from vendor and the complie data is some thing...

VBA-Protect Row
Hi All, I want to protect a row when a specific value is entered into a cell. For example, when column Final is changed from (blank or No) to Yes, that row is protected (in addition to the a function already programmed). {code for function already programmed ''''When Study Final is changed to Yes If Target.Column = 29 Then If Cells(Target.Row, 29).Value = "Yes" Then ''''Backlog set to 0 Cells(Target.Row, 54).FormulaR1C1 = 0 ''''Protect Row '????? ...

Row Autofit on Merged Cells
There is a cell in a row. The cell is merged with Columns C, D, E. When I apply Autofit to the whole row, some of the content in the merged cell is hidden. For example, the content is "A person printed 5 copies of menu and give it to the human resource department." When I apply Autofit, I can only see "A person printed 5 copies of menu" and the rest can not be seen. But when I select the cell, the whole content is displayed in the content area under menu buttons. I am dealing with large documents and I cannot apply Autofit row by row. And I copy the data from I...

Copying part of Spreadsheet while keeping Columns Widths and Rows Heights
I want to copy part of an existing spreadsheet to a new empty sheet. The normal Copy & Paste do not carry the Columns Widths and the Rows Heights to the new sheet, hence they have to be adjusted manually. Does anyone have a way to make this possible? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ In Excel XP, I made this work by doing a sort of a "double copy" Copy what you want, then select Edit, Paste Special, All. That copies the data. (I found t...

Run a Query on External data(ODBC) criteria based on input of a ce
I have an access database that I would like to put a DATE into a cell and have it auto run a query with that date criteria. I have done this with the query wizard and manualy but would like to automate the process by just typing the date and pressing the enter any one who could lead me in the right direction or who has done this before I would appreciate the help -- thank you TR Hi As an example here is a part of the procedure, I used for such a task (with ODBC for VisualFox tables, but only query syntax is affected by driver used) Public Sub RefreshQueryes() month= ActiveSheet.Ra...

Help w/ summarizing a mess of excel data from different sheets?
I'm not sure if this is the right place to post this. I really appreciate any and all help from you experts!! So here's the example: I have a mess of data sheets with sales numbers from a lemonade stand. The sheets list the seller in rows (Johnny, Bobby, Sue), and then have several columns for each seller listing what each seller sold: lemonade, coke, pretzels...and how many of each of those they sold. These sellers and their sales numbers are listed on several worksheets in several workbooks from different weekends and different seasons. I need to combine all this information t...

Entry data sheet automaticlly updates or adds data to differant sh
I have a workbook with several sheets these sheets are all the same eccept for the title. Colunms are Name, heat one, heat two, heat three, total. then I have a sheet that the colunms are Name, total How can I make the last sheet with the Name, and total more automatated. I would like for the Name colunm to be a dropdown of some sort to where it looks at all the other sheets for names when you see the name you want you click it. it then fills in the field with that name and puts the total from the sheet where the name came from in the total field. I could provide the xls file if ...

query comes up blank
Could somebody please look at my query. I've looked and looked and tried changing it all different ways, but I can't work out why I'm not getting any data. My query includes the following field Afternoon Shift: IIf([Sleepover]=True Or [ServiceDate]=[PhDate] Or Format([ServiceDate],"ddd")="Sat" Or Format([ServiceDate],"ddd")="Sun",0,IIf([EndTime]>Format(#8:00:00 PM#,"Short Time") And [EndTime]<=Format(#12:00:00 AM#,"Short Time"),Format([Hrs],"Fixed"),0)) On Tue, 12 Jan 2010 20:26:01 -0800, M...

Excel Pivot Table Row Subtotal
Everybody, I would like to change the Type subtotal to a difference between the Actual and Forcast. I have the field Part Number and Type in the row section, Month in the column selection and the sum of qty in the data selection. Can the Pivot table do what I am looking for. I have a pivot table similar to this: January | February Part Number | Type | -------------------- 123456 | Actual | 120 | 240 | Forcast | 150 | 230 123456 Total | 270 | 470 234567 | Actual | 100 | 500 | Forcast | ...

how to calculate the area under a curve
I have data in excel in cells A10 through A100. I want to obtain the area under this curve. If you are ready for the math, I explain three methods on my website http://people.stfx.ca/bliengme/ExcelTips/AreaUnderCurve.htm best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "sealman9" <sealman9@discussions.microsoft.com> wrote in message news:06A40205-8B63-43FA-9D83-21F02BFFB2C2@microsoft.com... > I have data in excel in cells A10 through A100. I want to obtain the area > under this curve. ...

how to unit data in different sheet into one sheet?
I have same style data in differet sheet: sheet1 q ww w ww e r r e t r (and adding) sheet2 5 f 6 g d h e h g juh (adding) and in sheet3 I want automatic display: q ww w ww e r r e t r 5 f 6 g d h e h g juh any solutions? One play via non-array formulas .. In Sheet1, data is assumed in cols A & B, from row1 down Use an empty col to the right, say col E Put in E1: =IF(COUNTBLANK(A1:B1)=2,"",ROW()) Copy E1 down to say, E100, to cover the max expected data in cols A & B In Sheet2, data is assumed in cols A & B, from row1 down Use an empty col to the right, say col E...

Differences from Money 2000
I've recently upgraded from Money 2000 to 2006 Deluxe. There has been a change that I'm kind of dissappointed with. In the BILLS section Money 2000 would show account totals at the bottom of the page. You could select future upcoming bills and/or deposits and view the totals for the account. This was very handy. Is there anyway to get this feature with Money 2006? Also, I don't do online banking. Money 2006 will immediately attempt to go online when I start it. Is there anyway to disable this action? Thanks. Unfortunately that feature isn't there in 2006. A lot of folks...

Solution needed to sort via button after deleting rows
Hello all, I have a spreadsheet where I have 6 different sets of data that need to be sorted often. This data changes often so I want to do this via a button users can simply click on to sort. Someone here helped me add the buttons and assign a macro to do the sorting and things work fine as long as the spreadsheet stays as is. The problem we couldn't overcome was things go wrong when I have to add or delete some rows within the set of data. For example: I've assigned a macro to a button to sort R58 down to R77 and things work fine. But, when I delete Rows 67, 68, 69, and 70 ...