CODEWITHSUNDEEP
sqlms-accessms-access-2007
Gutanoth
Gutanoth

Reputation: 842

adding an existing column to an existing table MS_Access

A little Background:

In this project, I am trying to combine 2 tables which I acquire from different applications.

One of the applications gives me several tables which I have successfully combined into 1 table:

DB_Total

The other application gives me a single Table with nothing but the Tagnames. This table is called:

TagNames_EA

Now, I would like to add the "Name" column from the "TagNames_EA" table into the "DB_Total" table so I can compare the values in the "Name" fields.

So far, I have this code to compare and filter out the names that don't match:

SELECT
    d.Address,
    d.Type,
    d.Name,
    IIf(t.Name Is Null, False, True) AS match_found
FROM
    DB_Total AS d
    LEFT JOIN TagNames_EA AS t
    ON d.Name = t.Name;

This works great, but I would also like to see the "Name" values from the TagNames_EA table in the DB_Total table.

I know It's possible to add a column to an existing table with the

ALTER TABLE DB_Total
ADD Names_EA

But, when I try this:

ALTER TABLE DB_Total
ADD Names FROM TagNames_EA AS Names_EA

It does not work

Is there an easy way to do this?

Edit: I tried to simplify the way I explained my situation.

Edit2: Example

In the "DB_Total" table, I have a list of names, which should be exactly the same as the names in the "EA_Names" table.

DB_Total:
     Name
S1\SVS_AK\ENA[3]
S1\SVS_AK\ENA[4]
S1\SVS_AK\ENA[5]
S1\SVS_AK\ENA[6]
S1\SVS_AK\ENA[7]
S1\SVS_AK\ENA[8]

However, sometimes mistakes are made and I would like to check if there are names that don't have matches. Like this:

TagNames_EA
    Name
S1\SVS_AK\ENA[3]
S1\SVS_AK\ENA[4]
S1\SVS_AK\ENA[5]
S1\SVS_AK\EMA[6]  <--- This is a Mistake and will come up as a mismatch
S1\SVS_AK\ENA[7]
S1\SVS_AK\ENA[8]

Currently, the code I use, adds a new column to my DB_Total table, with either the value "-1" (for a match) or the value "0" (for a name without a match)

Which looks like this:

DB_Total:               
     Name               match_found
S1\SVS_AK\ENA[3]            -1
S1\SVS_AK\ENA[4]            -1
S1\SVS_AK\ENA[5]            -1
S1\SVS_AK\ENA[6]             0
S1\SVS_AK\ENA[7]            -1
S1\SVS_AK\ENA[8]            -1

What I would like to accomplish is a table that includes the following:

DB_Total:               
     Name                 Names_EA       match_found
S1\SVS_AK\ENA[3]     S1\SVS_AK\ENA[3]        -1
S1\SVS_AK\ENA[4]     S1\SVS_AK\ENA[4]        -1
S1\SVS_AK\ENA[5]     S1\SVS_AK\ENA[5]        -1
S1\SVS_AK\ENA[6]     S1\SVS_AK\EMA[6]         0
S1\SVS_AK\ENA[7]     S1\SVS_AK\ENA[7]        -1
S1\SVS_AK\ENA[8]     S1\SVS_AK\ENA[8]        -1

After the comment from Joe, I'm starting to doubt this is the correct way to do this if I also want to see the name values from EA_Table in the same table.

Upvotes: 0

Views: 1173

Answers (2)

Joe
Joe

Reputation: 63434

SELECT
    d.Address,
    d.Type,
    d.Name,
    IIf(t.Name Is Null, False, True) AS match_found,
    t.name as name_ea
FROM
    DB_Total AS d
    FULL OUTER JOIN JOIN TagNames_EA AS t
    ON d.Name = t.Name;

That gives you the nonmatches; however it does not actually show you which match up to which - it would be up to you to work out which corresponded later on.

Upvotes: 1

SchmitzIT
SchmitzIT

Reputation: 9592

Your ALTER statement of course wouldn't work. You'd typically add an empty NULL-able column first, and then use a second INSERT statement to fill in the data. However, you will have to have another column, other than name, that should define what value from the _EA table does into the DB_Total table, as otherwise you would not be able to guarantee with 100% certainty you are matching the proper rows from both tables.

You can try the following to receive what I think is your desired result:

SELECT
    d.Address,
    d.Type,
    d.Name,
    t.name,
    IIf(t.Name Is Null, False, True) AS match_found
FROM
    DB_Total AS d
    LEFT JOIN TagNames_EA AS t
    ON d.Name = t.Name;

This should show you both name results next to one another. However, it would probably be easier if you had some unambiguous ID-value to ensure you really have the properly matching rows.

Upvotes: 1

Related Questions