Tech Support Guy banner
  • Please post in our Community Feedback thread for help with the new forum software! If you are having trouble logging in, please Contact Us for assistance.
Status
Not open for further replies.
1 - 6 of 6 Posts

·
Registered
Joined
·
27 Posts
Discussion Starter · #1 ·
Ok, ya'll have always came through for me before, so here we go again.

I have a database of students. There are 4 fields:

FirstName|LastName|ID|Course1:Course2:Course3:Course4:

The field that has the courses listed has each course, one after the other, with a colon separating them.

Let's say we have 200 courses and 100 of them are online and 100 are campus. I know if I set the criteria I can get all the fields to show up for just the 100 online classes. BUT, I want it to only show the online classes in the list of courses. When Access uses the criteria I set it finds all fields with that criteria.... but a criteria that has an online class may have a campus class as well, so how do I manipulate the output?

E-mail me at [email protected] if you think you could be of any assistance. Although, I'll make sure to post any information to the forum.

I'd greatly appreciate it.
Thanks,

Wes
 

·
Registered
Joined
·
21,334 Posts
Sounds like you need to make a seperate table for just classes and set up a field for class type ans assign them each a unique value. If there is a class that is offered online and on campus, you need to differentiate them somehow, by course number or something like that. I am not sure how good of an idea it is going to be to have a field with colon seperated values in the future when you db grows and you try to do queries or lookups on that field you are gonna have trouble.
 

·
Registered
Joined
·
27 Posts
Discussion Starter · #3 ·
Ok, I guess I need to give you a little more info.

The results of this query will be used to import these students into a program we use, so it has to have Firstname, lastname, id, and all the registered courses (seperated by colon) as the fields. We are actually pulling this info from a much larger database with fields such as sex/address/phone number/gpa/ ect ect.

We have have over 3000 students and over 400 courses that have both online and campus classes so going through to the individual courses wouldn't be worth the time.

I can query the 4 fields above and get only the students that are taking an online class, but the problem is: it shows all of the courses that the student is taking... online and campus. I need it to show just online courses.

I think that should help explain a little better...


Thanks for your input and I hope to hear back from you,

Wes
 

·
Registered
Joined
·
21,334 Posts
Unless you have some way of distinguishing between online and on capus classes you are kind of out of luck. You need something to break out the colon seperated classes and put one student and one class per line and then requery that recordset, but agasin you run into poor design because there are classes that are offered online and on capmus with the same name.
 

·
Registered
Joined
·
11,755 Posts
You need to have a new record for each course.

Table1: Student info (you have already)
Table2: Course info (CourseID, Name/Description, Online Y/N field, other stuff like instructor can be included)

Table3: (Main table) Courses
Contains lookup field to get student ID (or whatever field) and lookup field to get course ID (or whatever field).

I wouldn't get into a DB this big without doing it right. You think you'll have too much data, but you won't. Since the main table only grabs the student ID and the course ID--the rest of the info is just "linked".

It'll be tons easier to enter the data in two fields, rather than editing that course field you describe. Your queries will be much simpler in the long run. You will pay hell doing a search for a specific course in the kind of text box you'll need to do that course1:course2:etc. field. It'll take forever. You can put indexes on the student ID and course ID fields and your queries/reports will be able to run much faster.

Otherwise, your data is not normalized, which is a definite no-no in the relational database field. You may want to view the information available at http://support.microsoft.com/support/kb/articles/Q209/5/34.ASP?

Just trying to CYA, Wes!

PS: If you're getting the course info from some other source and it comes out like course1:course2:etc., then just bring it into Excel, select that column and hit Data-Text to columns using the colon as the delimiter.
 

·
Registered
Joined
·
941 Posts
Hi Wes,

What everyone's telling you here not only gets my db-building soul's Seal of Approval, but it's a must. There will be no way, short of writing some pretty fancy code, to pull those individual courses out and work with them, unless you make two tables.

The flipside is, that if you need to concatenate them into a single-line, colon-separated piece of text, you'll have to do some coding, too. Such is the nature of databases; keeps me in business, anyway ;). If you will build the two tables, then post back here with the table names and the names of the fields they contain, I will write you a little function that will put all of one student's courses into a single line separated by colons. But listen to the others on this one: if you don't separate that data now, you're gonna have big headaches down the line. (Someone should have explained that to whoever built your other program, too--see how you inherited his headache?)
 
1 - 6 of 6 Posts
Status
Not open for further replies.
Top