Results 1 to 8 of 8
  1. #1
    MrMaple is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2016
    Posts
    4

    Query Expression

    Starting to self teach my self a bit of Access and I've decided to take on a large project from the start.



    I need help using an expression in a Query to pull the correct data into the query.

    Current expression:
    IIf([GradePrecentage]<=0.045,"A",IIf([GradePrecentage]<=0.075,"B",IIf([GradePrecentage]<=0.105,"C",IIf([GradePrecentage]<=0.135,"D","F"))))

    The values in BOLD are currently in a table. How do I get the expression to pull from the table. It will clearly be easier to manage the Data in the table than the expression in the query field.

    The table that has the info is name "GradeStructure"

    Formatted below.
    LetterGrade GradeMin GradeMax
    A 0.00% 4.50%
    B 4.60% 7.50%
    C 7.60% 10.50%
    D 10.60% 13.50%
    F 13.60%


    Thank you in advance for your help.

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    think I can see what you are trying to do, but cannot offer advice without knowing your table structure and some example data

  3. #3
    MrMaple is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2016
    Posts
    4
    Query Structure
    Grade % = Absolute/Meter

    Route SrvDate Sum Of MeteredSales Sum Of AbsoluteProduct GradePrecentage Expr1
    02 4/1/2015 2,802.75 53.25 1.90% A
    02 4/2/2015 2,995.30 47.45 1.58% A
    02 4/3/2015 2,383.60 32.35 1.36% A
    02 4/6/2015 2,748.15 29.1 1.06% A
    02 4/7/2015 3,781.50 181.75 4.81% B
    02 4/8/2015 2,895.10 18.6 0.64% A
    02 4/9/2015 3,906.65 44.9 1.15% A
    02 4/10/2015 3,156.20 30.35 0.96% A
    02 4/13/2015 3,370.35 25.8 0.77% A
    02 4/14/2015 2,717.30 43.7 1.61% A
    02 4/15/2015 2,782.10 25.55 0.92% A
    02 4/16/2015 3,245.65 64.8 2.00% A
    02 4/17/2015 2,891.65 25.6 0.89% A

    LetterGrade GradeMin GradeMax
    A 0.00% 4.50%
    B 4.60% 7.50%
    C 7.60% 10.50%
    D 10.60% 13.50%
    F 13.60%

    If you need more please let me know.

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    OK - so what you want to do is to lookup the lettergrade from the gradestructure based on the grade percentage?

    There are a few ways

    Rather than an iif statement, you can use a subquery
    Code:
    SELECT *, (SELECT top 1 lettergrade from gradestructure where grademax>maintable.gradepercentage order by gradepercentage desc) as lettergrade
    FROM maintable
    or you can use a Cartesian query (no join)
    Code:
    SELECT maintable.*,lettergrade
    FROM maintable, gradestructure
    WHERE maintable.gradepercentage between gradestructure.grademin and gradestructure.grademax
    or you can use a join - note the two above can viewed in the query gui, but with this join, you can only view in the sql window
    Code:
    SELECT maintable.*,lettergrade
    FROM maintable inner join gradestructure ON maintable.gradepercentage between gradestructure.grademin and gradestructure.grademax

  5. #5
    MrMaple is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2016
    Posts
    4
    Click image for larger version. 

Name:	Syntax Error Subquery 7-27-16.JPG 
Views:	11 
Size:	27.7 KB 
ID:	25290

    I got the following error. I put this into the Expression Builder.

    Expr2: (SELECT [RouteSummaryforGrades].*,[LetterGrade]
    FROM [RouteSummaryforGrades], [GradeStructure]
    WHERE [RouteSummaryforGrades].[GradePrecentage] between [GradeStructure].[GradeMin] and [GradeStructure].[GradeMax])

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    not much point in showing only part of the query, however it looks like you have blended the first option with one of the others. And none of them are intended to be used as a subquery in their own right

  7. #7
    MrMaple is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2016
    Posts
    4
    Current SQL View:

    SELECT DISTINCTROW MachFilterforGrades.Route, MachFilterforGrades.SrvDate, Sum(MachFilterforGrades.MeteredSales) AS [Sum Of MeteredSales], Sum(MachFilterforGrades.AbsoluteProduct) AS [Sum Of AbsoluteProduct], [Sum Of AbsoluteProduct]/[Sum Of MeteredSales] AS GradePrecentage, IIf([GradePrecentage]<=0.045,"A",IIf([GradePrecentage]<=0.075,"B",IIf([GradePrecentage]<=0.105,"C",IIf([GradePrecentage]<=0.135,"D","F")))) AS Expr1
    FROM MachFilterforGrades
    GROUP BY MachFilterforGrades.Route, MachFilterforGrades.SrvDate, [Sum Of AbsoluteProduct]/[Sum Of MeteredSales], IIf([GradePrecentage]<=0.045,"A",IIf([GradePrecentage]<=0.075,"B",IIf([GradePrecentage]<=0.105,"C",IIf([GradePrecentage]<=0.135,"D","F"))));

    Where do I inject the code?

    Thanks

  8. #8
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    Not sure if you can use subqueries in a group by query, but try it and see

    compare what I provided
    (SELECT top 1 lettergrade from gradestructure where grademax>maintable.gradepercentage order by gradepercentage desc)

    with what you are using - you will see it is completely different

    Expr2: (SELECT [RouteSummaryforGrades].*,[LetterGrade]
    FROM [RouteSummaryforGrades], [GradeStructure]
    WHERE [RouteSummaryforGrades].[GradePrecentage] between [GradeStructure].[GradeMin] and [GradeStructure].[GradeMax])

    1. take out the bits highlighted in red in your code
    2. Add back the bits in green in my example code
    3. replace the bit in orange with MachFilterforGrades

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Query expression asking for an ID
    By JimO in forum Queries
    Replies: 4
    Last Post: 06-27-2016, 01:13 PM
  2. Replies: 4
    Last Post: 05-03-2016, 01:51 PM
  3. Replies: 2
    Last Post: 11-20-2012, 03:21 AM
  4. Replies: 4
    Last Post: 10-26-2012, 12:49 AM
  5. Query Expression Help
    By hmartin in forum Queries
    Replies: 7
    Last Post: 01-21-2012, 11:37 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums