This is a discussion on retrieve values within the SQL Development forums, part of the Databases category; Hi, I have a field called Options The values in the field are stored like this Easy to access, Somewhat ...
| |||||||
|
#1
| |||
| |||
| Hi, I have a field called Options The values in the field are stored like this Easy to access, Somewhat easy, Very hard How can i find the count of "somewhat easy" in this field todd |
|
#2
| ||||
| ||||
| What database are you using?
__________________ jmurrayhead If you agree with me... click the icon! If my post solved your problem, click the button in the lower right-hand corner of the post.If you like it here...throw us a few bones to help support us. Join our Folding team: DeveloperBarn Folding |
|
#3
| |||
| |||
| sql server |
|
#4
| ||||
| ||||
| Okay, I think it can be done if you use a stored procedure with some TSQL. However, I encourage you to rethink your database design. This does not follow database normalization rules. Instead of a comma delimited field, create another table that will create a relationship with the values you are storing and the record. For example, let's say you have the following tables: Users: userid username password UserGroups: groupid groupname permissions To allow a user to be a member of multiple groups you would have the following table: UsersInGroups: ugpid userid groupid Then you can more easily and efficiently do any type of query against the data with the help of joins to get the desired result. I suggest you do the same with your database. |
|
#5
| ||||
| ||||
| I take it to mean that the field can be one of 3 values, Easy, Sorta, Hard (or whatever). However, I wouldn't do it this way either, but add the other table with 3 records and a PK of 1,2,3. Then in the parent table, the field either has a value of 1, 2 or 3. So the count becomes: Code: SELECT COUNT(1) FROM daTable where daField = 2
__________________ Wolffy ------------------------ Opinions expressed are my own and do not necessity reflect those of any sane person. Any code provided is intended to be an example and is provided AS IS. Rework for your specific environment may be required. Void where prohibited by law. Not valid in California. Your mileage may vary. |
|
#6
| ||||
| ||||
| OK, on third read, I've changed my mind. One of the basic rules of relational databases is the a filed can have only 1 value. So you should create a table for storing your three options -- same advice as in my last post. |
![]() |
|
| Bookmarks |
| Thread Tools | |
| Display Modes | |
| |
| ||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Store Calculated Values (Updating Fields) | sbenj69 | Access Database Samples | 9 | January 12th, 2009 11:49 AM |
| Data is Null. This method or property cannot be called on Null values. | Shem | .Net Development | 8 | June 17th, 2008 09:39 AM |