Can one field in a table be used to limit the result of another field in the same rec

[swinster]

Hi,

I have a fairly simple database that looks like the attached. It is the beginnings of a school database. Unfortunately, the MS template doesn't quite do it.

We have around 700 pupils in 5 year groups. Each year's intake is assigned the year of that intake (e.g. 2002, 2003 etc).

The youngest pupils are in Year 7 whilst the oldest are in year 11.

Each Class is assigned based on the intake year but given the name of the schooling year - (e.g. year 7, year 8 etc). And there are 6 classes per year - hence names 7.1, 7.2, 7.3 etc.

My "Students and Classes" table should combine both the classes and pupils. However, I want to be able to firstly select the class to put the pupil into (and hence infer the induction year), then just show in the next filed only
the pupils that belong to that induction year and hence go into the selected class.

However I design my lookup or query, I can't seem to figure out how to do this, or if this can even be done.

Any help is much appreciated.

Chris

 

[MarkK]

Consider these relationships.
Note that the Year becomes an attribute of the relationship between a student and a class, or a teacher and a class. With this design you can specify a year and a student and easily return a class list with grades. Specify a year and teacher and you can easily return that teachers class lists. To find a student's year, do math on the the students IntakeYear.
Time is not object in itself, but rather an attribute of an object and a variable quantity. Time therefore rarely merits its own table.

 

[swinster]

I had thought about calculating schooling year from "actual year - current year". However, the schooling year span two actual years, so the current Year 7 will be in the 2006 intake year and finish in 2007. Buy putting the years into a table I thought it would be easier to then write code so at the end of the academic year I could archive all current year 11's into another table, then alter the year code for the other years by 1 and add a new record into the YearGroup table for the new intake year 7's.

To make it more complex, the school is divided into Form groups and Class groups. The children will be one group for registration (their form - e.g. 7AH, 7BJ etc) then switch into their Class groups for the rest of their lessons (e.g. 7.1, 7.2 etc). For the main, the children will remain in the same class set for ALL of their lessons.

The class and form name are made up of the schooling year the children are in, and the initials of the teacher (for form groups) or the class set (for lesson). Hence, in year 8, the classes above would become 8AH, 8BJ, 8.1 and 8.2 respectively).

I was going to have one table for forms and one for class sets. Only one child should be in any one form or set at any one time.

Teachers to not always teach the same class year on year apart from the registration form which they stay with until year 11. Other than that, they will be assigned to different class sets to teach. The year of the class they will be teaching will depend on the schooling year of the pupils.

From what I can see in your example, I would still run into the same problem as I am having now. If I have a ClassName of 7.1 or 7.2, in the tClass table, when I select a ClassID in the tStudentCLass table, I would only want to see the StudentID for those pupils that relevant to that year group – i.e. all year 7 students. I believe I would be presented with all pupils from all intake years – which is exactly what I am trying to remedy.

Maybe my ideas and table desing are a little haphazard. I am no database expert and have not touched anything more complex that a 3 table design in a LONG time.

Chris

 

[MarkK]

Don't archive stuff into a separate table. Tables are intended to store data based on data structure. Use queries with criteria to return a subset of records valid for your purpose. In this respect, even more clearly, a date is an attribute of data which you use to limit the records returned by a query.
That a school year spans two calendar years is a math problem. Criteria here looks like...

>= CDate("Sep 1 " & SomeYear) And < CDate("Sep 1 " & SomeYear + 1)

...but there are tons of ways to do date math in Access.
I can't really comment on Form Groups and Class Groups since I don't see what the criteria are for membership. Do you just put kids in these groups ad hoc? I'm not clear from your description whether classes are synonymous with subjects. In elementary school I was with the same kids, same teacher, all subjects. In high school I went to different classes with different kids and different teachers. Modelled as computer systems the two would have been completely different.
Generally, everything that is a distinct and invariable object merits a table. A subject. A teacher. A student.
But if a thing A can be calculated from another thing B then B is an attribute of A, not a distinct object. What I think of as a Form, for instance, would be a calculation on IntakeDate and perhaps some Pass/Fail data express as the attributes of other objects. To me a Form is an attribute.

 

