CODEWITHSUNDEEP
sqlms-access
Michael Martin
Michael Martin

Reputation: 1

MS Access SELECT to UPDATE

I have looked through the site but I can't find what I am looking for.

I have a select query -

SELECT DISTINCT [StoreName] FROM [tbl_SB_Daily_Totals]
UNION ALL    
SELECT DISTINCT [StoreName] FROM [tbl_SBG_Daily_In\OutStats];

Which gives me exactly what I want BUT I need this to update into a table.

Ultimately what I need is a table that has one column which will have a unique list of stores from the two different tables above.

Upvotes: 0

Views: 102

Answers (2)

Nilesh Thakkar
Nilesh Thakkar

Reputation: 1459

You have two options to do this.

INSERT INTO  NewTable (StoreName) SELECT StoreName FROM (
SELECT DISTINCT StoreName FROM [tbl_SB_Daily_Totals]
UNION 
SELECT DISTINCT StoreName FROM [tbl_SBG_Daily_In\OutStats]) as x

Or 

SELECT x.* INTO [NewTable] FROM (SELECT DISTINCT [StoreName] FROM [tbl_SB_Daily_Totals]
UNION  SELECT DISTINCT [StoreName] FROM [tbl_SBG_Daily_In\OutStats]) x

Second option will create the table for you if not already exists, but keep in mind that it will delete NewTable if already exist and create fresh every time you run this query.

Also use Union and not Union All if you want to remove duplicates from your combined result set.

Hope this will help.

Upvotes: 1

Krish
Krish

Reputation: 5917

Just use this query as a result set. something like:

insert into tbl_newTable (store_name)
select * from (
SELECT DISTINCT [StoreName] FROM [tbl_SB_Daily_Totals]
UNION ALL
SELECT DISTINCT [StoreName] FROM [tbl_SBG_Daily_In\OutStats]) as tb1;

Upvotes: 0

Related Questions