Help creating challenging query in access

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Help creating challenging query in access

Author

Topic

cqldba303
Starting Member

16 Posts

Posted - 2011-10-06 : 09:41:31

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, messaging, 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 (cqldba@gmail.com)

cqldba303
Starting Member

16 Posts

Posted - 2011-10-06 : 10:42:14

somebody gave me sql server query but it doesn't work with MS ACCESS and gives me syntax error: can anyone modify this to work in access ?
update y set
type = 'Virtual'
from (
select student_id
from @yourTable
where [type] = 'Hardware'
and product = 'Solution'
group by student_id
) d
join @yourTable as y on y.student_id = d.student_id

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-10-06 : 13:57:42

I'm not an Access guy but one way that should work is to create a query based on that derived table, then create another query (update query) using the first query JOINed to yourTable.

EDIT:
One thing I saw from googling:
http://stackoverflow.com/questions/4559316/update-query-from-outer-joined-tables-or-derived-tables

EDIT2:
As I mentioned in your other thread, according to several posts I found the JET engine has a problem using derived tables with aggregations as part of an UPDATE query.

This is the access version without the GROUP BY which seems to work:


UPDATE yourTable 
INNER JOIN (
       SELECT student_id 
       FROM yourTable 
       WHERE type='Hardware' 
       And product='Solution'
       )  AS d ON yourTable.student_id=d.student_id 
SET yourTable.type = 'Virtual';

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources