Finding text in Column

ElvisR

Registered User.
Local time
Today, 13:36
Joined
Aug 3, 2007
Messages
20
Hi all, it has been a while since I delved into Access, but here I am.

I have a table that contains two columns (names and classes attended). In the Classes column, the entries are separated by commas, so it look like: 1st, 2nd, 3rd etc.

I am trying to run a query to show all in a specfic class, i.e. 51st. The problem I am encountering is that the like operator returns all classes that contains 51st, 151st etc.

I am looking for suggestions on a better course of action.

My thanks.
 
Looks like you aren't storing your data properly (as a normalized database should).

Each field in a record should be ATOMIC ( that is, the lowest particle that will stand on its own).

You should not be storing multiple data within a single column. This is just plain bad design and it will cause you no end of grief to get things out, as noticed by your post.

You need to fix the data BEFORE GOING ON. You can't query reliably data that is stored this way.
 
Thanks for the response.

I tried putting each Class in a different columns, after I received the data in Excel format, but did not have much luck their either.

What is your suggestion?
 
Thanks for the response.

I tried putting each Class in a different columns, after I received the data in Excel format, but did not have much luck their either.

What is your suggestion?
They don't go in columns, they go in RECORDS.

So for example:

tblStudents
StudentID - Autonumber (primary Key)
StudentFName - Text
StudentLName - Text

tblClasses
ClassID - Autonumber (primary key)
ClassDesc - Text

tblClassOfferings
ClassOfferingID - Autonumber
ClassDate - Date
ClassID - Long Integer (Foreign Key from tblClasses)

tblClassesStudents
ClassesStudentsID - Autonumber (Primary Key)
ClassOfferingID - Long Integer (Foreign Key from tblClassOfferings)
StudentID - Long Integer (Foreign Key from tblStudents)


Something like that. And then you can use queries to pull all of the information you need together and use criteria on whatever field(s) you need.
 
instead of

john smith : math, science, french

you should get a table that looks like this

studentname course
john smith -- math
john smith -- science
john smith -- french

--------
in fact you should have a student table, and a course table

so you get

(student) 1 - john smith
(student) 2 - mike reilly

and

(course) 1 - math
(course) 2 - science
(course) 3 - french

and then you get your classes table, joining these together

student course
1 (takes) 1
1 (takes) 2
1 (takes) 3

hope this makes sense
 
Hi -

If you would post a page or two of the Excel spreadsheets, we can probably provide an automated method of populating properly normalized tables.

Best Wishes - Bob
 
Thank you all for helpful input. I am not really trying to make a permanent database out of this data, I just need to extract data based on specific info.

I have attached an example of the type of data I am dealing with, for your input.
 

Attachments

Users who are viewing this thread

Back
Top Bottom