Unanswered: how to select multiple entries from one table
i m designing examination database...have created subjects table....no i want to select multiple subjects from subject table on sing form for a student n have to save foreign key values of subjects in another table....how could i select multiple subjects on a single form using access database on initial level through wizard....
depends on your data model
From what you are syaing I would expect the data model to include a separate table for subjects, students and studentsubjects (3 tables)
the application would use a separate form for subjects and students woith a subform on the students form whihc in effect creates a cross reference between subjects and students. Effectively this implements a many-to-many relationship.
you could use 6 combo boxes on a single form, and say store the subjects in 6 columns in the student ID - I wouldn't reccomned it, but what ever floats your boat, sunbeam..... It doesn't sound like your data model is normalised or sufficently adapatble.
I suppose you current data model has a referential integrity link to the 6 columns in your student record?
you have a potential problem in how you make sure you don't select the same subject more than once for the same student (you could do that with some VBA in the before update event). You could also requery the combo boxes to remove the selected subject from the other boxes.
you have a potential problem if some of the subjects are linked (ie if they study X, then they can study Y & Z, but can't study A,B OR C
I wonder if there are any examples of this sort of problem in someones homework?
I've done some things similar to this before. One unique way is to use a Selection Table. Create another table with a foreign key link on your subjectID in the subjects tblSubjectSelect, this table will contain the subject ID and a true/false field:
fkSubjectID | chkSubject
You build a form based on the Subject Table and this table to show a list of subjects with checkboxes on each record. You can check and uncheck the items you want than have an apply button or on close query the table for the results applied to a studentID.
Another way, which can be tricky is to have 2 listboxes side by side on a form. You select the Subjects in the left list and click a button which requeries the form to show the selections of the list in the right listbox. (for bells and wistles use a double-click event action on the lists to add/remove items from one to the other) I remember using a lot of nested 'If's to make that work.
Keep in mind if you use a list box you will need to write code to loop through the list and capture the selections. If you use a Table to mark selections and are having multi-user access you will need to take care to make sure only one user is editing selections on the same table. Either make that a local table or dynamically create the table when the selection form is opened.
I still think the optimal solution is a separate table storing the details of the sunjects studied with a referential link to both the student AND the subject. If the primary key of this table was the studentID AND the subjectID then it woul be easy to ensure that the same student couldn't study the same subject more than once
Display the students subjects as a sub form to the student ID. Heck you could even use the smae sub form to display which students are studying a selected subject.
In my view, unless you are doing something incredibly complex then if there is not an easy way to do it in Access, without resorting to masses of VBA then you basic table design is supect. This thread on the face of it doesn't seem particularly complex. Unless of course the assignment requires the use of 6 combo boxes on the the one form that is....