[swinster]

Lagbolt, Many thanks for the input. I have at least firmed up some "do's and don'ts" of what I need to do. I will restructure the table as such. It was mentioned elsewhere that graduation year would be preferable to intake year (i.e. what happens if a kid joins from another school), but the math is similar - I just need to figure it out and built a function to calculate the kids school year from that.

With regard to the school structure, it is a bit like the high school you describe.

The school is a secondary in the UK. We teach ages from 11-16. The 11/12 year olds are in Year 7 and the 15/16 years olds are in year 11. There are approximate 700 pupils in the entire school, which works out to approximately 6 groups/classes in each year.

The school day is divided into 6 lesson periods, 50 minutes each. We also have a registration first thing in the morning and just after lunch. The children are grouped in 'forms' for registration, which simply means that children of all abilities in a single year group are put together for this registration period. These form groups are named after the year the children are in and the initials of the teacher that takes the form. E.g. Year 7 pupils in John Smiths form will be in form 7JS. The following year the form will be called 8JS, then 9JS etc. The children almost certainly stay in the same form throughout their school life (unless needed to be moved for behavioural reasons).

When the children go to lessons, they go into different classes based on general ability. So, those of the highest ability in year 7 go in into 7.1, then 7.2 .... to 7.6. The children will have all their classes (Maths, English, ICT, etc) in these 'sets' (although they may alter slightly for certain subjects such as Maths and English). In essence they stay in the same class but this CAN change between subjects - so is more akin to you high school example.

I hope this gives a better understanding of the form/class system. This is a very common way of doing things in the UK.

The class name are very general (i.e. History, Geography, ICT, Maths, English, RE, Art, PE, Science, Welsh, French or Spanish). It is possible that John Smith can be form tutor to 7JS and teach 7.1, 7.3 and 7.6 in ICT this year but then teach 7.2, 7.4 and 7.5 next year (or in fact any combination, and will teach all year groups 7-11), but teachers will usually stay within their specialisms.

I think that this is the general school structure but as I'm sure you are aware, things are subject to change and special cases.


The main reason for building this DB was to simplify the miryad of data sources we have for pupils at least in the IT department if not the whole school. Keeping a track of their attendance in form and classes as well as all assessment material, exam results, reports and behavioural incidents would be useful, as well as seating plan layouts. This is an awful lot to take into account so I only wanted to concentrate on the seating layouts and classroom structures first then add assessment and the rest. But I am also aware that I don't want to keep re-designing.

 

[MarkK]

- So form is what we call grade, like "grade 6 was my last year of elementary school."
- But in high school, what grade I was in was not a rigourously definable thing. I needed a certain number of credits to graduate. Some of those credits had to consist of core courses like English 10, English 11, North American Lit, French 10, French 11, etc... Some credits were from what we called electives.
- So if I failed French 10 (core) and Techincal Drawing (elective) when I was in 10th grade, I'd have to take French 10 again in the Summer or during my 11th year. If I took French 10 during my 11th year I would not graduate with most of the other kids in my class. The Technical Drawing credits, on the other hand, I could replace by passing a Typing course during my 11th year, as long as I had a certain number of elective credits.
- Elective High School courses had no grade level distinction, so I took Biology 10 when I was in grade 8.
- So if I'm a student with mostly Grade 11 level courses but some Grade 10 level courses, what grade am I in? The concept was not rigourously defined enough for it to have been a part of a computer system that would have modelled my high school.
- My high school could have been represented in a computer system using only Class, Student, Teacher, and the many-to-many join tables where appropriate.
- Also, John Smith's form for grade 7? Intuitively 7JS, but calculated using a class, say Homeroom, from the tClass table, which would be linked to the teacher (JS) and being the current year minus an average of the intake date of the students (7). And the following year the students in 7JS would not need to be explicitly re-assigned to 8JS. System date math does this automatically.
- Just trying to get you to be rigourous about how your tables are designed. One record in one table should be irreduceable, discrete, distinct and having no possiblility of being calculated from other data in other tables. If you get this right at the beginning it'll save you a million hours later on trying to coerce poorly related data into a useable form.
Cheers,
Mark

 

