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

Similar Articles

[PageSpeed] 4

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:

Query
I have a transaction # for each record in my main form with a subform "Approvals" and "Checkouts". I created a query to show the sum of all Approvals and all Checkouts by Transaction # for each record. (one for Accruals and one for Checkouts). All approvals and checkouts come up in these queries. I want to create a report showing, per Cost Center, the $ amt of Approvals, the $ amt of Checkouts, and a calculated field to show the remaining value. The report comes up with all approvals and checkouts per cost center, however, if there is an approval that does not hav...

Deleting Non-Duplicate Rows
Have done tihs is the past, but can't remember how: Have a sheet with 9500+ rows. Column C contains a storage bin number. Want to delete all rows that DO NOT have a duplicate (trying to resolve items that have a duplicate bin number.) Have sorted the sheet on Column C. THX. . . -- BillW ------------------------------------------------------------------------ BillW's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27516 View this thread: http://www.excelforum.com/showthread.php?threadid=470299 You could use a helper column of formulas: =countif(c:c,c1)...

Autofit Row Height #3
Hello, I have a column on sheet 1 that is set to wrap text so that the row height increases and decreases as more text is entered into the cell. This works fine. Users enter a number on sheet two where there is a lookup function that returns the appropriate text from sheet 1. The problem is that when the text is returned by the function, the row height does not adjust to fully display all of the text in the cell. Is there a way of automating this? Any help would be appreciated. -- Thanks, MarkN ...

Setting random number range based on query, not table
I have a "quiz" form that randomly pulls up questions from my table. It works fine, and I don't mind that it repeats questions. This allows me to sit and review for as long as I want. The problem is, that I want to use criteria in a query to limit the available questions. If I base this form on a query and limit the number of questions, the code I am using is still setting the maximum number as the number of records in my table. In the past, I had a similar database (which I have lost) that would open the form, set the selector to the last record available to the form,...

too many different formatting cells
I can't open an excel document because whem I try to open it says that too many different formatting cells. How to resolve this and open this document? Maybe... XL: Error Message: Too Many Different Cell Formats http://support.microsoft.com/default.aspx?scid=213904 A few people have said that OpenOffice.Org has been able to open the file. Then they clean it up and save it there. Then excel can open that cleaned up version. http://www.openoffice.org, a 60-104 meg download or a CD jo wrote: > > I can't open an excel document because whem I try to open it says that too >...

Big difference in performance
I wrote a class on which I perform unit-testing before integration in the main development line, the unit-testing and main dev being two separate projects. I wrote a routine which constructs a data structure in the class, which uses CArrays, vectors, and the nth_element routine from STL, with the routine being recursive. Now, in the unit-test project, the routine takes ~3-4 seconds to execute. In the main development code, it takes ~70 seconds to execute. This is measured for the routine itself (no setup or cleanup), same data as input, same computer, and both projects compiled for debug. ...

Compare and Highlight Rows
I have an excel file with two worksheets. is there a way to programatically compare the two worksheets to find matches and highlight those matches on each worksheet. They both have the same columns. I want to compare the InspectionID column. The Inspection ID column may have duplicates in both of the worksheets. I have never done anything in excel above the beginner level. However, I am a very skilled VBA programmer (programmed in ACCESS for over 10 years). Thank you You may find it easier to use Conditional formatting instead: For instance, with Sheet1 column A selected, and cel...

Difference between XML templates and Active Reports
Why does RMS use XML templates (for receipts and P.O.s) and for most other reports Active Reports? I realize that the XML receipts can not be changed or resorted on the preview screen, but why use not all active reports? Active reports are based on formating the results of a SQL Query - if you can work out the SQL to get the result set you want, you can create almost any report you want. The XML Templates are based on objects internal to RMS, like the current transaction for reciept printing, the active PO or Transfer request for PO prining, or a given customer for Statements. Y...

location of web query
I have a file that refreshed 2006 data that I am now changing to 2007 but I cannot recall where a web query is on a certain worksheet, is there an easy way to find in which cell the query resides? ...

