Search results

oooh, how do you do that? I have currently split the db and the temporary tables are not in the backend. So they are created by the user on demand but never appear in the backend of the db. Is this an okay approach? Martyn

It is calculated in a query. The mark is turned into a percentage. I join this to a table containing the grade sets and use a where >= and < the desired mark. It took a little getting my head around but it all works. The db contains sensitive info at the moment but I can create some sample...

Thank you for that. It is much faster and has helped me a lot. I am stuck creating a temp table because I am doing a substantial amount of processing of the queried data. I tried putting the processing into the query but it slowed everything substantially (load time of > 10s for a form). I...

Thanks for your help Larry. Reducing the complexity has made things much simpler and (so far) I have been able to implement all the functionality I need.

Thanks for this. It felt quite slow to load so I have dumped the query into a temporary table. I can now hide columns easily.

Thank you. I was told by another member to only use no duplicates for primary keys. So i thought that composite PK was better in this regard.

I have a crosstab query that can hold student marks from a predefined number of tests. I want to display the query on a form so I have set the column headings of the query to the known values I expect from the test titles. This all works fine. However, I would like to hide columns that...

Hi Larry, Here is my redesign. It seems to me to be more logical. The student_has_subject junction table expresses the relationship Each student can have many subjects Each subject can have many students The primary key of this junction table is an instance of a student_subject (these are...

I am a subject lead. Students study with us for two years and do final exams. They move between classes as they move from one year to the next. We have a school management system that deals with the day to day. I want to input and track grades in the 12 classes of students of (at first just one...

Hey Larry, I know the feeling about old age.... 😂 I think I have come up with a design that works. I had some time while a class was doing a test. I took on board the suggestions from your posts got rid most of the junction tables. I now have assessments belonging to subjects. I can easily...

Thank you. I will take a look properly over teh weekend. BTW, I can't see the tables/forms/queries on the left of the screen. how did you hide them? Martyn

Thank you Pat. I will take a step back and have a good think. The model in the UK is very different. Students are with us for two years at my college. All I am interested in is keeping track of the grades of pupils in the department that I manage (Science). Let me have a better think about...

Thank you for your detailed answer. I will try and see how to model the relationship between classes and tests. I will make sure I look at the junction fk pairs and enforce the unique constraint. The cycle was to represent the academic year. I wish to be able to access results of tests from...

Hi Larry, The bit that confused me is how the students are assigned to a class. In the class table, one student has 12 entries (all of which are physics classes), but in reality they can only exist in one class at a time. I can later that they only one class in the gradeset table. I can't...

Maybe this is a better design?

Okay, thank you. I think I need to redesign. I had maybe separated things too far. I thought students had many classes and classes having many students was unrelated to students having many assessments. But I guess the assessments are related to the classes because in order to take an...

I did notice I could edit a query of students and student_has_assessment but it became uneditable once I combined with the class table. Maybe I am not understanding access. I would like to select (filter) only one class of students from a large number of students and edit just their marks. In...

Thank you Tom. I was rather hoping for a more spreadsheet like look. I query students from a single class and get their test data and only want to edit their marks in a big list rather than moving through each record at a time, the rest of the data is read only. Maybe it is not possible and I...

Just playing with putting together a UI for a student database (I have been helped tremendously so far on the design of the tables and relationships on another thread in the tables forum). I am now looking at the ui for entering and editing data. I thought I could run a query and edit the data...

The two approaches will lead to the same end result. The use of junction tables for student_class or student_grade seems to be the standard model on introductory websites on relational db. Larry's approach seems very different and uses fewer tables and relationships to achieve the same end...