Interesting Query, Need help!!

[accessdba]

If you can't see table clearly see the attachment:
I am facing interesting challenge and wondering if someone can help me with this:

This is the table I have:
Student_ID
Student Name
Type
Product
10001


David
Virtual
Infra
10002
Troy
Hardware
Solution
10002
Elizabeth
Core
Server
10003
Karen
Windows
Solution
10002
Tony
Hardware
Software
10004
Loren
Hardware
Server
10004
Katty
messaging
Solution

so what I want to do is:
I want to look for records which has (Product='Solution' and Type='Hardware') and I want to change all the Type of those records to 'Virtual'. But not only that, the challenging part is, I want to change Type of all the records which has (Product='Solution' and Type='Hardware'), and Student_ID is same.

So for example: In the above table data I want to write a query which will look for 'Solution' in the column Product, if it finds it, it will look for 'Hardware' in the column Type, if it finds it, it will look for similar Student_ID as whatever it was for that particular records in the rest of the table and for all those records it will change Type='Virtual'. (remember if it finds 'Hardware' in the type, and after that when it will be looking for same Student_id in rest of the table, there might be several records where Student_ID will be matching to the same Student_ID of the same records but they might have many different Type - in above example Student_ID 10002 has 3 different Type)

so if i run that query in the above table it should give me this results:
Student_ID
Student Name
Type
Product
10001
David
Virtual
Infra
10002
Troy
Virtual
Solution
10002
Elizabeth
Virtual
Server
10003
Karen
Windows
Solution
10002
Tony
Virtual
Software
10004
Loren
Hardware
Server
10004
Katty
messaging
Solution


notice that in result Type didn't change for Student_ID=10004 because even though we did find Product='Solution' we didn't find where it has 'Hardware' for that product.

thanks
If you can't see table clearly see the attachment:
someone gave me sql server version of the query but but it is giving me syntax error. Also I am planning to run this in MS ACCESS 2010 db
update y set
type = 'Virtual'
from (
select student_id
from student
where [type] = 'Hardware'
and product = 'Solution'
group by student_id
) d
join student as y on y.student_id = d.student_id

[orange]

Have you considered normalizing your data?
3 tables.
Student
Product
StudentHasProduct