Results 1 to 6 of 6
  1. #1
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051

    Need to create an query in SQL joining a table on a calculated value

    I have a query who's SQL is working perfectly



    It produces FIRSTNAME,LASTNAME, FINAL_LEVEL (a calculated value based on Table1, Table2 and Table3)

    I need to create a join onto Table4 with two columns LVL and grade with a join between Query [final_level] and table 4[grade] so that a grade can be added to the original fields.

    It has to be done at SQL level I think as I cant access the final_level column in the query builder

    Can anyone advise. Tired of trying!

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    I think it may make more sense if you post the layout of the tables, queries, and important fields along with some sample data.

    By the way, do you have any control over the structure of your data tables?
    When I read this:
    It produces FIRSTNAME,LASTNAME, FINAL_LEVEL (a calculated value based on Table1, Table2 and Table3)
    it leads me to believe that you may have a design issue and a working with a database that is not normalized. You typically should not have like fields in multiple tables.
    Last edited by JoeM; 11-26-2016 at 08:15 PM.

  3. #3
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    OK hope this is easier. Relationships window is shown too. Never tried a project this big and I'm certain it will show!!
    Click image for larger version. 

Name:	relationships.png 
Views:	17 
Size:	28.6 KB 
ID:	26554
    I work in a school and am designing a database to store results from assessments

    staff use a form to select their class which opens a query with their group

    Click image for larger version. 

Name:	results.png 
Views:	17 
Size:	15.6 KB 
ID:	26552

    Staff enter results in P1,P2 and MA and the RAW score is calculated.


    The next query calculates which level the students get based on RAW score and the tier of paper they sat

    The results currently are
    Click image for larger version. 

Name:	query results.png 
Views:	17 
Size:	20.4 KB 
ID:	26555

    I'd like to then use this the results of this query along with the table below to convert final_level to a grade

    Output should be First name, last name, final level, grade


    Click image for larger version. 

Name:	convertgrade.png 
Views:	17 
Size:	12.5 KB 
ID:	26553
    If you need anything else please ask

  4. #4
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Sorry, was pretty busy over the holiday weekend and wasn't around a computer much.

    So, is it just a matter of also joining convtlvl2grade to your query, doing a left join from the final_level field to the EOY_Target field?

  5. #5
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Yes. But I can't do it in design mode as the final_level field is calculated.

  6. #6
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Leave your query "as-is" then, and use it in another query in which you link that query to your convtlvl2grade table.

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

Similar Threads

  1. Replies: 10
    Last Post: 06-10-2015, 08:16 AM
  2. Replies: 1
    Last Post: 11-30-2014, 12:31 PM
  3. Replies: 5
    Last Post: 04-04-2014, 03:33 PM
  4. Replies: 1
    Last Post: 05-09-2013, 07:54 PM
  5. Create a query With a Calculated Field
    By Content1 in forum Queries
    Replies: 2
    Last Post: 01-05-2013, 12:46 PM

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
  •  
Other Forums: Microsoft Office Forums