| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-08-21 : 10:33:16
|
| Kevin writes "How can I create a lookup for a column value in a record?Example: If I wanted to display a list of product categories in the prodcategory field in the products table and select one, how would I do it?" |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2002-08-21 : 11:08:08
|
| SELECT DISTINCT prodcategory FROM productsIs that what you are looking for?Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
 |
|
|
ZMAN441
Starting Member
4 Posts |
Posted - 2002-08-21 : 11:17:33
|
| Sorry, what I am looking for is how to lookup "category values" from prod_category tables when a user is entering information in the products table in the column(field)called "category". Much like you would create a lookup in access. thanks |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2002-08-21 : 11:41:50
|
| It would be best if you posted your "CREATE TABLE" statements so I can get an idea of what you are looking for.If I remember what a lookup is in access, you can't exactly do that in SQL Server. You can define a relationship, but you have to write a query that your User Interface will use to populate a drop-down box.That query might be this:SELECT DISTINCT category_value FROM prod_categoryThen when you INSERT the record into the products table, you insert the Primary Key value into the category field in the products table.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
ZMAN441
Starting Member
4 Posts |
Posted - 2002-08-21 : 11:45:43
|
| I understand as a beginner I was wondering if it could be done at the table level with a procedure? |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2002-08-21 : 11:46:45
|
| Not that I know of...<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2002-08-21 : 11:47:15
|
| Are you trying to enter data in using Enterprise Manager or something?<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
ZMAN441
Starting Member
4 Posts |
Posted - 2002-08-21 : 12:02:58
|
| yes, through enterprize manager |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-08-21 : 12:14:03
|
| No, that won't work.Enterprise Manager is one of THE WORST data entry interfaces you can use, and it shouldn't be used for serious data entry ever. You can very easily do what you want using MS Access instead, and the wizard will even build the dropdown for you.All you need to do is link the SQL Server tables to your Access database. You can find more information in the Access help file under "linking tables", and there is a wizard for linking tables as well (under File:Get External Data menu) |
|
|
|
ZMAN441
Starting Member
4 Posts |
Posted - 2002-08-21 : 12:37:05
|
| thanks for the info. I know how to use and connect through access. I was trying to see if this could be accomplished without the use of an interface. Thanks for confirming it cannot.Kevin |
|
|
|
explicit
Starting Member
1 Post |
Posted - 2002-08-24 : 15:35:43
|
| SELECT DISTINCT prodcategoryFROM products what is the distinct for? |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2002-08-26 : 11:49:02
|
Lets say you have a table that looks like this Item Category---------------------------Widget Category1Whozit Category1Whatzit Category2 If you did "SELECT Category FROM Products" You'd get category1 twice in your result set, making your drop-down box be ineffective.On the other hand, if you do "SELECT DISTINCT Category FROM Products" You'll have two records in your result set, which makes sense.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
|
Lookup values (SQl Server 2000)