[swinster]

Things are never quite as simple as they appear!

Indeed, the concept of our Form is close to your Grade, but the similarities do not continue. The form year a child is in, in the UK is defined by their age and nothing else. A 15 year old child could never turn up to school but would more than likely be place in year 11. Of course having said this, we did have one year 8 boy that was placed in a year 7 class at the beginning of this year, however, he has now been moved. There is no credit system and no formal method of holding back pupils (methinks that some pupils would never leave school ).

As both form and class numbering are defined using the school year of the pupil, then indeed you do not want to reassign class numbers for the majority of the class each year. 7JS will become 8JS, and 7.1 will become 8.1. The problem with the latter is that there will be a new 7.1 class for the new year 7 intake, where as John Smith will stay with the same form for the 5 years they are in school and will then be reassigned to a new year 7 intake (unless of course he leaves to go to another job).

So for example -

This school year (2006-2007) we want to set up classes 7.1, 7.2, 7.3 ..... 7.6

To select the pupil that will go into these classes I want to be able to select (in the main) only from those students that are in the current year 7 (of course I have mentioned above an exception - so I guess I could use a tick box on a form that would apply a filter criteria, but could be de-selected to show all pupils if needed).

The following year (2007-2008) those pupils in 7.1 will move to 8.1, 7.2 will move to 8.2 etc, in the main. It would seem sensible to be able to easily, if not automatically, rename or make a new class sets as needed (I think this is why I had my year group table - I could easily rename year groups and this change would cascade across all classes). However, there will also be a new year 7 intake and they will be divided as before (7.1, 7.2 ....etc), so I need to be able to reuse the class name but for the new pupils.

Now for the curve ball. Things do change a bit in year 10, as the children take "options" in year 9, allowing them to select subjects they want to take to work toward their GCSE's (formal qualification) in that subject (although certain subjects are still compulsory - Maths, English, Science, Welsh). New classes are also introduced that previously didn't exist (such as ICT foundation as well as ICT GCSE, expressive arts and others). This will kind of "balls up" the above as now some pupils will not be in the expected automatically defined class of 10.1, at least not for all subjects. Now we are at a point that is similar to your high school where pupils will go to different classes. At this point classes may need to be completely redefined and could be different for each department. Although the model changes in year 10, I still want to be able to select pupils for year 10 classes just from those in year 10, and once done, the pupils will then remain in the same classes for year 10 and 11, so again should be automatically/easily re-assigned.


These are two distinct teaching blocks and are separated into what we call "Key Stages". So, year 7, 8 and 9 are in KS3, whilst 10 and 11 are in KS4. The pupils in KS3 follow the national curriculum hence all do similar subjects and so can be put in the same classes/sets for each subject. At KS4 they move toward formal qualification in each subject so there is an element of change.

Thinking about it further, we would need to be able to record what marks/exams/comments (or what ever) where made and what class the pupils was in at that time. So if the class number for a pupil automatically changes from 7.1 to 8.1, I need to know that result and comments made when the pupils was in 7.1 not 8.1.


This all seem so easy in real life. It is not until you start to break it down for computer consumption that you realise how complex and confusing the model is.

 

[The_Doc_Man]

This all seem so easy in real life. It is not until you start to break it down for computer consumption that you realise how complex and confusing the model is.

This simple yet profound statement directly touches on the intuitive nature of some things and the problem brought about by transferring intuitive things in Access. Since it is a dumb program running on a glorified adding machine on steroids, intuition is not one of its strong points.

