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

If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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?

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.

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.

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.

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!