adding an existing column to an existing table MS_Access

Question

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.

I am confused. You are joining on name. So, the names in the Tagnames_EA table are already there - they're the same as the names on the DB_Total table. Are you asking for the non-matches to also show up? — Joe
– Joe, Commented Feb 21, 2013 at 8:35
I understand the confusion, I will try to make it a little clearer for someone that has not been working with this database for the past 2 weeks! — Gutanoth
– Gutanoth, Commented Feb 21, 2013 at 8:39
Currently, it adds a column to the DB_Total table named "match_found" If the names from the two tables match, the corresponding field in the "match_found" column returns "-1", if they don't match, it returns a "0" — Gutanoth
– Gutanoth, Commented Feb 21, 2013 at 8:44
Sorry, that doesn't help because I don't think you understood my comment. Based on the SQL code you posted above, your new table (if you created it from that select) would have all rows of DB_Total table with an indicator if it was found in the tagnames table; but there is no second variable to add on. Can you show an example of the data in each table and the result you would like to see? — Joe
– Joe, Commented Feb 21, 2013 at 8:45
How are you going to identify that the EMA row should be matched up to the ENA row? Your join won't accomplish that, certainly. — Joe
– Joe, Commented Feb 21, 2013 at 9:22

SchmitzIT · Accepted Answer · 2013-02-21 09:18:16Z

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.

Joe · Accepted Answer · 2013-02-21 15:56:57Z

1

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.

edited Feb 21, 2013 at 15:56

answered Feb 21, 2013 at 13:58

Joe

63.5k7 gold badges51 silver badges70 bronze badges

2 Comments

Gutanoth Over a year ago

This code generates an error after wich it highlites the "t" from t.name as name_ea within the SELECT instruction

Joe Over a year ago

I left out a comma, try again.

Collectives™ on Stack Overflow

adding an existing column to an existing table MS_Access

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related