This leads me to one of the things I call "the Old Programmer's Rules." If you can't do it yourself on paper by hand, you will never get Access to do it for you. And it is PRECISELY because computers have no intuition, no vision, no ability to extrapolate ideas - only numbers - that you have this problem.

I'm not going to try to download anything. I need to emphasize the METHOD to you. Start by really studying "DATABASE NORMALIZATION." Don't limit yourself to Access help. If you have a textbook that includes the subject, hit the books. If not (or even if so), Google-search "database normalization." Only bother with items from sources you recognize - reputable colleges and universities from the .EDU domain, perhaps some database product vendors from the .COM domain, and just pick a few because even those criteria leave you with a lot to read. For Access designs, shoot for 3rd normal form and you'll be fine.

You must start at the beginning - the MODEL. You must deconvolute it by hand first in order to expose its rules. I've made this suggestion many times to many people facing a really nasty problem for the first time. If you know ever part of this, then I've insulted your intelligence, which is not my goal. But sometimes when reading problem descriptions, I get a sense of someonee needing to have those basics reinforced. Here goes...

OK, next, get a bunch of supplies. My usual advice is a dry-erase board and a box of markers, assorted colors if you like color-coding. Get some sticky notes in quantity. (The idea being if you have leftovers, they are still useful for office environments.)

Now tear into your problem to identify the entities you wish to represent. Entity in this context = person, place, thing. (Since you are in the education field, entities are frequently named as NOUNS for which collections are possible.) Children - a collection being a bunch of them. Years - a collection being an eternity of them. Classes - a collection being a school full of them. Etc. etc. It does not matter whether they are concrete or abstract things. Children are concrete. Classes are an abstaction of the more general concept of identifiable groups. So either way works.

OK, now you define (on your white board) the tables that will hold records representing the elements of the collections. Because in Access, collection = table (except when we are talking about the Access internal structure called a Collection.) The sticky notes will represent individual records. Choose a size large enough that you can write names or numbers or something to keep individual items identifiable within the collection/table.

Now make a list of what you want to do. Write this down in a very formal list. Make it a check-list. Keeping normalization rules in mind, for each item in the check-list, see whether you have enough information among your tables to accomplish the goals.

Actions frequently take the form of queries such as append, delete, insert, maketable (should be rare), etc.

Things you have to do by hand often take the form of Forms.

Selective access to table elements - in sense of only seeing what you wanted to see - often take the form of Queries. Seeing those things in a certain order or with certain transformations - such as a lookup - also occur via the query. Forms and reports are often best driven via queries rather than directly through tables.

If you have really complex transformations, something that you cannot break down as a sequence of query actions, you MIGHT want to delve into VBA - but leave that for the nastiest of implementation problems.

Goals might include moving children from school year to school year or it might be something else entirely. It is OK to have multiple low-level goals. The physical proof of goal accomplishment is often a report. Progress tracking is also done as a report. Finally, since this is bound up in government issues, there will be reports to the government that are goals in themselves.

Take these design concepts in hand and apply the most powerful of ALL computers to the solution. Use your mind. Deconvolute the problem along the concept that worked 2000 years ago, when Julius Caesar used the basic principle, "Divide and Conquer."

You are asking us for ideas, and we are good at that to a limit - but the final solution must work for you. It is your problem. That is also why we often have trouble giving you a solution. You are the one who KNOWS the problem and will have the best chance of dividing into pieces - because from our side of the web screen, we can't see the pieces. You can.

If you have specific questions, come back here. But right now, your path isn't to ask us questions. It is problem examination like you have never done before. Good luck! Continuing to part II///

 

[swinster]

The Doc Man and Lagbolt, I thank you for you informed comments. Although I have studied relational databases and understand the basics of normalisation, putting this into practice if have always found difficult. It is clear that I needed to remap the entire project and formailse what I wanted to achive. I have now spent most of the day trying to do this but I will post a new thread and ask for comment there as I have now moved away from this thread topic.

