Update query using counts.

[Rob W]

Greetings,

Im used to using Informix/Ingres SQL so sometimes the syntax in access VBA
confuses me.

With the tables below the relationship is 1 row (tblStudent) to MANY
(tblStudentDeliveryResults)

The design query when I switch to update confuses me so not sure where to
add to some of the code i.e. Where do I put the Having/Group by? As im used
to using the total function which is available for SELECT queries.

Does the codes below look easily transferable to access SQL and also logic
and well structured?

UPDATE tblstudent
Set pgc_date = date()
Where studentid IN(
Select studentid
From tblStudentDeliveryResults
Where (actualresults>60 or resitresults >60)
Group by studentid
Having count(studentid) >3 and count(studentid)

Anything I can do to explain further, please let me know.

Kind Regards
Rob W

 

[Michael Gramelspacher]

Greetings,

Im used to using Informix/Ingres SQL so sometimes the syntax in access VBA
confuses me.

With the tables below the relationship is 1 row (tblStudent) to MANY
(tblStudentDeliveryResults)

The design query when I switch to update confuses me so not sure where to
add to some of the code i.e. Where do I put the Having/Group by? As im used
to using the total function which is available for SELECT queries.

Does the codes below look easily transferable to access SQL and also logic
and well structured?

UPDATE tblstudent
Set pgc_date = date()
Where studentid IN(
Select studentid
From tblStudentDeliveryResults
Where (actualresults>60 or resitresults >60)
Group by studentid
Having count(studentid) >3 and count(studentid)

Anything I can do to explain further, please let me know.

Kind Regards
Rob W

This works for me, but I left off the Having clause as I do not understand it.

UPDATE tblstudent
SET pgc_date = DATE()
WHERE studentid IN (SELECT studentid
FROM tblStudentDeliveryResults
WHERE (actualresults > 60
OR resitresults > 60)
GROUP BY studentid);

 

[Rob W]

The having clause is used as I want to count all the rows within
tblStudentDeliveryResults for a studentid where actualresults or
resitresults >60.

Depending on how many records where the results are over 60 I want a
different date to be set to todays date.
There is THREE different date stamps i.e pgc_date, therefore was going to
run THREE different SQL queries to avoid complications.

I hope this makes sense.

Regards
Rob