Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2005
    Location
    Minnesota
    Posts
    42

    Unanswered: Design Query(w subquery?) that can produce Maximum Year By Org

    I'm struggling with creating a maximum year for an org and getting that amount for fiscal year <="2014". I can easily do it in 2 queries but am thinking there is a way to do it in a subquery within the main query thus only having 1 query that does all the work.

    I am attaching a spreadsheet with the full table - tbl_budget_E772A as a tab, QRY1 sql and results where i try but fail with subquery to get the results, QRY 2 And QRY3 results where I get exactly what i desire. I have the third query running off the second one.

    Can someone do this in one query and explain in laymans terms if possible what they are doing?

    Thanks.
    Attached Files Attached Files

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    not knowing your table design, your columns and data it hard to say. postign an Excle file in this case just doesn't help explain the problem or provide test data to construct a query

    you can use a query inside an standard jet query providng you can form a JOIN. failing that a sub select should be capable of doing what you want

    failing that you could possibly do this as a separate item in the form or f report or whatever is consuming the data
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Aug 2012
    Posts
    45
    John, you were on the right track. If you were not using Access, you could use a CTE (google it) for your 'base' query, but we can get the same results with a correllated sub query. I used a real text editor to format this, but I know it will look like a garbled pile of junk once you paste it into Access's horribly worthless editor. Anyhow, I think this is what you need..
    Code:
    SELECT * FROM 
       (
    	SELECT 
    		DIVISION, 
    		DEPTNBR, 
    		ORG, 
    		Fiscal_Year, 
    		YEAR_MONTH_RPTG, 
    		OPN_CODE, 
    		SUM(Budget_Amt) AS SumOfBudget_Amt
    	FROM tbl_budget_E772A
    	WHERE OPN_CODE = 'M'
    	AND Fiscal_Year <= '2014'
    	AND YEAR_MONTH_RPTG = '201406'
    	GROUP BY DIVISION,DEPTNBR,ORG,Fiscal_Year,YEAR_MONTH_RPTG,OPN_CODE
    	) t1
    WHERE Fiscal_year = 
    (SELECT MAX(Fiscal_Year) 
       FROM tbl_budget_E772A t2
      WHERE t1.ORG = t2.ORG
        AND t2.YEAR_MONTH_RPTG = '201406'
        AND t2.Fiscal_year <= '2014')
    One thing to note, the HAVING clause is like a where clause for a column to which an aggregate function has been applied. Since you were not doing that, I moved all your HAVING conditions to WHERE clause.

    Greg

  4. #4
    Join Date
    Apr 2005
    Location
    Minnesota
    Posts
    42
    Thanks Greg...that worked perfectly.

    Your design in one query is way more efficient then the two queries I have. Is there a good tool to use to design this type a query or is it just experience? You mentioned real text editor.

    Thanks for your help. Next time might be better for me just to paste the sql in the forum instead of in excel...was thinking I could show my results and what I wanted easier in excel.

    CTE - Common Table Expression...that is helpful to know the name and I am googling looking at now. Is it better to use something like oracle sql developer when trying to design these queries? I have that tool.

  5. #5
    Join Date
    Aug 2012
    Posts
    45
    I used Notepad++ for that one, but it really depends on what you want to do. There are tons of free ones out there. Notepad++ will allow you to format SQL, VB, C, etc. If you just want to format, then you would be looking for an editor that knows various languages, in this case SQL, and can highlight key words, and most importantly(to me anyway), will show your tab lines. You might also look into getting SQL Server 2012 Express (it's free) if you are using Windows 7, or SQL Server 2008 R2 Express if you are running on XP. It will work nicely with Access if you want to link to your data. Sometimes Oracle and Microsoft products don't play well together.

    As for design of the query, yes, that will come with experience. In this case, if it were me, I would probably have the 'base' query stored, then use it in the summary query, since you might want to also use it in other queries. Remember, there is a difference between how efficiently you can write the query, and how efficiently the SQL compiler can interpret it and return results. The most efficient query from the perspective of the database is often not the best 'looking' in the editor. It really depends on the database you are using, indexes, etc. Anyhow, happy querying.

    Greg

  6. #6
    Join Date
    Aug 2012
    Posts
    45
    One more thing... You have wrapped your Fiscal_year and Year_month_rptg parameters in single quotes, meaning the database is interpreting them as strings. If they are really stored as string data (which they should not be), then you have to wrap them in quotes. However, if they are stored as numbers (which they should be), then by wrapping them in quotes, you are forcing the database to have to convert them to numbers while searching for your data. It probably won't make much of a difference if you only have a few rows, but it you have millions of rows, you would start noticing performance differences. If your two fields are stored as numbers, take the quotes off.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •