Update Query Criteria

[padlocked17]

All -

I have an update query built that checks to see if a date is entered for when a document is mailed as well as a date for when that document was received.

I'm trying to setup this query to check and ensure that if multiple documents (Form8) were mailed that each one was marked as having a received date before the Update query add's a "Graduated" mark to the student. Screenshots are attached.

Anyone have any idea on how I can set this up?

Basically the tblFormEight can have multiple entries assocaited with each EnrollmentID (aka Student enrolled in a class) which represents multiple Form's set out in the mail. I don't want this Update query to run unless all entries for each EnrollmentID have Received Dates.

Thanks!

 

[padlocked17]

Any ideas?

 

[CraigDolphin]

First create a query that shows all records from tblFormEight
<air code>

Select [EnrollmentID] 
From [tblFormEight]
WHERE (([EnrollmentID] =' & [COLOR="Red"]Forms!SomeForm!txtEnrollmentID [/COLOR]& ') AND (Not IsNull([MailedDate])) AND (IsNull([ReceivedDate]));

Obviously you'll need to provide the query with the enrollmentid to check somehow...this example uses a text control on a form to provide that information to the query.

Then you're going to have to trigger this 'check' somewhere which should run some code

in that code, use DCount to count records in your new query.
<air code>

if DCount("[EnrollmentID]","YourQueryName") > 0 then 
'looks like you've got a record with a mailed date and no received date for this student
Else
'all clear...proceed with your update query here
End if

 

[padlocked17]

The problem is that I can't specify an EnrollmentID on a form control as I want this query to be run everytime the database is opened and check ALL EnrollmentID's to see if ALL Form 8's were marked as received for EACH EnrollmentID.

Does that make sense?

 

[CraigDolphin]

Yikes...dredging my memory on this...

You said:

"I don't want this Update query to run unless all entries for each EnrollmentID have Received Dates"

A. Do you really mean you don't want the query to run for any enrollmentid if ANY enrollmentID has a null receiveddate?

B. Or do you mean that you want the update to run for the enrollmentid's that have received dates present, but not for the enrollmentid's where the received date is null?

You can trigger code in the on_open event of whatever form is opened at startup.

If it's the former (A), use a Dcount to count records in a query that finds any records in tblFormEight with a null in the recieved date field and is not null in the mailed date field.
If the dcount returns > 0 then don't execute your update, otherwise run it.

If it's the latter (B), create a totals query that lists Enrollmentid for any records in tblFormEights with a null in the recieved date field and is not null in the mailed date field. Let's call this qryEnrollmentIDsWeDontWantToUpdate.

Create another Totals query that lists all EnrollmentID's in tblFormEight and uses a left outer join to link to qryEnrollmentIDsWeDontWantToUpdate. Add the EnrollmentID from qryEnrollmentIDsWeDontWantToUpdate to the grid as well, and use a criteria of Is Null for this field.

Run the query and you should see all the enrollmentid's for people who you want to run the update for, but not the people who you don;t want to run the update for.

Then, add the new query to the table grid of your update query and use an inner join to link it to tblFormEight in your query. This should limit your update query to working only for records with enrollmentid's that show up in your new query.

 

[padlocked17]

B. Or do you mean that you want the update to run for the enrollmentid's that have received dates present, but not for the enrollmentid's where the received date is null?

You can trigger code in the on_open event of whatever form is opened at startup.

If it's the latter (B), create a totals query that lists Enrollmentid for any records in tblFormEights with a null in the recieved date field and is not null in the mailed date field. Let's call this qryEnrollmentIDsWeDontWantToUpdate.

Create another Totals query that lists all EnrollmentID's in tblFormEight and uses a left outer join to link to qryEnrollmentIDsWeDontWantToUpdate. Add the EnrollmentID from qryEnrollmentIDsWeDontWantToUpdate to the grid as well, and use a criteria of Is Null for this field.

Run the query and you should see all the enrollmentid's for people who you want to run the update for, but not the people who you don;t want to run the update for.

Then, add the new query to the table grid of your update query and use an inner join to link it to tblFormEight in your query. This should limit your update query to working only for records with enrollmentid's that show up in your new query.

I am going to give this a shot. I can see the logic in my head now, just wasn't able to come up with it on my own.

I'll give it a shot and let you know how it turned out.

Thanks for taking the time to type that all up for me!

 

[padlocked17]

Alrighty. Here is the SQL for my queries:

qryDoNotGraduate

SELECT tblFormEight.EnrollmentID, tblFormEight.MailedDate, tblFormEight.ReceivedDate
FROM tblFormEight
GROUP BY tblFormEight.EnrollmentID, tblFormEight.MailedDate, tblFormEight.ReceivedDate
HAVING (((tblFormEight.MailedDate) Is Not Null) AND ((tblFormEight.ReceivedDate) Is Null));

qryDoGraduate

SELECT tblFormEight.EnrollmentID
FROM qryDoNotGraduate RIGHT JOIN tblFormEight ON qryDoNotGraduate.EnrollmentID = tblFormEight.EnrollmentID
WHERE (((qryDoNotGraduate.EnrollmentID) Is Null));

qryGraduateStudent

UPDATE (tblClasses INNER JOIN (tblEnrollment INNER JOIN tblFormEight ON tblEnrollment.EnrollmentID = tblFormEight.EnrollmentID) ON tblClasses.ClassID = tblEnrollment.ClassID) INNER JOIN qryDoGraduate ON tblEnrollment.EnrollmentID = qryDoGraduate.EnrollmentID SET tblEnrollment.Graduated = Yes, tblEnrollment.GraduatedDate = Date()
WHERE (((tblEnrollment.Graduated)=No) AND ((tblEnrollment.GraduatedDate) Is Null) AND ((tblClasses.StartDate) Is Not Null) AND ((tblClasses.ClosedDate) Is Null) AND ((tblFormEight.MailedDate) Is Not Null) AND ((tblFormEight.ReceivedDate) Is Not Null) AND ((tblFormEight.NotReceived)=No));

When I try to run the update query I get "Operation must be an updateable query."

Any ideas. The queries all show what info should be updated correctly, it's just getting hung up.

Thanks!

 

[CraigDolphin]

I work better hands-on. Can you post a zipped, stripped copy of your db for me to look at?

 

[padlocked17]

You bet. Let me see about zipping up all necessary files (using vPPC and it requires an Access Control DB, Back-End Security DB) in addition to the Front-End/Back-End of my Database.

Give me a few hours to strip it down.

Thanks a ton!

 

[padlocked17]

Actually I think I figured it out.

Change the Do and DoNot Graduate queries to non-totals queries or just select queries seemed to fix the problem.

Was there a reason behind using the Totals queries?

Thanks.

 

[CraigDolphin]

Well, I suggested totals queries in order to produce a list of enrollmentIds so that each id only appears once. Not knowing the table structure of your db, I'm not sure what effect having the id's repeated would have for your update query.

But, if it works as you want it to without them being total queries then run with it. Just check to be certain it is performing only as expected before putting it into your production db.

 

[padlocked17]

Got it.

I have AutoNumber Id's for all tables, so having multiple entries with the joins should still show correctly. I'll do some testing however to ensure.

Thanks!