I hope you can overview the new thread and comment on the entities and formal requirements.

 

[The_Doc_Man]

My ISP choked before I could finish part II.

Where I took that thread was the second "Old Programmer's Rule" - Access cannot tell you anything you didn't tell it first.

Discussing the things you do in the database, your goal will almost always be a form or report that helps you visualize what you have. Let's take a look at rule #2 as it applies to reports.

The reason you want to have records represented is because as you go through your checklist, you have to decide just how you will (or CAN) implement the reports you want. Suppose you have two students. Joe Blow and John Bull. Suppose they need to be in different segments of the report for some reason. The indicator of that reason MUST exist in the database or you will be unable to separate them. This is an example of working backwards to find fields you didn't originally think you needed. But if they are the only way to distinguish between two targeted report sections, you've just identified something that MUST be in the tables.

Now, there are things that you could want that naturally lead to the issues of parent/child tables and many//many tables.

Suppose you want to see everything that is current about John Bull. No problem, he's in the student table. BUT ... what if you needed to see some facts of his past performance? You need a student history table for which you have at least two keys taken together (what we call a compound key). If you have history data, it cannot go in the student table because you need a DATE field to specify which part of the history you want to see. Based on the normalization rule, historical data that includes dates as a partial key cannot go in the student table because that only depends on the student. So you have just identified a need for a SEPARATE entity history table with the entity ID and a DATE as the compound prime keys. When we talk about the record's contents depending entirely and only on the prime key, this chain of logic is how you recognize the need for a second table.

The other case is, for the more advanced students, you might have many variations in schedule. For instance, John Bull might be taking history, geography, business math, and English. Joe Blow might be taking English, advanced math, biology, and chemistry. You would probably have a table for the "subject" entities. Might even have a separate table for the class sessions for the current year that contain subject ID, session ID, class meeting times, room numbers, and teacher ID. But you can't list the students in that table. The key for sessions shouldn't mix in students. So you make a new table holding the student's ID and the session's ID in a record. One record for one attendee of one session. (Not talking about attendance records and daily sessions. I was thinking of a session like 10AM MWF or 1:30 TTh - that kind of session.)

Anyway, you would need to list one record per session per student registered for that session. Again, it is the "purity of keys" issue that makes you split the tables apart and create the junction tables (of which this student/session is a good example).

In each case, as you populate your tables and think about the reports and forms you need to display, you must look for purity of keys and presence of the discriminating data to see whether you need to add a new field to an existing table, or a new table based on combinations of keys that describe what you just identified.

No massive project comes easily, and I'm sure you can see just how massive this project really is. It will be a bear to wrestle into submission. But it is a good project and I wish you lots of success with it.

 

[The_Doc_Man]

And now, with the topic of "purity of keys" having been explored, I will answer your original question.

Can one field in a table be used to limit the result of another field in the same rec

No.

The "purity of keys" rule says that every field in a record must dependly only and entirely on the prime key. Not on part of the prime key for the case of segmented keys. The WHOLE prime key or it is disallowed - to purists. Having field X depend on non-key field Y violates the "purity of keys" concept. Even to me, a tried-and-true pragmatist, this is one of those rules that I would be extremely reluctant to violate. Your table's normalization status depends on purity of keys.

If your design requires this, you have a serious problem. It is worse if your real-world model ALSO requires this to happen. However, the odds are that you can redesign this requirement out of your table structure.

 

[swinster]

And now, with the topic of "purity of keys" having been explored, I will answer your original question.

No.

The "purity of keys" rule says that every field in a record must depend only and entirely on the prime key. Not on part of the prime key for the case of segmented keys. The WHOLE prime key or it is disallowed - to purists. Having field X depend on non-key field Y violates the "purity of keys" concept. Even to me, a tried-and-true pragmatist, this is one of those rules that I would be extremely reluctant to violate. Your table's normalization status depends on purity of keys.

