CODEWITHSUNDEEP
sqlms-accessms-access-2010ms-access-2007ms-access-2013
snowshow
snowshow

Reputation: 1

How to Add columns to a multi-table Query with SQL in MSAccess

So I have merged two tables together in MS Access to a query. However, 1 of the tables was a customer table and the other was a supplier table. When I merged them the column with the customer number and column with supplier number was appended to each other an into the same table. Which is fine as all information from all columns are providing same information except the column that provides supplier or customer number.

See below for example:

How It looks

I want it to look like this: You can identify if its a customer based on the 2 first integers. How I want it to look

The query I'm taking information from is named "New1 All Items" and want to call the new column for BP_Type

Create Table [Updated Items]
AS
Select *,
BP_Type = Case [New1_All_Items]
when Supplier between 0000100000 and 0001344030 then 'Supplier'
when Supplier between 1000000307 and 1006860889 then 'Customer'
else 'No Link'
End BP_Type
from New1 All Items

I tried creating a table called Updated Items while selecting all from the query and created a new column based on Case functionality. However, I get "syntax error". Someone please help me.

Upvotes: 0

Views: 53

Answers (1)

Mika Oukka
Mika Oukka

Reputation: 216

You can use make table query for this. This would be using the 2 first integers:

SELECT 
    [New1 All Items].BP, 
    [New1 All Items].[Supplier/Customer #], 
    IIf(
        Left([Supplier/Customer #],2)="11",
        "Supplier",
        IIf(
            Left([Supplier/Customer #],2)="22",
            "Customer","No Link"
            )
        ) AS Type 
INTO [Updated Items]
FROM [New1 All Items];

Upvotes: 0

Related Questions