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

    Angry Connecting a crosstab query to another table in one SQL

    I've seen several posts on this idea but none quite seems to have the answer for me.



    Every student at one exam slot sits three tests giving a total out of 240. The grades (from 1-,1,1+,2-,2,2+ up to 9) are at different scores for each assessment.

    So this crosstab produces the total score from the three papers in a calendar slot such as Christmas 2017.

    I have
    Code:
    SELECT qryEnterResults.slotID_FK,  qryEnterResults.MathsClass, qryEnterResults.StudentID_PK, qryEnterResults.Firstname, qryEnterResults.surname,  qryEnterResults.PaperNumber, Min(qryEnterResults.TestScore) AS MinOfTestScore, Sum(qryEnterResults.TestScore1) AS TotalScore 
    FROM qryEnterResults INNER JOIN tblCalendar ON qryEnterResults.SlotID_FK = tblCalendar.SlotID_PK
    WHERE (((qryEnterResults.slotID_FK)=17))
    GROUP BY qryEnterResults.slotID_FK,  qryEnterResults.MathsClass, qryEnterResults.StudentID_PK, qryEnterResults.Firstname, qryEnterResults.surname, qryEnterResults.PaperTier, qryEnterResults.UPN, qryEnterResults.PaperNumber;
    which works fine producing a list of names, other details and exam results out of 240 for the exam slot 17 (Christmas 2017).

    eg
    John 200
    Simon 198

    The score column needs to be converted to a grade and this is where the issue lies.

    I have (and will have more) 4 grade tables

    score_ID as autonumber
    score as short (from 1-240)
    grade as string (1-,1,1+ etc all the way up to 9+)

    Each assessment needs to be linked to one of the tables but not the same one. It will vary depending on the test they did.


    The following:

    Code:
    SELECT qryEnterResults_Crosstab.slotID_FK, qryEnterResults_Crosstab.StudentID_PK, qryEnterResults_Crosstab.mathsclass, qryEnterResults_Crosstab.Firstname, qryEnterResults_Crosstab.surname, IIf([papertier]='Higher',[gradeH],[gradef]) AS grade
    FROM qryEnterResults_Crosstab LEFT JOIN TblBoundaryMockSet3Autumn2017 ON qryEnterResults_Crosstab.TotalScore = TblBoundaryMockSet3Autumn2017.score;

    produces a list of names and their grade based on the grade table TblBoundaryMockSet3Autumn2017 (which will vary)







    but for this form I'll need ALL the results from every assessment connected to the correct grade table.

    Can I join the crosstab to the grade table using SQL for each slot, then union those results together?

    If not, can anybody see a work around?

    Happy to produce other info if you need it.

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    If I understand you correctly, I would create a new query joining the original data to the Grades table
    Then create a crosstab using the new query

    I do very similar but fairly complex queries to produce reports like the screenshots below
    Attached Thumbnails Attached Thumbnails ResidualsReport.PNG   GradesAnalysisOptions2a.gif  
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Thanks for prompt reply Ridders

    The "problem" is I have or may have up to ten Grades tables

    So getting 35/240 in one module/subject etc might result in a different grade than in another.

    I've inserted some images to show you what I have:

    crosstab query to collect results from the only three papers with the total

    Click image for larger version. 

Name:	p1.png 
Views:	24 
Size:	52.9 KB 
ID:	36893
    students total now converted to a grade using one of the grade tables
    Click image for larger version. 

Name:	p2.png 
Views:	24 
Size:	60.1 KB 
ID:	36894
    query to complete the second part.
    Click image for larger version. 

