Search code examples
sqlms-access

SQL create table based on other column values

I'm wondering if its possible to create a new table from another table that includes rows based on one column value and only some rows from the same column based on a condition in another column?

I have a table and I only want to select rows where the 'Tier' column = 1,2 or 3 and has been created this year. I can achieve this with:

SELECT * INTO Table1
FROM Table_v2
WHERE Table_v2.Tier in ('1', '2', '3')
AND CreatedDate > Date()-365;

My issue is that I want to select all rows for 'Tier' 1 & 2 but for 'Tier' 3 I only want rows where the following column 'Type Of Check' = Quality so the desired output would look something like this:

Desired output example:

enter image description here

Solution

  • You can do that with an OR for your Type Of Check value. If there are only 3 possible values for Tier then you can also omit Table_v2.Tier = '3'.

    SELECT * INTO Table1
FROM Table_v2
WHERE CreatedDate > Date()-365
AND (Table_v2.Tier IN ('1', '2') OR (Table_v2.Tier = '3' AND [Type Of Check] = 'Quality'));