Crosstab query totals
Need some help, I have a crosstab query that returns the following data see sql below, what I need is to total all gearbox types that are R and 37 and R and 27 and R and 47 etc. an example R and RF 27 total = 492 <> 1 2 4 SubTotals R 27 20 26 303 12 361 R 37 6 66 307 8 387 R 47 8 87 424 2 521 R47R 37 2 2 R57R 37 3 2 5 R67R 37 1 1 2 R77R 37 19 19 RF 27 4 17 109 1 131 RF 37 1 11 76 88 RF 47 1 25 39 65 TRANSFORM Sum(InspectionLog.Quantity) AS SumOfQuantity SELECT Sum(InspectionLog.Quantity) AS SubTotals, InspectionLog.GearboxType ...

Access 2007 SQL Pass Through Queries
I have an Access 2003 mdb that I use to connect to a SQL 2005 database. When I open the application in Access 2007 it errors on using SQL pass through queries. I see that pass through queries (amongst other options) have been disabled under Access 2007. However it seems that they are enabled under certain circumstances so I've been trying to get them working by changing the security settings. The queries are still failing after setting the following: - Sandbox Mode to 0 (turn off sandbox mode for all apps) - Applied a recognised Digital Signature - Trusted the Publisher - Trusted the loc...

Anyone Else Use Database Query to Query Another Sheet in the Same Excel Workbook?
Wow! I tried this out, and it seems to work beautifully. It's odd, though, that I haven't seen this technique mentioned in any of the several Excel references that I've looked at. Basically, I have a workbook with several worksheets...one worksheet contains a large list with all the records. I wanted to set up the other worksheets to have certain functions...I wanted them to use only certain columns from the main list, and to contain only certain records from the main list that met specified conditions. This is the best solution I have found so far - i.e., creating database q...

