query help for filtering data

[faiza1808]

ummm I'm making a database which will monitor progress of students
therefore i have a field for the 'group' e.g group 1, group 2, group 3
what I'm trying to make is a filter query where I will select the 'group' and a list of people in the certain group selected will appear only in a list form under the 'name' field
e.g.

group 1 is selected
under the 'name' field a list will open with the names of students in group 1

so how do I do it?
should I make a table with the student names and groups separately?
what will I require?

I'd really appreciate the help... been trying to solve this query for the past week now

 

[Kafrin]

Can students be in more than one group? Or can they change groups but you'll want to see historically what groups they've been in? If either of these is true then you should have one table for Students, one table for Groups and one table that links to both students and groups (StudentGroupMap).

If students are only ever in one group at a time (and you don't care where they used to be if they change groups) then you just need two tables, Students and Groups, with a field for the group (linking to Groups) on the Students table.

So you have a form (MyForm) with an unbound control on it that is a dropdown of Groups (I'll call this control ChooseGroup). In the first case, base your query on StudentGroupsMap, in the second case base your query on Students. In both cases you need a field in your query that looks at ChooseGroup.

This option will require you to choose a group, otherwise nothing will appear:
Put GroupID in the top line (this is the field on Students/StudentsGroupsMap that links to the Groups table), in the criteria line put Forms!MyForm!ChooseGroup

This option will show all records if you leave ChooseGroup blank, or will only show the records for the group if you select something in ChooseGroup:
In the top line put Iif(IsNull(Forms!MyForm!ChooseGroup),True,GroupID=Forms!MyForm!ChooseGroup), and int he criteria line put <>False

 

[faiza1808]

Umm groups can change. You said to make a student and group table. I've done it, the relationships look like this I'm really bad at the relationships part. I never really understood it. but are the fields ok are do I need to alter?
and how do I make a relationship, it wont let me make a one to many relationship between the ID and form in all tables?

(just add the w w w. it wont let me put in links
.fileswap.com/dl/I7Egsds/prnt_1.bmp.htm

anyways after the sorting of the tables. I make a form out of the tables right?
then I apply the rest?


thanks for the help earlier
it was very much appreciated

 

[Kafrin]

OK, firstly some naming conventions. It's a really bad idea to give tables or fields the same names as recognised Access commands. From your picture I'd recommend changing the following names:
Form
Name
Record
Date
(also avoid Group, Report, Table and Value)

Then we need to sort out your relationships. You don't currently have any relatinships, but without them a database is kinda pointless. It's not easy to get your head round them to start with, but it is well worth it.

I thought I'd try recreating your tables and putting in the relationships, but I found I couldn't work out what the tables were telling me. So ideally I need you to explain what information you have and what you want...

So, is Form a form group? I get then that teachers take form groups and teachers teach classes. Is Record supposed to be the class? If so, why does it have a field for Form? What's Full Name - the class name, the student name or something else?

Basically I need you to tell me what each table is for, what information is it supposed to store.

 

[faiza1808]

alright, well um yes form is the form group, the teachers are the teaching staff. The students tables is the names of the students with the form classes, The students can change groups if the teacher feels they need to go a level up or down

the record table is the main table. I'm trying to make something that will help me manage my records better. It is basically a detention record because I deal with behaviour issues in the school.

the whole system is manual and therefore I want to computerize it because of too much paperwork

the fields in the table are:

form-the group the student belongs to
Name-name of student in detention
Reason- Reason for detention
Teacher - teacher who has given the detention
Date - date the detention will take place
letter given- a letter has to be given to the student with 2 day notice, so i need this field to make sure the letter is given
Duration- duration of detention (theres 2 slots 30 mins and 60 mins)
attendance- this is to track if student has attended so I can chase it up later
reason of absence- reason for absence to see if its applicable or not



after i have all this sorted then I need make this into a form, so that its easy to search for different aspects e.g search a certain date and students who did not attend detention. I want to implement these through buttons on the forms, which will link with each other.

Because there are around 300 students i need to do this for I want have the database set so I can select the group and in the name section only that group will appear


I hope that helps...

and thank you for the help

 

[Kafrin]

Hi, sorry I disappeared for a bit, life's been hectic!

Attached is my recreation of your database, with the relationships in place. Have a look at this and see if the relationships make any sense to you.

I'll get back to you about your original question in a couple of days - I haven't had time today to get on to that bit.