If your design requires this, you have a serious problem. It is worse if your real-world model ALSO requires this to happen. However, the odds are that you can redesign this requirement out of your table structure.

Doc the Man, I'm posting back here because no matter how I think about this, it seems to come back to the same problem. I cannot figure out how I would "join" class sets to pupils in the manner that I want.

If all pupils are contained within a table, we could say that an attribute of the pupil would be their graduation year. This would lead us to be able to calculate the school year they are in.

Now, I can set up class names in a different table, and even set up a further table to indicate that this class is associated with a particular year group.

However, at some point in need to associate a certain pupil with certain classes so I will need to join two tables. I will want to select the class set (one field), then add a pupil to that class set (second field), but I ONLY want to see pupils that could go into that class set based on their year group and that the year group of the intended class.

Therefore in need to filter the possibilities of the the second field based on the selection of the first. I just can't seem to figure out any other way of doing it.

 

[The_Doc_Man]

To associate students to classes you need THREE tables.

1. Student table

2. Classes table

3. Student/Class junction table which MIGHT be as simple as listing student ID and Class ID in a single record of two fields - to show that student X is in class Y. The relationships are such that each student (one-side) will have more than one class, so the junction table lists them (many-side). Each class (one-side) will have many students, so the junction table lists them (many-side). Then you sort the JUNCTION table on student IDs to see which classes the student is taking, or sort on class IDs to get a class membership list.

In a junction table, you have a way to accomplish a many//many join in only two steps - by accomplishing two joins, each one//many. First, join the junction table to one of the main tables. Then join that query to the other main table. When you have that, you have all you need to print either report. Depending on your Access version, the newer versions allow a more complex syntax to do a multi-join in a single select. But doing it as two selects in a layered setup still works and is easier to understand for folks new to Access concepts.

HINT: To make sorting faster, sort on the (probably numeric) ID fields if you don't need the text equivalents to be alpha-sorted.

From set theory, remember that each set has a chance to overlap with other sets. Where this overlap occurs, set theorists call that the junction of the two sets. (Hence the JOIN nomenclature, JOIN and JUNCTION being derived from the same Latin root word.) So in DATABASE set theory terms, you need a junction table to enumerate the members of the two sets at their junction.

I repeat, you cannot do what you want inside a single table. But there is no such restriction if you apply your filter dynamically to a third table such as the indicated junction table. If both class and student have an associated year group, you can establish a filter on the ELIGIBILITY list (which might be only a query) based on joining that year number properly - or improperly.

The latter is - suppose students in 8th year AND ABOVE can take certain optional classes in any order, but 7th and below cannot. For your core course where there is no option - take 8th year English in your 8th year - you have a list based on strict equality - and you can mark a class as "strict year match" to make the eligibility query select them. But with classes noted as "minimum year match" you have a second issue. Here is where a UNION query might help. I'm going to make up names to allow this to be clear.

Query ELIGIBILITY:

SELECT StudID, StudYear, ClassID, ClassYear, ClassStrict FROM Student, Class WHERE ClassStrict = TRUE AND StudYear = ClassYear UNION
SELECT StudID, StudYear, ClassID, ClassYear, ClassStrict FROM Student, Class WHERE ClassStrict = FALSE AND StudYear >= ClassYear;

This is only your list of people eligible to take particular classes. You could use it to populate class filling forms. You can, of course, sort this with ORDER BY clauses - or make the form do the sort for you. You can perhaps write a little something that allows you to see the combinations in this list and if you click on a button, that class assignment is made for you. Might take a little bit of VBA underneath to populate the JUNCTION table's recordset from the fields of this query - but it might also be very fast.

Another view - and this is your call - is that if there are mandatory classes that if you are in year 8, you take the year 8 list plus some options, you mark the class with another flag saying ... optional (but beware, "optional" is a keyword in VBA, so pick another good name). Then instead of having to pick class membership for the non-optionals, you just assign them and be done with it. Then and only then, go back to figure out what optionals the students can take within eligibilty.