Date/Time field calculation
Hello, i am trying to make a sum calculation on a field that keeps duration of time. How can i do this? I get errors or zero as result. Thanks On Jun 21, 10:02 am, Stathis <s_paraske...@hotmail.com> wrote: > Hello, > i am trying to make a sum calculation on a field that keeps duration of > time. How can i do this? I get errors or zero as result. > Thanks You need to do the calculation at a level at least 1 level below what you want the answer to be in. Example msgbox DateDiff("h", #6/20/2007 1:00:00 AM#, #6/20/2007 1:30:00 PM#) will give an ans...

conditional formating: ifs and highlighting rows
my spreadsheet documents error incidents, with each row showing the date the incident was discovered (column A) and the date it was resolved (column I). it also calculates networkdays (column J) -- unless column I=0 -- and references an array of holiday dates on another sheet. i would like to create a conditional format that will identify rows with an incident, but no resolution date, then highlight the row and possibly even show "unresolved" in column J cell of that row. Thanks! Well, you have to decide which way you want to go with this. If you leave column J ...

How do I find duplicate rows in a list in Excel, and not delete it
I have a long list of data in Excel that is 3 columns wide. I need to find and save only the duplicate rows but don't want to delete them. Instead, I could delete the unique rows and keep the duplicate rows. In the customer assistance, I found out how to delete duplicate rows and save the unique rows, but this is exactly opposite of what I want to do. This is one option: =COUNTIF($A$4:$A$18,A4)>1 copy down, and use a Autofilter to find all True HTH Ola Sandstr´┐Żm Picture encl.: http://www.excelforum.com/attachment.php?attachmentid=3498&stc=1 +---------------------------...

wrapping text in a query field
I set up the field in table to memo and tried entering a lot of information but when I open the report that field does not expand to show all of the entries. Can this be done in query and reports both. Thanking you in advance. Mary Lou On Dec 12, 12:06 pm, MaryLou <Mary...@discussions.microsoft.com> wrote: > I set up the field in table to memo and tried entering a lot of information > but when I open the report that field does not expand to show all of the > entries. Can this be done in query and reports both. > > Thanking you in advance. > Mary Lou Go to the p...

Can you help me with a troublesome query?
I'd like some help formulating a query for the following situation. The problem I actually have to solve is somewhat complicated, so I've extracted the essence of the problem into the simplified situation described below. While I know I could do this with an ugly cursor solution, I'm pretty sure that performance would be terrible as the table grows to a large size, which it will in the real problem I have to solve. So I'd like to hear your best ideas on how to solve this in a way that will scale to as high as a million records or more. Consider this table: ...

Query plan isn't doing an index seek
If you have the following: SELECT WorkID,VendorName = (SELECT VendorName FROM Vendor WHERE VendorID = ws.VendorID) FROM Work ws What I get is a Hash Match from 2 index scans. |--Compute Scalar(DEFINE:(dbo].[VENDOR].[VendorName]=[dbo].[VENDOR].[VendorName])) |--Hash Match(Right Outer Join, HASH:([dbo].[Z_VENDOR].[VendorID])=([ws].[VendorId]), RESIDUAL:([dbo].[VENDOR].[VendorID]=[dbo].[WORK].[VendorId] as [ws].[VendorId])) |--Clustered Index Scan(OBJECT:([dbo].[VENDOR].[PK_VENDOR])) |--Clustered Index Scan(OBJECT:([dbo].[WORK].[PK_Work] AS ...

Data/Values change when Query Analyzed in Excel
I have a query with 3 tables that represent master item list, count qty and as qty. When I look at the results in Query, all is well and accurate yet when I select Analyze with Excel, values change. For example, if a record shows zero qty in the as of field and zero quantity in the count field, I get a value in one or both of the fields that origianlly were zero. Other than linking the tables and grouping by master item list (to show all parts regardless of qty's), there are no formulas or expressions in this query. One of the tables is linked to a FoxPro table via ODBC driver. ...

IF two different conditions are met
How do I enter a formula where two different conditions have to be met? I have a date in G3 and either "complete" or "incomplete" in H3. I wan to put a formula in I3 that states if G3 has a date and H3 i "complete" then return "Yes" otherwise return "No". Can it be done -- Message posted from http://www.ExcelForum.com Hi try =IF(AND(ISNUMBER(G3);H3="complete");"Yes";"No") -- Regards Frank Kabel Frankfurt, Germany > How do I enter a formula where two different conditions have to be > met? > > ...

Need help to structure simple (I think) query...
I think this is a simple query but I am not well-versed in SQL so I cannot figure this out. I have the following tables (I show only the relevant stuff): Documents PK_DocumentID Reviews PK_ReviewID FK_DocumentID FK_ReviewStatusID The relationship is one-to-many: One Document can have many Reviews. A Review has a Status: 1 = Not Started 2 = Open 3 = Closed For ALL the Reviews associated with a Document, there can be only ONE Review that has a non-closed status. Sometimes this condition does exist, which results in a data integrity problem, so I want to identify th...

Can 2 Union Queries Be used in Another UNION ??
I have created 2 separate Union Queries that work just fine. Can these 2 union queries be used in Another Union query (that will essentially combine the output of those 2)? Thanks very much, Kev -- Message posted via http://www.accessmonster.com Yes, as long as the two union queries are outputting data in the same structure and as long as the query engine doesn't decide that the combination of the union queries is too complex. Have you tried to do this? If so, did it fail? And if it failed were there any error messages? -- John Spencer Access MVP 2002-2005, 2007 Center for H...

Column and Row reference
How do i hide the column and row reference number in a sheet, when viewing the worksheet i do not to show the top and right hand side references Dave Tools>Options>View. Uncheck "row and column headers". Good idea to spend a few minutes browsing through the various Tools>Options tabs to see what else is available to toggle on/off. Gord Dibben Excel MVP On Mon, 19 Jul 2004 15:53:02 -0700, "Dave" <Dave@discussions.microsoft.com> wrote: >How do i hide the column and row reference number in a sheet, when viewing the worksheet i do not to show the top an...

How do I pivot a range of cells, i.e. from column to row?
How do I pivot/transpose the data from a range of cells from, say, a column to a row? I imagine it should be very simple, but I haven't found out how yet. For instance: row# 1 data 1 2 data 2 3 data 3 4 data 4 to column# 1 2 3 4 data1 data2 data3 data4 ...

Column, Row Font Size
For some reason the column letters and the row numbers - at the very top and far left on any worksheet - have become so small on a particular worksheet as to be illegible. What have I done, and how do I undo it? If it only happens on one worksheet, maybe you've set the zoom too small. From the menu bar: View|Zoom... (and resize to look nice) There's a Zoom icon on the Standard toolbar that you could use, too. (My icon is at the far right of that toolbar.) Old Red One wrote: > > For some reason the column letters and the row numbers - at the very top and > far left ...