Solved

Add Lookup Column to existing DB on SQL2005

Posted on 2011-03-11
3
253 Views
Last Modified: 2012-05-11
No real question per se. I am just not good at this and do not want to destroy any data.

I created a Column in Access and transfered it to the existing DB via the upload Wizard. (Customers) - I filled a few Dummy Customers in and I can access it through our adp on the new form.

here's where I need the help.

Existing Database (tblDATA) does not have a Customers Column which refers to this now. - I am so not the person to do this but I am stuck with it.

Can anyone guide me step by step on how to create a column in SQL Management Studio and link it with this Customers Table I created?

There are many more linked Tables in there (tblStatus, tblDepartments and they all have an FK, int, not null) description after the name but "alas" I'm too dumb for this :-/

Step by Step solution appreciated - and I promise if it works I'll add a few Boni points :)
0
Comment
Question by:sktmx13
  • 2
3 Comments
 
LVL 39

Accepted Solution

by:
lcohan earned 500 total points
ID: 35111955
Ok, so you have tblDATA that has a column lets call it "CustomerName" right?
You want to add a matching CustomerId to that CustomerName so you can use your Customers table instead for ALL Customers related data right?
Assuming all these and if your Customers table has a Id (int),Name(varchar)....columns here's what I would do:

Add CustomerId column to tbldDATA like

ALTER TABLE tblDATA add CustomerId int null;

Populate it from Customers table

UPDATE tblDATA SET CustomerId = Customers.Id
FROM Customers
WHERE tblDATA.CustomerNAme = Customers.Name

Then you could add a FK one to many from Customers(one) table you created to tblDATA(many)

 - no worries to add new column to a table won't cause any data los however...bad written queries against tblDATA may fail. I mean any INSERT INTO ....SELECT * FROM tblDATA will fail becaus the table structure was changed.
0
 

Author Comment

by:sktmx13
ID: 35113598
The answer is totally appreciated but I am not sure if we're on the same page.

My Customer Table has 2 columns

ID - AutoNumber
CustomerName - Text 255 Chars (mostly Business Names)

I created a few Customers such as CableVision, Charter, etc...  and upsized that table to the existing SQL Database.

tblData has many existing columns but no CustomerName yet.

--------------------------------------------------------------------------------------------------------

If we're one the same page - then where do I enter the code you gave me? And how could I add a FK one to many... ?
0
 

Author Closing Comment

by:sktmx13
ID: 35359667
I figured it out but would have appreciated a more in depth approach
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Creating and Managing Databases with phpMyAdmin in cPanel.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question