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
2421 Views

Similar Articles

[PageSpeed] 58

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 with subquery as source to another query
I have created a crosstab that uses a subquery as its source and contains a PIVOT... IN clause, which executes successfully. I have a query that selects certain records from that crosstab and performs calculations on its fields, but it fails with a "Syntax error in FROM clause," highlighting the TRANSFORM verb. I tried simplifying the select query wrapper, as SELECT * FROM ([crosstab query]);, which fails with the same message. Could the subquery be so complex that it frustrates the query optimizer? (The subquery JOINs two tables and LEFT JOINs a third, and selects ...

Calculating a Date
I have two fields in a query: "Date Completed" (mm/dd/yyyy) and "Time Used (hours)" (nn.n). I am trying to calculate a third "Date Started". Assuming an eight-hour day, I am trying to use the expression [Date Completed] - [Time Used (hours)]/8 but I can't figure out which Date/Time functions to use to convert my dates and hours to the same units to do the calculation and then back to a date again. I am using Access 2007 and am not all that competent with it. Thank you in advance for your help! You can try the following. DateValue(DateAd...

Saving Queries from Address Book Views
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Email Client: Exchange I am working on a test machine with at least 40 custom search queries in Address Book View for the current profile; I would like to get a copy of 40 custom queries and apply to another system without having to re-write it again. <br><br>Anyway to export queries from Address Book Views? <br><br>OR <br><br>Where is the location that keeps these queries? Prefs? Hoping if copy and paste query file on other machine would work. <br><br>Thanks i...

tagging rows to columns???
I just created an excel catalog for my cd jukebox as I loaded the tray (300 discs). Now I want to alphabetize column A (artist) and have column B (album title) follow column A. Is there a way to do this? Thanks for all help. beo Try on a duplicate copy of your sheet: Assuming your lists are in cols A and B, data from row2 down, viz.: Artist Title ABC Text1 XYZ Text2 DEF Text3 etc Select cols A and B Click Data > Sort Check "Header row" under "My list has" Ensure settings under "Sort by" are: Artist > Ascending Click OK For the sample data ab...

Trying to calculate average call length
Usually our phone system spits out a report for me, but it's broken right now... and I have to provide this info to another department ASAP. I'm trying to calculate the average call length for our call center agents. My spreadsheet looks like this: Call Duration 00:04:39 00:00:58 00:03:18 00:04:02 The cells are custom formatted as hh:mm:ss. All I want to know is the average length for those four calls. But when I try the AVERAGE function, I get a DIV/0 error. Any ideas on what I'm doing wrong? Thanks in advance! pilates_jocelyn wrote: > Usually our phone ...

Exporting outlook data from a different harddrive
Hi, My motherboard has died so I will need to re-install windows with a new motherboard. I have a backup of my old drive, which includes my outlook 2000 directory. Once outlook is installed on the new drive, how can copy the old data in the new installation? Normally I would export as a pst, but since the directory is on a different hard disk, I'm not sure I will be able to actually run outlook on that drive to do the export. Any ideas or suggestions? Thanks in advance Thanks a lot! That makes things much simpler Normally, you should never export to a PST to back up data. Just c...

Outlook time stamp different from OS
I have a user who's outlook time stamp is out of sync with the OS time stamp. This occured at the daylight savings time switch over. Does anyone know how to resolve this? does outlook, windows, and the server all have the same time zone configuration, including the DST setting? -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com "HLi CS" <anonymous@discussions.microsof...

Calculation Help Needed
A company has 4-shifts (ie 1,2,3,4) and each shift is 1-day (24hrs), and the shifts repeat after they complete. Shift 1 to 4 is Monday to Thursday, so the following Shift 1 to 4 is Friday to Monday and this continues on a 4-day cycle. If an employee works only on shift2, how do you calculate the number of days from shift3 until his scheduled shift2. Thanks Greg On Jul 27, 11:37=A0am, "Greg (code...@gmail.com)" <code...@gmail.com> wrote: > A company has 4-shifts (ie 1,2,3,4) and each shift is 1-day (24hrs), > and the shifts repeat after > they complete. Shift 1 to 4 ...

Inventory Turnover Report Calculation Seems Incorrect...
We are running GP version 9.0 and I noticed the results from the inventory turnover report are incorrect. Regardless of what item I run the report for the results are including in the Qty sold YTD sales from Dec06. This is causing the turnover # to be incorrect also. Is this report including Dec06 sales in error because the Dec06 ending inventory #'s need to be used in the calculation of the 2007 turns? When did you do your inventory close at the end of 2006? It sounds like it might have been done early in December rather than at the end of the month. -- Richard L. Whaley Auth...

Extract different strings from a cell?
If I have "100BDDDABAABD" in a cell and I'd like to extract the last 10 characters and put each character into a different cells. Then, the first rest of characters (first 3 in this case) into another cell. How do I do that in Excel? Thanks, Since you said "first 3 in this case", I'll assume that the number of characters will vary in each cell. With data in A1, enter this in B1, and copy across to K1: =MID(RIGHT($A1,10),COLUMNS($A:A),1) Then, in L1, enter this for the remaining characters: =LEFT(A1,LEN(A1)-10) -- HTH, RD -----------------------------------...

Insert Text From Different Cell
I would like to enter a company name, such as "ABC Co" in a cell o sheet1 and then on sheet2 have a cell with text "Proposal for ABC Co". I would also like to be wildly rich. Can anyone help with either o those -- Jorad ----------------------------------------------------------------------- Joradi's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2686 View this thread: http://www.excelforum.com/showthread.php?threadid=40105 Lets say you manually enter in Sheet1, cell A1 ABC Co In sheet2, cell A1, enter ="Proposal for " & Sheet1...

Query Date Range
How can I use Between/And to prompt for date range that includes the start and end date? Hi, Design a select query for your table. add all fields to the design grid. In criteria box of the date field write this Between #BeginningDate# And #EndingDate# and run the query to find results. JB "karenfocus" wrote: > How can I use Between/And to prompt for date range that includes the start > and end date? My current criteria is in a query date field and is written Between [Enter start date] And [Enter end date] but when I run the query from the first day of the month to the...

Running Windows Media Player from MFC Application Query?
Hi all I am making an application in which i displaying in a list control all the mp3 files belonging to a folder selected by the user. I want to add the following functionality. As soon as the user double clicks on any of the displayed MP3 file, i want to invoke the windows Media Player and play that MP3 file. Now how i invoke the Windows Media Player for my MFC Application? Waiting for suggestions Regards ...

row shrinkage
On two workbooks in a worksheet I use I am having a problem with some rows shrinking to where i can't see them. They are different rows in each workbook. (4,5) and (6,12,13,17). I don't have anything set to hide. Everytime i manually set the row height and save the file as soon as I open it the rows have shrunk again. Is there a setting I am missing. I've seen a few posts that complain about columnwidth changing (usually in some version of xl97), but never a post about the rowheight changing. Any chance you have a macro running that changes the rowheights? Try opening excel ...

default row height and column widths
I need to paste alarge amount of text into a cell without affecting the width of column or height of row Hi, I couldn't get my copy of Excel (2003) to do otherwise, without choosing either Format> Cells> Alignment> Wrap text or Format> Column> AutoFit Selection AFTER pasting text into the cell. Are you saying this is not your experience? PS: posting your e-mail address in a public forum is a good way to get both spam and viruses sent to it. -IanRoy "purchasing@overstocks.com.au" wrote: > I need to paste alarge amount of text into a cell without affecting ...

Find Duplicates Query
I have a Find Duplicates Query based on the "Name Field". Some sample results are below. John Doe 12/3/2007 John Doe 12/15/2007 John Doe 1/28/2008 Is there a way to further restrict results to only return results that the date fields are within 30 days of other? So in the above example, the bottom result would not be returned because it is not within 30 days of any other results. Any help is greatly appreciated. Thanks, Matt. Use a subquery to identify whether there is another record within 30 days. This kind of thing: SELECT ID, [TheName], [TheDate] FROM ...

"default" calculation
A1= sub-total 1 A2= sub-total 2 A3=total; that is, sum(A1,A2) .....but in case I want to manually enter A3 (i.e. because there is another additional cost for some reason), I want the user to have that option. However, if the user presses delete on A3, or starts typing something but then backspaces and presses return but then does not enter any new data (perhaps because they realize that they just want to use the formula), rather than have the formula deleted and being left with a completely blank cell, i woud like the formula to recaculate and have that calculated value appear in A3. ...

Vlookup next row
I am attempting to use vlookup with values that have many decimal places. The problem is they are close enough for an exact match so I must use an approximate. I always get a value that is one before the value I wish to have because it takes a value lesser than the one I am trying to match. Is there a way I can get it to take the value in the next row? The numbers are in numerical order. Thanks One way: Instead of =VLOOKUP(A1,J:K,2,TRUE) use =INDEX(K:K,MATCH(A1,J:J,TRUE)+1) In article <4363DF28-A994-4D9A-9C84-3E3AA7BC6800@microsoft.com>, "Mark" <Mar...

How Calculate How Many Toolbars Will Fit On a Frame "Row"
Hello, I have several toolbars that I'd like to display on as few "frame rows" as possible. Since the User may change display resolution I can't hardcode which toolbars should be displayed on a row and I therefore need to calculate how many will fit at runtime. To do this I get the size in screen units of the Frame that holds the toobars and then, for each toolbar, I get the size (again in screen units) of the toolbar and use that to calculate how much "space is remaining" on the row. If there's enough room left I add the toolbar to the row and if not I star...

Two identical formulas for a different result?
Hello everyone, :) A very quick question, but nonetheless very intriguing to me (I jus lost 4h on that :( ) I don't understand why my first formula works, and the second and thir don't. {=SUM(IF(FiscYear="2003",IF(IncomeFeeID="RB",Amount,0),0))} =SUMPRODUCT((FiscYear="2003")*(IncomeFeeID="RB")*Amount) (result is #value) =SUMPRODUCT((FiscYear="2003")*(IncomeFeeID="RB"),Amount) (this one could work too I thought, but result in a 0) I am simply trying to sum (amount) when the fiscal year is 2003 an IncomeFeeId is RB......

Mailbox in a different AD forest
Hi all We've just created a second Active Directory forest "domain3" (win2k3) on our network and setup a 2-way trust with our exisiting domain "domain2.domain1" which contains an Exchange 2003 server. Can I setup mailboxes for the domain3 users in the domain2.domain1 Exchange server? Ideally I do not want to create disabled accounts in domain2.domain1and then associate their mailboxes with accounts in domain3, or deploy an Exchange server in domain3. I just want to host the domain3/user mailbox on the domain2.domain1 Exch2k3 server. So far I haven't read an...

How do i select different cells to be use in a formula.
Hi I need to know how to select different cells to be used in a formula, but i don't know what is the separator that i need to use to this, see the example bellow Cells that i need to include on my formula: A1 C5 BH32 Thanks. Hi you didn't mention what formula but here's a starting point: =AVERAGE(A1,C5,BH32) or depending on your regional settings =AVERAGE(A1;C5;BH32) hope this helps Cheers JulieD "Manuel" <Manuel@discussions.microsoft.com> wrote in message news:A855A562-725C-4B10-B111-A88F7A767122@microsoft.com... > Hi > > I need to know ho...

Product key for different language
Just ordered a second copy of Office 2004 for Mac and with the shipment confirmation of the shop I have noticed that I have ordered the wrong language version and not the English one. As I already have a copy of the English Office 2004 I was wondering if it is possible to use the product key from the new non-english version for an installation from the english cd. In other words: can I use (technically and legally) the license which I have bought now to install a second copy of the english version or do I have to return the non-english version to swap it for the english one? I would ra...

won't calculate far enough
Hello, I have a formula (see below) that will work for rows 2-1000, but once I change it to look for columns beyond 2000, it will not work anymore... Any suggestions? This works: =SUMPRODUCT(--('Q Info'!$A$2:$A$1000= 'Per Peice'!A9),'Q Info'!$C$2:$C$1000*'Q Info'!$B$2:$B$1000) This DOESN'T work: =SUMPRODUCT(--('Q Info'!$A$2:$A$9999= 'Per Peice'!A9),'Q Info'!$C$2:$C$9999*'Q Info'!$B$2:$B$9999) -- Nicki Taylor Both you formulas work for me. -- HTH Sandy sandymann2@mailinator.com Replace@mailinator.com with @tiscal...

shared pop3 account with different outlook clients.
Hi! We are using different versions of Outlook. The versions used are Outlook Express 5.5, 6.0sp1 and Outlook 2000 and Outlook XP. The problem is a shared pop3 account, which all the clients receive (and leave a copy to). Some clients receive the mail properly, but some clients receive corrupt messages, i.e. messages with no subject and sender fields and a blank message body. Now, i found a corresponding KB article, but we dont have any IE 6.0:s installed. Is there any other software which might have that mlang.dll problem, or should i upgrade all browsers to 6.0SP1? Thanks in adv...