In-Table Hierarchy

Geoff Taylor

Active Member
Joined
Dec 11, 2006
Messages
257
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?
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
You need four tables, as a starting point. Have a look at this image...
StudentSchema.PNG


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
 
Upvote 0
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.
 
Upvote 0
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    |
----------------------------------------
 
Upvote 0
Apologies for the delay in responding...
Your sample data record is correct; students can have multiple records.
 
Upvote 0
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:

Code:
SELECT 
    CourseNumber, 
    Count(CourseNumber) As Enrolled 
FROM
    MyTable;
 
Upvote 0
I tried these links,good.

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

xiao1.jpg

xiao2.jpg
 
Upvote 0

Forum statistics

Threads
1,224,502
Messages
6,179,126
Members
452,890
Latest member
Nikhil Ramesh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top