Name:	qr2.png 
Views:	24 
Size:	51.5 KB 
ID:	36895

    If I've misunderstood your reply then I apologise.

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    No - you should only have one Grades table from which you extract the matching records based on specified criteria.
    From example the attached is part of my Grades table used for reporting purposes. It has almost 500 records to handle:
    - effort / attainment / target / behaviour etc
    - KS2 / KS3 / KS4 / KS5
    - Foundation/Core/GCSE / NVQ / AS / A2 etc

    This is a small part of my main commercial app for UK schools - School Data Analyser
    If you are interested you can download a DEMO version from the same place with dummy data for a fictitious school. Its a BIG download though

    Click image for larger version. 

Name:	Grades.PNG 
Views:	25 
Size:	104.6 KB 
ID:	36900
    Last edited by isladogs; 01-11-2019 at 09:54 AM. Reason: Added missing screenshot
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Andy,

    Could you combine your multiple grade conversion tables into one adding the subject or test name to it then simply join to the crosstab using both the score and the test name to get the proper grade?

    Cheers,
    Vlad

  6. #6
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Im still not there I'm afraid.

    Gicu, I put all my Grade tables in one table tblAllBoundaries as shown below

    So 46 marks on the paper entitled MockSetautumn3 Higher paper (more advanced) will get you a 3+ but on another paper it will get you a 4.

    I'm not really sure how this helps though

    Click image for larger version. 

Name:	sniip1.PNG 
Views:	19 
Size:	23.1 KB 
ID:	36902

  7. #7
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Did you look at the Grades table in my screenshot or the DEMO app on my website?

    Having put all grade info in one table, you then link your table with the scores and filter by whichever fields apply for that specific example
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  8. #8
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    I downloaded the DEMO but needed username to use it?

    I think I've found a workaround though.

    results are no longer in a crosstab query (using Sum now)

    All grade boundaries are stored in this table (very short version)

    Click image for larger version. 

Name:	snip3.PNG 
Views:	19 
Size:	17.2 KB 
ID:	36903
    which connects to this qry

    Click image for larger version. 

Name:	snip4.PNG 
Views:	19 
Size:	25.4 KB 
ID:	36904

    to give me

    Click image for larger version. 

Name:	snip5.PNG 
Views:	21 
Size:	13.9 KB 
ID:	36905

    I suspect this is what at least one of you was suggesting....


    Two issues now. The table above has grades A - G. I need 1- to 9+ (27 sub grades)

    I also need to add the other 3 "groups" so that will be 27 x 2 x 4 = 216 rows!! Nothing!!

  9. #9
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    The user name / password info is supplied with the DEMO.
    Standard user is 999 for both; admin user is 111 for both.
    More details in the supplied documentation.
    For info, the demo data is for 2017-18 as I haven't got around to updating it

    The approach you've just described makes sense but for display purposes, you may still need a crosstab
    Last edited by isladogs; 01-12-2019 at 01:55 PM.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  10. #10
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    What I was suggesting was to have a grade table with the fields GradeID (PK, auto#), Score(0 to max), Grade (1- to 9+) and Tier (or subject or MocSet1). Then you use a double join on score and Tier to your crosstab or other query to get the grade corresponding to the particular score and tier.

    Cheers,
    Vlad

  11. #11
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Thanks both for your assistance. I went with one table for all my grade information then created a crosstab to get the data in the form I needed.

  12. #12
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You're welcome, glad to hear you've sorted it out.

    Cheers,
    Vlad

  13. #13
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Good decision. Onwards and upwards....
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

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

Similar Threads

  1. Crosstab to table query
    By waldi in forum Queries
    Replies: 2
    Last Post: 03-16-2018, 07:31 AM
  2. Crosstab table / query
    By WendellS in forum Queries
    Replies: 2
    Last Post: 08-27-2016, 07:22 PM
  3. Table Info Not Connecting to Query or Report
    By sarabeth in forum Access
    Replies: 4
    Last Post: 08-12-2015, 01:56 PM
  4. Table like a crosstab query but not
    By CodLiverOil in forum Reports
    Replies: 5
    Last Post: 05-18-2015, 12:08 PM
  5. Replies: 1
    Last Post: 07-30-2010, 10:28 AM

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