Combine MS queries

bennyboo

New member
Local time
Today, 19:46
Joined
Sep 5, 2016
Messages
2
Hi,

I'm a beginner MSAccess, SQL user. I'm currently working in MS ACCESS 2007 creating multiple update queries to update different fields. Can you please advise how can I combine these queries into 1? That is, i want to update all the fields at once rather than one query at the time. These are the queries I want to combine:

UPDATE [tbl_A] INNER JOIN tbl_B ON [tbl_A].Email = tbl_B.Email
SET tbl_B.[PBC Status] = [tbl_A]![Item Status]
WHERE ((([tbl_A].[Item Name])="Course 1"));

UPDATE [tbl_A] INNER JOIN tbl_B ON [tbl_A].Email = tbl_B.Email
SET tbl_B.[PACP Status] = [tbl_A]![Item Status]
WHERE ((([tbl_A].[Item Name])="Course 2"));

UPDATE [tbl_A] INNER JOIN tbl_B ON [tbl_A].Email = tbl_B.Email
SET tbl_B.[LSR Status] = [tbl_A]![Item Status]
WHERE ((([tbl_A].[Item Name])="Course 3"));

Please let me know if I can provide additional information.

Thank you.
 
You can set all three fields in the same query if they are in the same table.

When you are in the query editor, simply add the fields to your query and set their new value.
 
Change the Where part in the first query to the below, then only run the first query.
... WHERE [tbl_A].[Item Name]="Course 1"
Or [tbl_A].[Item Name])="Course 2"
Or [tbl_A].[Item Name])="Course 3";
 
Hi, thank you for your response, however it didn't quite work. I think it's because I'm trying to make the update to different fields:

UPDATE [tbl_A] INNER JOIN tbl_B ON [tbl_A].Email = tbl_B.Email
SET tbl_B.[PBC Status] = [tbl_A]![Item Status]
WHERE ((([tbl_A].[Item Name])="Course 1"));

UPDATE [tbl_A] INNER JOIN tbl_B ON [tbl_A].Email = tbl_B.Email
SET tbl_B.[PACP Status] = [tbl_A]![Item Status]
WHERE ((([tbl_A].[Item Name])="Course 2"));

UPDATE [tbl_A] INNER JOIN tbl_B ON [tbl_A].Email = tbl_B.Email
SET tbl_B.[LSR Status] = [tbl_A]![Item Status]
WHERE ((([tbl_A].[Item Name])="Course 3"));

Any other suggestions? Thank you.
 
Sorry I didn't notice that:
UPDATE [tbl_A] INNER JOIN tbl_B ON [tbl_A].Email = tbl_B.Email
SET tbl_B.[PBC Status] = [tbl_A]![Item Status], tbl_B.[PACP Status] = [tbl_A]![Item Status], tbl_B.[LSR Status] = [tbl_A]![Item Status]
WHERE [tbl_A].[Item Name]="Course 1"
Or [tbl_A].[Item Name])="Course 2"
Or [tbl_A].[Item Name])="Course 3";
 

Users who are viewing this thread

Back
Top Bottom