Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2008
    Posts
    59

    Unanswered: Need help using a query to show if students have completed monitoring forms

    If someone could help me with a solution to this problem I would be grateful. There's a couple of ways I'm thinking of doing this at the moment. Please let me know what is best or if there are other solutions.

    This is a quick brief of what I'm doing. I have a number of students who are registered to various different classes. I then have a number of monitoring forms which are also registered to a number of different classes, with the monitoring data being entered into the database.

    I have created a query which shows each student and what monitoring would need to be completed for that student. I.e. it will show student ID then monitoring ID for each monitoring form that relates to the course(s) they are on.

    Now, what I need is a system to show whether the monitoring forms have been completed for each student. At the moment when a monitoring form is completed the monitoring ID for that form and the student's ID are stored together into a table. Therefore, it is easy to add this to my previous query but then my query will only show which forms have been completed.

    What I want to know is how can I change my query so that it has a new column which shows whether the form has been completed from the information I have. Is there a formula I can write which would put a 'yes' if there is a matching monitoring ID to student ID in the table I just mentioned, and put a 'no' if there is nothing present. This would be the perfect way to do this but I don't know enough about formulas. Can anyone suggest how I could do this?

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Ok, very basically, what you could do it have one query show you which forms have been completed and another of the total list, completed or not. Then link the two queries together, giving all the records on the total list and only the matching ones from the completed list (you can do that by double clicking the line between the tables in the Design View of the query). The rest should become clear to you

    No doubt the SQL overlords will come along and paste some SQL for you that makes all that sound hard, but I use this method quite happily to do what you want.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Nov 2008
    Posts
    59
    Thanks for the suggestions. I am still struggling with this though.

    I have created one query which lists all surveys which need to be completed.

    I then have another query which lists those completed.

    I don't understand how I can link these. Do I need to start another query which would link them both?

    I think what would help me with this is. At the moment I have my query which lists all surveys that need to be completed. When I add the table which has all the completed surveys, the list is whittled down to include just the surveys which are completed. How can I get it to still display all surveys. But then also have an extra column which lists the survey ID if completed, but if it is not completed simply lists a blank box? I think this would help me as a starting point.

    Thanks
    Russ

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I don't understand how I can link these. Do I need to start another query which would link them both?
    Yes or no. Take your pick.

    In your query that shows all that need to be completed, add the one showing those completed to the query (QueryB). Link them via their PKF. Once linked you can double click the link line and then select the one that shows all from XYZ table and only those records from <QueryB> which match. Then, add the PKF from QueryB to the grid. When that field is NULL, it's one that still has to be done.

    Make sense?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Nov 2008
    Posts
    59
    Okay, well I did try that before but I presumed you were talking about something else, as when I tried to change the link relationship I always got an error message specifying that the data is ambiguous.

    However, I have spent sometime better defining relationships and realised there was a couple of strange links. I changed those round and hey presto, it now work's perfectly.

    And I've just mastered the IIF statement so I am a very happy man indeed!

    Thanks for your help Startrekker!

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You're most welcome

    Great to hear that you got there, and nicely done resolving the ambiguous joins thing!
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •