update a column within a join statement and group by clause

I have pulled up the following recordset.

SELECT Max(Text4)Text4,Max(Text5)Text5, Sum(Total)TotalsperStudent FROM AttendanceGrid INNER JOIN tblRegistration ON AttendanceGrid.[ID]=tblRegistration.[ID] Where  AttendanceGrid.Fiscal = '" & Fiscal & "' And AttendanceGrid.[CSW/TitleXX] = 'C' And AttendanceGrid.Month = '" & Com(z) & "' And NewPart = 0 And Combo11 like '" & rec1.Fields(0) & "' Group by tblRegistration.ID HAVING Sum(Total)>0

Open in new window


Within this recordset, I would like to update the column called NewPart = 1 in the table called AttendanceGrid.  

How is this possible?
al4629740Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Paul_Harris_FusionConnect With a Mentor Commented:
Or specifically with your runtime values:

update tblRegistration set NewPart = 1
Where ID in
(
SELECT tblRegistration.ID
FROM AttendanceGrid INNER JOIN tblRegistration
ON AttendanceGrid.[ID]=tblRegistration.[ID]
Where  AttendanceGrid.Fiscal = '2013'
And AttendanceGrid.[CSW/TitleXX] = 'C'
And AttendanceGrid.Month = 'September'
And NewPart = 0
And  Total > 0
And Combo11 like 'Alternatives'
)
0
 
QuinnDexCommented:
update AttendanceGrid set NewPart = 1 Where AttendanceGrid.[ID] in (SELECT Max(Text4)Text4,Max(Text5)Text5, Sum(Total)TotalsperStudent FROM AttendanceGrid INNER JOIN tblRegistration ON AttendanceGrid.[ID]=tblRegistration.[ID] Where  AttendanceGrid.Fiscal = '" & Fiscal & "' And AttendanceGrid.[CSW/TitleXX] = 'C' And AttendanceGrid.Month = '" & Com(z) & "' And NewPart = 0 And Combo11 like '" & rec1.Fields(0) & "')
0
 
al4629740Author Commented:
Sorry, I meant to say "NewPart = 1 in the table called tblRegistration.

Would it be:

update AttendanceGrid set NewPart = 1 Where tblRegistration.[ID] in (SELECT Max(Text4)Text4,Max(Text5)Text5, Sum(Total)TotalsperStudent FROM AttendanceGrid INNER JOIN tblRegistration ON AttendanceGrid.[ID]=tblRegistration.[ID] Where  AttendanceGrid.Fiscal = '" & Fiscal & "' And AttendanceGrid.[CSW/TitleXX] = 'C' And AttendanceGrid.Month = '" & Com(z) & "' And NewPart = 0 And Combo11 like '" & rec1.Fields(0) & "')
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
al4629740Author Commented:
I tried the following with no success:

update tblRegistration set NewPart = 1 Where tblRegistration.[ID] in (SELECT * FROM AttendanceGrid INNER JOIN tblRegistration ON AttendanceGrid.[ID]=tblRegistration.[ID] Where  AttendanceGrid.Fiscal = '2013' And AttendanceGrid.[CSW/TitleXX] = 'C' And AttendanceGrid.Month = 'September' And NewPart = 0 And  Total > 0 And Combo11 like 'Alternatives')

Error:
Msg 116, Level 16, State 1, Line 1
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
0
 
QuinnDexConnect With a Mentor Commented:
try this

update tblRegistration set NewPart = 1 Where exists tblRegistration.[ID] in (SELECT * FROM AttendanceGrid INNER JOIN tblRegistration ON AttendanceGrid.[ID]=tblRegistration.[ID] Where  AttendanceGrid.Fiscal = '2013' And AttendanceGrid.[CSW/TitleXX] = 'C' And AttendanceGrid.Month = 'September' And NewPart = 0 And  Total > 0 And Combo11 like 'Alternatives')
0
 
Paul_Harris_FusionCommented:
update tblRegistration set NewPart = 1
where ID in
(
SELECT tblRegistration.ID
FROM AttendanceGrid INNER JOIN tblRegistration
ON AttendanceGrid.[ID]=tblRegistration.[ID]
Where  
AttendanceGrid.Fiscal = '" & Fiscal & "'
And AttendanceGrid.[CSW/TitleXX] = 'C'
And AttendanceGrid.Month = '" & Com(z) & "'
And NewPart = 0
And Combo11 like '" & rec1.Fields(0) & "'
Group by tblRegistration.ID
HAVING Sum(Total)>0  
)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.