I had another go at this today and managed to get a query to get the info from
your data where there was:
A) a single Max(Due) value for a Project 's and submissions (428 records), and
B) multiple/duplicate Max(Due), so got info from the Max(Sid) of the Group (4 records)
The approach was to get all the records where there was a single Max(Due) for the submissions for a Project.
Then, for those records where there were duplicate Max(Due) values, get the Max(Sid) and related info for that Project.
Then UNION the 2 sets of info to get the final values.
Here is the final query:
Code:
SELECT Project.pID
,Project.Tracking_num
,QueryJED1.sTracking_num
,QueryJED1.MaxOfDue
,Submission.sID
,Submission.ReviewNum
,Submission.Reviewer
,Submission.Completed
,Submission.Due
FROM (
QueryJED1 INNER JOIN Project ON (Project.pID = QueryJED1.pID)
AND (Project.pID = QueryJED1.pID)
AND (QueryJED1.pID = Project.pID)
)
INNER JOIN Submission ON (QueryJED1.MaxOfDue = Submission.Due)
AND (QueryJED1.sTracking_num = Submission.sTracking_num)
WHERE Project.PID IN (
SELECT MyQ.pid AS MQPID
FROM (
SELECT Project.pID
,Project.Tracking_num
,QueryJED1.sTracking_num
,QueryJED1.MaxOfDue
,Submission.sID
,Submission.ReviewNum
,Submission.Reviewer
,Submission.Completed
,Submission.Due
FROM (
QueryJED1 INNER JOIN Project ON (QueryJED1.pID = Project.pID)
AND (QueryJED1.pID = Project.pID)
AND (QueryJED1.pID = Project.pID)
)
INNER JOIN Submission ON (QueryJED1.MaxOfDue = Submission.Due)
AND (QueryJED1.sTracking_num = Submission.sTracking_num)
ORDER BY Project.pID
) AS myQ
GROUP BY myQ.pid
HAVING count(reviewnum) = 1
)
UNION
SELECT Project.pID
,Project.Tracking_num
,QueryJED1.sTracking_num
,QueryJED1.MaxOfDue
,Submission.sID
,Submission.ReviewNum
,Submission.Reviewer
,Submission.Completed
,Submission.Due
FROM (
QueryJED1 INNER JOIN Project ON (Project.pID = QueryJED1.pID)
AND (Project.pID = QueryJED1.pID)
AND (QueryJED1.pID = Project.pID)
)
INNER JOIN Submission ON (QueryJED1.MaxOfDue = Submission.Due)
AND (QueryJED1.sTracking_num = Submission.sTracking_num)
WHERE submission.sid IN (
SELECT max(sid)
FROM (
SELECT Project.pID
,Project.Tracking_num
,QueryJED1.sTracking_num
,QueryJED1.MaxOfDue
,Submission.sID
,Submission.ReviewNum
,Submission.Reviewer
,Submission.Completed
,Submission.Due
FROM (
QueryJED1 INNER JOIN Project ON (QueryJED1.pID = Project.pID)
AND (QueryJED1.pID = Project.pID)
AND (QueryJED1.pID = Project.pID)
)
INNER JOIN Submission ON (QueryJED1.MaxOfDue = Submission.Due)
AND (QueryJED1.sTracking_num = Submission.sTracking_num)
ORDER BY Project.pID
) AS Num2
GROUP BY num2.pid
HAVING count(reviewnum) > 1
)
An interesting "battle" with the SQL as it has been a while. I hope the query is responsive enough for your needs.
Good luck.