I have a from based on a query that groups students taking the same exams taken in a specific rooms, however I cannot include the primary key as this is unique to each exam. I also have a subform showing the invigilators allocated to each room. Both main form and subform are linked through the Primary key ID (relationship) which cannot be grouped which brings me a parameter query when running the form, how do I solve this please?
-
-
Hi
This problem is 'solved' by designing the database/relationships correctly!
Your grouped query can return the RoomID, examinationID and/or any day/time period ID as grouped composite primary key (for this query). The invigilator (are they defined in an Invidilator table?) should be allocated to the room/exam/time (defined in a separate joining table!) which would have the same primary key as indicated above, or somthing similar to this.
I assume the students are asigned to the exam/room/time in a similar way?
More information on the DB design is required to be more specific.
HTH
MTB -
As Mike says, you need to step back and think about what you actually want before you design your forms. This involves consideration of what you want certainly, but also of how you think you will get to it. I suspect, if you need access to the PK (or any individual data) of the records, that grouping in the query itself would not make much sense.
What may make sense, is aggregating (using GROUP BY on) the query for the main form but ensuring the sub-form shows individual records. In this case though, the linking fields could not involve specific record data such as the PK.Comment
-
Although MiketheBike makes some valid points, there are also pitfalls in agregate queries that even with a properly designed database one will fall prey to.
First, as MikeTheBike pointed out, you did not provide any kind of information about the tables and relationships; thus, one can only ASSUME that the database is mis-designed.
So a few links for you to follow:
A Tutorial for Access
Database Normalization and Table Structures.
11.Aggregate Query Woes
Once you've worked thru these then lets us see the tables.Comment
Comment