In-Table Hierarchy

[Geoff Taylor]

Hi,
Apologies if this is newbie level stuff; I'm better with Excel than Access...

I have a table of students. Two of the fields in each student record are the individual courses being studied and an overall program of study.

Each program is made up of courses. Each course can contribute to several programs. (For instance, a course on basic anatomy could form part of a medical program or a sports science program).

I would like a query to generate a number of students for each course within each program. Could someone point me in the right direction?

 

[SydneyGeek]

You need four tables, as a starting point. Have a look at this image...

Actually, five. Drew this up and noticed that, just like the join table between Courses and Students, you need a join table between Programs and Courses. That's how you build a many to many relationship in Access (or any other database).

To give yourself an interface for entering all the data, build forms and subforms. A Student form with a subform based on Enrolments lets you add courses to a student's study load.
A similar Enrolments form in a main Courses form lets you see who is enrolled in any course, and add new students. The important thing to remember is that the 'Many' side of a relationship should be in a subform; the relationship between the forms should mirror the relationship between the tables.

Queries with the Student, Courses and Enrolment tables will let you see the detail of who is enrolled where; and you can use those queries to build reports.

Denis

 

[Geoff Taylor]

Thanks for this. It makes sense.
The dataset I'm working with is a single table exported from another piece of software. Splitting it into 4 tables will be a pain, but if that's what it takes ...

Thanks again.

 

[xenou]

It's a little odd having only one field for course and one for program. Are you saying that a student only has one -- and only one -- course? Or is the data repeating? Example, student 1 has two courses and therefore two records:

----------------------------------------
| Student | Course       | Program     |
----------------------------------------
| S001    | C001-Anatomy | Medicine    |
| S001    | C002-Biology | Medicine    |
| S002    | C002-Biology | Engineering |
| S003    | C002-Biology | Medicine    |
| S004    | C001-Anatomy | Medicine    |
----------------------------------------

 

[Geoff Taylor]

Apologies for the delay in responding...
Your sample data record is correct; students can have multiple records.

 

[xenou]

It's likely not to be the best structure for a database (according to the well trod rules of database normalization), but if all your data is in one table I should think you could write a simple query:

SELECT 
    CourseNumber, 
    Count(CourseNumber) As Enrolled 
FROM
    MyTable;

 

[dearsara]

I tried these links,good.

Andrew's Excel Tips
The Spreadsheet Page
AJP Excel Information
Contextures
Office Automation, Ltd.