CODEWITHSUNDEEP
sqlms-access
Tal Taub
Tal Taub

Reputation: 23

Update query with select and where clause

I'm doing a little project for school is sql. I'm using MS Access as my database engine.

I'm trying to calculate and update Project Phases with the real end date of a phase by the last real end date of an assignment in that phase. This query return me a prompt that ask me to enter a value for "meet.PhaseId".

UPDATE ProjectPhases 
SET RealEndDate = (SELECT MAX(asgn.RealEndDate) AS RealEndDate 
                   FROM Assignments AS asgn 
                   INNER JOIN 
                   (
                     Meetings AS meet 
                     INNER JOIN ProjectPhases 
                     ON meet.PhaseId = ProjectPhases.PhaseId
                   ) 
                     ON asgn.MeetingId = meet.MeetingId 
                   WHERE meet.PhaseId = ProjectPhases.PhaseId 
                    vAND asgn.RealEndDate IS NOT NULL) 
WHERE meet.PhaseId = ProjectPhases.PhaseId;

Database structure (image)

Upvotes: 2

Views: 421

Answers (2)

Parfait
Parfait

Reputation: 107652

Consider saving your aggregate query in a separate query and then use the domain aggregate, DLookUp() in the update query. In MS Access SQL (unlike other dialects), update queries must maintain the updateable condition (or non read-only) which rules out use of aggregate queries.

SELECT query (no ProjectPhases join)

SELECT meet.PhaseId, MAX(asgn.RealEndDate) AS MaxRealEndDate 
FROM Assignments AS asgn 
INNER JOIN Meetings AS meet 
ON asgn.MeetingId = meet.MeetingId 
WHERE asgn.RealEndDate IS NOT NULL
GROUP BY meet.PhaseId

UPDATE query

UPDATE ProjectPhases p
SET p.RealEndDate = DLookUp("MaxRealEndDate", "myaggQuery", "PhaseId=" & p.PhaseId)

Alternatively, you might consider a nested domain aggregate to avoid the saved aggregate query:

UPDATE ProjectPhases p
SET p.RealEndDate = DMax("RealEndDate", "Assignments", "MeetingId=" & 
                         DLookUp("MeetingId", "Meetings", "PhaseId=" & p.PhaseId) &
                         " AND RealEndDate IS NOT NULL")

Upvotes: 2

Gustav
Gustav

Reputation: 55841

You don't select meet.PhaseId from your subquery, thus no PhaseId exists to filter on.

It is often easier to build your query step by step using the GUI designer.

Upvotes: 0

Related Questions