Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Dec 2013
    Location
    Vestal, NY
    Posts
    11

    Unanswered: Access 2007 Subquery as a New Field

    Despite spending an entire day looking for a solution, I have not found an answer to this question.

    I have a query in my database that includes (basically) the following: Company, Fuel Type, Month, Year, Applications Received, Applications Approved. This query returns the number of applications by year and month. What I'd LIKE to do is add 2 additional columns/fields: one that equals the number of applications received in Septemer, and another that equals the number of applications received in December. I am not looking for a SUM. I just need the value for each individual company, even if it means that all of the other months' rows are blank.

    I need both of these fields / columns in one query, in separate fields, so I can then add another field that subtracts September from December.

    I have tried all day to add a subquery to a field but I keep getting errors. I either get prompted for a parameter (which I do not want). Or I get errors that say "No current record".

    I tried using the guidance in this link

    This is driving me crazy! ANY HELP IS APPRECIATED.

  2. #2
    Join Date
    Dec 2013
    Location
    Vestal, NY
    Posts
    11

    Access Subquery in New Field

    I have been trying for an entire day to find a solution to this problem with no success. ANY HELP IS APPRECIATED.

    I have a query in an Access 2007 database that (basically) has the following fields: Company, Fuel Type, Month, Year, Applications Received, Applications Approved. This query returns the applications for each month of the year. What I'd LIKE to do, is add 3 additional fields: one that equals the September applications received, another that equals the December applications received, and then a third calculation field that subtracts September from December.

    I am not looking for a SUM total for the applications, I just need the September field to equal the number of applications that each company received for that month. It's fine if all of the other months' rows are blank for this field. Again, I just need September and December in their own columns, so I can use those new fields in the calculation field.

    I have been trying to use a subquery to create the Sept and Dec fields to no avail. When I run the subquery I either get prompted for a parameter (which I don't want), or I get a "No Current Record" error. Maybe a subquery is not the right approach?

    I used this link as guidance for writing a subquery, but it doesn't have quite the same example of SQL for what I'm trying to do: Nest a query inside another query or in an expression by using a subquery - Access

    PLEASE HELP!! Thank you, thank you!!

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Please post the SQL expression of the query you're using, as well as the names and data types of the columns that are involved in the query.
    Have a nice day!

  4. #4
    Join Date
    Dec 2013
    Location
    Vestal, NY
    Posts
    11
    The following is the SQL code where I have included the subquery as its' own field:

    SELECT [EEPS II Verification Query].OpCo, [EEPS II Verification Query].Program, [EEPS II Verification Query].Fuel, [EEPS II Verification Query].Month, [EEPS II Verification Query].Year, [EEPS II Verification Query].[Applications Received], [EEPS II Verification Query].[Applications Approved], (SELECT [EEPS II Verification Query].[Applications Received] FROM [EEPS II Verification Query] WHERE ((([EEPS II Verification Query].Month) Like "Sep") AND (([EEPS II Verification Query].Year) Like "2013"))) AS [Sept Applications]
    FROM [EEPS II Verification Query]
    WHERE ((([EEPS II Verification Query].Year) Like "2013"));

    In the code above I am attempting to have the new field (Sept Applications) display the number of applications received in September in its' own column.

    Here are the column names and data types:
    Column 1
    Name: OpCo, Data type: Text
    Column 2
    Name: Program, Data type: Text
    Column 3
    Name: Fuel, Data type: Text
    Column 4
    Name: Month, Data type: Text
    Column 5
    Name: Year, Data type: Number
    Column 6
    Applications Received - Number
    Name: Applications Approved, Data type: Number
    Column 7 (Subquery)
    Name: Sept Applications

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. There are several syntax errors, or at least syntax incoherencies, in you query expression:
    If the column Year is numeric, you should not use:
    Code:
    WHERE [EEPS II Verification Query].Year Like "2013"
    but:
    Code:
    WHERE [EEPS II Verification Query].Year = 2013
    - You don't enclose litteral values in single or double quotes when the column is numeric.
    - Like should not be used with a numeric operator (although Access is very permissive about this).
    - Like should not be used instead of =.

    2. If what you want to be returned in the alias column [Sept Applications] is the number of applications that each company received for a given month and a given year, you should use COUNT:
    Code:
    SELECT COUNT([EEPS II Verification Query].[Applications Received])
    3. Keep in mind that every row corresponding to the criteria for the whole query (i.e. WHERE [EEPS II Verification Query].Year = 2013) will be returned. This means that for every row matching the criteria (i.e. Year = 2013), something will be returned in the alias column [Sept Applications].
    You could circumvent this by using an IIf() expression to garantee that the contents of the alias column [Sept Applications] will be null when the row does not match both conditions (Year = 2013 and Month = 'Sep'):
    Code:
    SELECT [EEPS II Verification Query].OpCo, 
           [EEPS II Verification Query].Program, 
           [EEPS II Verification Query].Fuel, 
           [EEPS II Verification Query].Month, 
           [EEPS II Verification Query].Year, 
           [EEPS II Verification Query].[Applications Received], 
           [EEPS II Verification Query].[Applications Approved], 
           IIf ( [EEPS II Verification Query].Month = 'Sep' AND [EEPS II Verification Query].Year = 2013,
                 (SELECT COUNT([EEPS II Verification Query].[Applications Received]) 
                    FROM [EEPS II Verification Query]
                   WHERE [EEPS II Verification Query].Month = 'Sep' AND [EEPS II Verification Query].Year = 2013),
                 Null
               ) AS [Sept Applications]
      FROM [EEPS II Verification Query]
     WHERE [EEPS II Verification Query].Year = 2013;
    A better solution (or should I say a more natural solution in the SQL way of doing things) would involve an identity column and the use of a LEFT JOIN.
    Have a nice day!

  6. #6
    Join Date
    Dec 2013
    Location
    Vestal, NY
    Posts
    11
    Thank you -- this is helping me to understand how this works! But, this SQL does not do quite what I'm hoping for. I'm attaching a screenshot that shows part of the results. You'll see that what is happening in the "Sept Applications" column is that it is counting how many times "Sep" in 2013 is appearing in the table. So, in that column you'll see the number 22 on every Sep 2013 row.

    What I actually want is for that column to simply equal the number of applications for Sep 2013. So, for example, in the first instance in the screenshot (9th row from the top), rather than that result to be 22, I want it to be 3952 which is the exact number of applications that were received for that program in that month and year.

    Please let me know if you want me to clarify.

    Again, thank you so much for helping!!
    Attached Thumbnails Attached Thumbnails subquery_results.jpg  

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    There's no screenshot attached to your last post!
    Have a nice day!

  8. #8
    Join Date
    Dec 2013
    Location
    Vestal, NY
    Posts
    11
    I'm sorry, I think I'm missing something. One more time...
    Attached Thumbnails Attached Thumbnails subquery_results.jpg  

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by MaryMgn View Post
    What I'd LIKE to do is add 2 additional columns/fields: one that equals the number of applications received in Septemer, and another that equals the number of applications received in December. I am not looking for a SUM. I just need the value for each individual company, even if it means that all of the other months' rows are blank.
    Quote Originally Posted by MaryMgn View Post
    What I actually want is for that column to simply equal the number of applications for Sep 2013. So, for example, in the first instance in the screenshot (9th row from the top), rather than that result to be 22, I want it to be 3952 which is the exact number of applications that were received for that program in that month and year.
    Both are contradictory: it's either a count or a sum.

    Replace COUNT by SUM in the query I posted and see if it yields what you're expecting.
    Have a nice day!

  10. #10
    Join Date
    Dec 2013
    Location
    Vestal, NY
    Posts
    11
    I thought of that too. But, then instead of the number 22 appearing in the Sept Applications column, it's replaced by the number 34792 (which is the sum of all the Sept applcations received by ALL programs). I don't really want a SUM or a COUNT, what I want is for the result to just be equal to the number of applications received by each individual program.

    As an example of the desired result, see the attached screenshot.
    Attached Thumbnails Attached Thumbnails subquery_results.jpg  

  11. #11
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    OK, it's clearer now. Replace the part in blue in the query I posted formerly with:
    Code:
           IIf ( [EEPS II Verification Query].Month = 'Sep' AND [EEPS II Verification Query].Year = 2013,
                 (SELECT [EEPS II Verification Query].[Applications Received] 
                    FROM [EEPS II Verification Query]
                   WHERE [EEPS II Verification Query].Month = 'Sep' AND [EEPS II Verification Query].Year = 2013),
                 Null
               ) AS [Sept Applications]
    In other words, just remove the COUNT.
    Have a nice day!

  12. #12
    Join Date
    Dec 2013
    Location
    Vestal, NY
    Posts
    11
    When I use that code for the subquery, then I get the "No Current Record" error.

  13. #13
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    This is strange: If you got something when using an aggregate function (COUNT ou SUM), there is no reason to get nothing when removing them. There is a simpler solution, though. From what I can see in the screenshot you posted, what you want is the value in [Applications Received] be repeated in [Sept Applications] when [Month] = 'Sep' and [Year] = 2013.

    You don't need a subquery for that:
    Code:
    IIf ( [EEPS II Verification Query].Month = 'Sep' AND [EEPS II Verification Query].Year = 2013, [EEPS II Verification Query].[Applications Received], Null) AS [Sept Applications]
    Have a nice day!

  14. #14
    Join Date
    Dec 2013
    Location
    Vestal, NY
    Posts
    11
    Thank you, that worked!! But, now I see that this unfortunately doesn't help me do what I really need which is to be able to subtract September from December.

    I'm attaching a screenshot -- I've added now 4 additional columns:
    Sep Apps Received
    Dec Apps Received
    Sep Apps Approved
    Dec Apps Approved

    This is the code that I used:
    SELECT [EEPS II Verification Query].OpCo,
    [EEPS II Verification Query].Program,
    [EEPS II Verification Query].Fuel,
    [EEPS II Verification Query].Month,
    [EEPS II Verification Query].Year,
    [EEPS II Verification Query].[Applications Received],
    [EEPS II Verification Query].[Applications Approved],
    IIf([EEPS II Verification Query].Month='Sep' And [EEPS II Verification Query].Year=2013,[EEPS II Verification Query].[Applications Received],Null) AS [Sep Apps Received],
    IIf([EEPS II Verification Query].Month='Dec' And [EEPS II Verification Query].Year=2013,[EEPS II Verification Query].[Applications Received],Null) AS [Dec Apps Received],
    IIf([EEPS II Verification Query].Month='Sep' And [EEPS II Verification Query].Year=2013,[EEPS II Verification Query].[Applications Approved],Null) AS [Sep Apps Approved],
    IIf([EEPS II Verification Query].Month='Dec' And [EEPS II Verification Query].Year=2013,[EEPS II Verification Query].[Applications Approved],Null) AS [Dec Apps Approved]
    FROM [EEPS II Verification Query]
    WHERE ((([EEPS II Verification Query].Month) Like "Sep" Or ([EEPS II Verification Query].Month) Like "Dec") AND (([EEPS II Verification Query].Year) Like "2013"));

    What I'd like to do is subtract "Sep Apps Received" from "Dec Apps Received". So for example, in the first 2 rows the Sep Apps = 3952, Dec Apps = 4669. I want to be able to subtract Sep from Dec (4669 - 3952), and then put the result in a different column (result would be 717). Can't figure out a way to do that.
    Attached Thumbnails Attached Thumbnails applications_pic.jpg  

  15. #15
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by MaryMgn View Post
    What I'd like to do is subtract "Sep Apps Received" from "Dec Apps Received". So for example, in the first 2 rows the Sep Apps = 3952, Dec Apps = 4669. I want to be able to subtract Sep from Dec (4669 - 3952), and then put the result in a different column (result would be 717). Can't figure out a way to do that.
    1. And the result of the subtraction would appear on which row, knowing that this operation involves 2 different rows?
    2. Is there any Primary key or Identity column in the data set?
    Have a nice day!

Tags for this Thread

Posting Permissions

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