I actually think I have a working solution but by different means. I changed the query from a count query to an if you find the wording attendance after you find attendance MV in the first table to give me that associates ID. So that I can append new language to the bh warnings. I believe this works out for me. I tried to manipulate the table and was able to successfully do so. Does this make sense to you?
Code:
SELECT w1.[Associate ID Number], w2.Type AS NewViolationCnt, w2.Level, w2.Status
FROM [B-H Warnings] AS w1 LEFT JOIN [B-H Warnings] AS w2 ON w1.[Associate ID Number] = w2.[Associate ID Number]
WHERE (((w1.Type) In ("Attendance - M.V.")) AND ((w1.Status) In ("Active")) AND ((w2.DateOfWarning)>[w1].[DateOfWarning] And (w2.DateOfWarning)<=Date()) AND ((w1.DateOfWarning)>=DateSerial(Year(Date()),1,1) And (w1.DateOfWarning)<Date()))
GROUP BY w1.[Associate ID Number], w2.Type, w2.Level, w2.Status
HAVING (((w2.Type)="Attendance") AND ((w2.Status) In ("Active")));