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

    Unhappy I thought this was perfect but..........

    Click image for larger version. 

Name:	database.jpg 
Views:	22 
Size:	216.1 KB 
ID:	32923

    The database collects and stores assessment info for students where I work.


    I was certain this was working ok

    However it seems that now that I cant get a list of the papers (and scores) which one student has sat.

    I get a list of all the papers he has sat with extra slot information which didn't refer to him

    eg Pupil below has only sat papers from Slots 17 and 23. However, since those exam papers were also used in exam slots 16 and 22, he's getting linked to their info as well and I don't want this.



    Click image for larger version. 

Name:	database1.png 
Views:	23 
Size:	24.3 KB 
ID:	32924

    Is the issue down to my database layout?




    Hope someone can help
    Last edited by andy49; 03-08-2018 at 06:31 AM.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Your images are not showing.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Click image for larger version. 

Name:	database.jpg 
Views:	18 
Size:	110.6 KB 
ID:	32925Click image for larger version. 

Name:	database1.png 
Views:	18 
Size:	24.3 KB 
ID:	32926

    Any luck showing now?

  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
    Showing in both posts. Suggest you post the query design view and SQL.
    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
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Happy to help

    Code:
    SELECT tblStudent.StudentID_PK, tblTestPaper.PaperCode, TblPaperStudent.TestScore, tblStudent.UPN, tblStudent.MathsClass, tblCalendar.Yeargp, tblCalendar.CalendarYear, tblCalendar.SlotID_PK
    FROM ((tblCalendar INNER JOIN tblslotpaper ON tblCalendar.SlotID_PK = tblslotpaper.slotID_FK) INNER JOIN tblTestPaper ON tblslotpaper.PaperID_FK = tblTestPaper.PaperID_PK) INNER JOIN (TblPaperStudent INNER JOIN tblStudent ON TblPaperStudent.studentID_FK = tblStudent.StudentID_PK) ON tblTestPaper.PaperID_PK = TblPaperStudent.PaperID_FK
    WHERE (((tblStudent.StudentID_PK)=5208));
    Click image for larger version. 

Name:	database3.png 
Views:	15 
Size:	140.3 KB 
ID:	32927

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Try changing to an inner join between each table as in the relationships window
    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

  7. #7
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    already tried inner and left and right I'm afraid. There is no change to the results.

    I think it's because students in a lower year group have been assigned the same paper as this student. The join then picks up this in the query.

    Hope this makes sense. I really appreciate the reply

  8. #8
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Then why not filter by year group as well?
    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

  9. #9
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    I suppose basically because when I'm after the slotIDs for all the exams that any student has done, I feel the database should allow that without filtering by year too. Also, if I filter by year I wont get a students results from last year.

  10. #10
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by andy49 View Post
    I suppose basically because when I'm after the slotIDs for all the exams that any student has done, I feel the database should allow that without filtering by year too. Also, if I filter by year I wont get a students results from last year.
    Filtering by year group will filter the students but, if your database is designed properly, it shouldn't prevent results from previous academic years being shown
    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

  11. #11
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I'm late to this thread but isn't it possible that you may need different queries to gather different data?

  12. #12
    keviny04 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Apr 2015
    Posts
    128
    Quote Originally Posted by andy49 View Post
    eg Pupil below has only sat papers from Slots 17 and 23. However, since those exam papers were also used in exam slots 16 and 22, he's getting linked to their info as well and I don't want this.



    Click image for larger version. 

Name:	database1.png 
Views:	23 
Size:	24.3 KB 
ID:	32924

    Is the issue down to my database layout?


    Hope someone can help
    If a paper can be slots 16, 17, 22, and 23, and a student took the paper for only slots 17 and 23, then your database is missing info about the paper a student takes. Your "tblPaperStudent" table probably needs to contain slot info, not just paper info.

  13. #13
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    thanks for all your replies

    Keviny04 you seem to be close to nailing the issue.

    the moment I add the tblpaperSlot table is when the problems start.


    Before I add the tblpaperSlot to the query I get just the tests that the student has taken. (slots 17 and 23)

    After, I get the extra rows.

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

Similar Threads

  1. Replies: 8
    Last Post: 12-23-2013, 06:53 PM
  2. Thought I could use this in 2010
    By Ruegen in forum Programming
    Replies: 3
    Last Post: 10-24-2013, 11:35 AM
  3. Is this thought even possible?
    By TOMMY.MYERS668 in forum Access
    Replies: 2
    Last Post: 07-29-2013, 09:42 AM
  4. Replies: 1
    Last Post: 01-06-2012, 03:55 PM
  5. Thought to be simple query
    By tmcrouse in forum Queries
    Replies: 9
    Last Post: 10-01-2010, 10:18 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