You might also need a DCount query of the JUNCTION table to show you how many classes each student is already enrolled to take on the form you use to finish class assignments, just so you don't give some poor kid 14 class hours daily. Never hesitate to sanity-check your results even if sometimes it seems like overkill. You'd rather overkill your code than overkill a student, right?

 

[swinster]

You'd rather overkill your code than overkill a student, right?

Er, depends on the pupil

But seriously, many thank for this. I will need to actually build it to see it working. I'm not hot on the SQL syntax. So let me get this straight.

In the junction table I want to add a new record that will combine a class with a pupil (which is what I originally had). I will first select a ClassID (which will be used of the first field in the junction table. The UNION query will then be able to use look up information (YearGroup FROM Class) to be able to filter the Pupils to show only those in that year group. But this is done within a FORM rather than a TABLE

This is exactly what I was trying to do originally but I was trying and get this working within the table lookup (I have now read people comments on table lookups, but I figured if I could get it working there, I would be able to get it working later on). Rather this is a FORM operation.

 

[The_Doc_Man]

Form and/or query.

The junction table is a list of places in your tables where students and classes collide, so to speak. (And as hard as it is to educate SOME kids, the collision is more like a train wreck... I so truly DO understand your comment "depends on the pupil".)

You can build this any number of ways, but the easiest way (not always the fastest, please note) to build the empty table. Now build a form based on that table. (Yeah, I know ... it's empty.) When placing controls on the form, I'll suggest two combo boxes, one for student and one for class. The tool wizards must be enabled for this next part to work. You tell the combo box to pick the students by name, but STORE the Student ID field. Ditto, pick the classes by name but STORE the Class ID field. Then store the record.

Now this is where the smarts in the form COULD be helpful. There is a topic you need to read. Search this forum for "Cascading Combo Boxes" to see how to take a student's ID as the criteria for what classes appear in the drop-down list for available classes. You already "know" the student's form-year. You already know the class form-year (whether exact or minimum). These items can be used to drive a QUERY (not a direct lookup from a table) of available classes to supply the combo box for Classes.

The UNION query syntax I showed you was a way to include TWO sets of criteria. You can also do this another way, since SQL is such a full-featured way to make messes.

SELECT StudID, StudYear, ClassID, ClassYear, ClassStrict FROM Student, Class WHERE ( ClassStrict = TRUE AND StudYear = ClassYear ) OR ( ClassStrict = FALSE AND StudYear >= ClassYear );

Both the original union and this more complex WHERE clause have the same effect. This one reads better. In any case you could use a variant of these as a way to select what classes a given student is allowed to take.

We haven't touched on this yet, but there are other things to consider, such as the time that a student takes each of these classes. There are topics here on "scheduling" and "booking" that might help with that issue.

I may owe you a slight apology, too. I hope some of these ideas don't overwhelm you or leave you totally confused. See, the glory of Access is that you can do as much as you can imagine with it if you follow the rules, and I've been known to have an overactive imagination. Hence my fantasy-fiction writing hobby. Let me say this: I am certain beyond doubt that your problem is soluble by normal methods. But be aware that at this point, you are still in the phase where you are seeking inspiration and direction. You literally have too many choices right now. Inherently the most confusing part of the project. I do not doubt that you have serious misgivings. And that is quite natural.

We all get the butterflies in the stomach when attacking a really big project. I'm a 35-year veteran of the programming wars and I still get that tingle now and then when I have decisions to make. Just remember to keep your eyes on the prize. Actually, prizes. Learning and building something useful - both eventually leading to a lot of self-gratification. Improved reputation at work. Good for one's career. And then there's the down side. If you make it work, it'll be yours forever. Don't be surprised if 90 years from now they dig up your coffing and bang on the lid. "Swinster. SWINSTER! How did you make that query work when the kid hadn't met all the qualifications...?" You'll NEVER rest in peace if you make it work.