Combine MS queries (1 Viewer)

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";

Click to expand...

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";

Click to expand...