Results 1 to 5 of 5
  1. #1
    johnsocy is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    2

    Question Report showing employees who did not complete a specific assignment

    Hello! I am brand new to access, and have set up a database with three tables: Employees, Questions, and Answers.

    The Employee table contains employee data, including "Student Name" and "Area"

    The Questions table contains "Question Date" "Question 1" "Question 2" and "Question 3"

    The answers table contains "Student Name" "Answer 1" "Answer 2" and "Answer 3"

    I am looking to set up a report where I can enter the "Question Date" and see a listing of "Student Name" that do not have a record for that date.

    It would be nice to be able to select specific "Area"s of students, but I could make do without that.

    Any assistance would be greatly appreciated!
    Chris

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    How do you know which question record the answer record relates to? Is there a field in Answers for the QuestionID?
    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
    johnsocy is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    2
    I forgot to indicate; the Answers table has a field for "Question Date" which is linked to the Questions table "Question Date.


    Quote Originally Posted by June7 View Post
    How do you know which question record the answer record relates to? Is there a field in Answers for the QuestionID?

  4. #4
    Cran29 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Nov 2010
    Posts
    38

    Data base to record questions and answers etc

    I have a Data base already made which records the the date the question was asked records wrong answers and produces records for the individuals as to what they answered wrong. It also produces the information for the employees who got them all correct. It if you moved it up a gear could produce reports trend etc. I dont know if i can attach it on this form as it is large. I use it to record toolbox talks tasks etc. I have attached the relasionship tables and you should be able to construct it from using the same layout. You will need a calender table.
    Attached Files Attached Files

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Not sure date as pk/fk is good idea.

    The question on each date will apply to multiple students? (employees are the students?)

    Back to your original question.

    Build a query that includes the question and student tables but don't use a JOIN clause. This will cause a cartesian relation of each record in one table to each record in the other table. Every possible combination of student and question will result. Now build a query that joins the cartesian query to the answers, link on the pk/fk date fields, join type 'Include all records from [cartesian query name]...'. Set criteria under studentID from answers as Is Null. This should show all student/questions with no answer.

    BTW, should use ID instead of names saved in answers table, employee table needs a unique ID field as primary key. Name parts should be in separate fields.
    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.

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

Similar Threads

  1. Replies: 5
    Last Post: 03-01-2013, 04:20 PM
  2. Replies: 1
    Last Post: 09-24-2012, 07:09 PM
  3. Replies: 2
    Last Post: 06-15-2012, 07:32 AM
  4. Not lookup, but assignment
    By ArmyLT in forum Database Design
    Replies: 3
    Last Post: 11-17-2011, 02:35 PM
  5. Showing a specific forward date ?
    By Lojik in forum Queries
    Replies: 2
    Last Post: 03-24-2011, 07:38 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