Solved Yearss (1 Viewer)

Mercy Mercy · Feb 1, 2023

Actually I am defeated. I am looking for a query to show students and classes and streams.

I created sub forms like this.

Look at relationships.

Main form- tblstudents.

Subform1- frmgradeyears.

Subform 2- frmgradestream.

Now my question is
When I create a query it puts a student in all streams.

I don't whether the problem is referential integrity or not?

Kindly assist.

Attached is a database.

MajP · Feb 1, 2023

Your relationships are fine, but your data seems jacked up. What is going on here? You need a unique index on GradeID and StreamID so that there are not multiple
1,1
1,1
1,1
1,2
1,2
1,2
Also Does each grade get every stream?

tblGradeStream tblGradeStream

tblGradeStreamID	GradeID	StreamID
12		1
2	1	2
4	1	3
5	1	1
9	1	2
11	1	3
13	1	1
14	1	1
16	1	1
18	1	1
20	1	1
21	1	1
22	1	1
3	2	2
6	2	4
10	2	4
15	2	3
17	2	1
19	2	2
8	3	3
7	4	2

Mercy Mercy · Feb 1, 2023

Thanks for your advice. Kindly assist me. Please. I am new in access.

Mercy Mercy · Feb 1, 2023

MajP said:
Your relationships are fine, but your data seems jacked up. What is going on here? You need a unique index on GradeID and StreamID so that there are not multiple
1,1
1,1
1,1
1,2
1,2
1,2
Also Does each grade get every stream?

tblGradeStream tblGradeStream

tblGradeStreamID GradeID StreamID
12

1
2
1
2
4
1
3
5
1
1
9
1
2
11
1
3
13
1
1
14
1
1
16
1
1
18
1
1
20
1
1
21
1
1
22
1
1
3
2
2
6
2
4
10
2
4
15
2
3
17
2
1
19
2
2
8
3
3
7
4
2

Let us take for example we have 8 students in grade 1.

Student A should be in grade 1 RED.

Student B should be In grade 1 WEST

Student C should be in Grade 1 NORTH.

Student D should be in grade 1 SOUTH.

Student E should be in grade 1 YELLOW

Student F should be in grade 1 ORANGE

Student G should be in grade 1 GREEN

Student H should be in grade 1 ZEBRA

So in this case we have one grade and 8 streams.
. Therefore we have 8 students in grade 1.

Thanks in advance.

MajP · Feb 1, 2023

Again that data is jacked. You cannot have that table with only one student in the database.
Provide a useable set of test data and your query.

My mistake. But that is confusing.
Answer the question. Can a student be in more than one stream?
If only one stream per student per grade your one table should include
StudentId_fk
Gradeid_fk
Streamid_fk
And index these as a unique if multiple streàms per grade per student. If only one stream then index student and grade.

mike60smart · Feb 1, 2023

Mercy Mercy said:
Let us take for example we have 8 students in grade 1.

Student A should be in grade 1 RED.

Student B should be In grade 1 WEST

Student C should be in Grade 1 NORTH.

Student D should be in grade 1 SOUTH.

Student E should be in grade 1 YELLOW

Student F should be in grade 1 ORANGE

Student G should be in grade 1 GREEN

Student H should be in grade 1 ZEBRA

So in this case we have one grade and 8 streams.
. Therefore we have 8 students in grade 1.

Thanks in advance.

Take a look at the attached.

The query1 produces the output you require.

Mercy Mercy · Feb 1, 2023

mike60smart said:
Take a look at the attached.

The query1 produces the output you require.

Genius! Exactly what I needed. Thanks.

Mercy Mercy · Feb 1, 2023

@mike60smart I am requesting you to look at the attached database and assist me come up with referential integrity between students and teachers. Assist me with query to show for example in year 2023, teacher A is a class teacher for grade 1 RED for example.

I am humbly requesting you. Please. Please. Please.

mike60smart · Feb 1, 2023

Hi

I think you need to rethink your table relationships.

In a specific Year I take it you have a number of Streams, with each Stream comprising a number of Students.

Is this the case?

Mercy Mercy · Feb 1, 2023

mike60smart said:
Hi

I think you need to rethink your table relationships.

In a specific Year I take it you have a number of Streams, with each Stream comprising a number of Students.

Is this the case?

True. Example 2023
Grade 1 red- 37 students
Grade 1 green- 40 STUDENT.
Grade 1 ORANGE- 54 students
Grade 1 north- 43 students

We also have teachers teaching those classes

mike60smart · Feb 1, 2023

In a specific Year you have a number of Terms.

So in a Specific Year for Each Term there are a number of Streams with a number of Students in Each Stream

Mercy Mercy · Feb 1, 2023

Yes. And in each term different EXAMINATIONS are done for several subjects
Year- 2023
Grade 1- grade 1 RED
Grade 1 YELLOW
Grade 1 north
Term - term 1
Examination- WEEKLY EXAMS.
SUBJECTS.

TEACHERS.

Mercy Mercy · Feb 1, 2023

Examinations are done weekly
Week 1
Week 2
Week 3.
Week 4

mike60smart · Feb 1, 2023

Would it be better to say:-

In a specific Year there are a Number of Terms.
Each term has a Number of Grades (Does grades equate to Kindergarden, Primary , Secondary??)
Each Grade has a Number of Students with each Student in a specific Stream

Mercy Mercy · Feb 1, 2023

mike60smart said:
Would it be better to say:-

In a specific Year there are a Number of Terms.
Each term has a Number of Grades (Does grades equate to Kindergarden, Primary , Secondary??)
Each Grade has a Number of Students with each Student in a specific Stream

Correct

Mercy Mercy · Feb 1, 2023

We have 4 terms. In each term we have grade 1 to grade 6.

Mercy Mercy · Feb 1, 2023

In this grade 1 to grade 6 we have 8 streams for each grade

Pat Hartman · Feb 1, 2023

You need to remove StudentID from tblGradeYears. tblGradeYears defines the universe of your grade structure. Then you need a junction table to connect students to grade years. The junction table would be named tblStudentGradeYears. It would contain an autonumber PK and the two FKs StudentID and YearGradeID. Then you need a unique index on the two PKs. Open the indexes dialog. On the first blank line add a name for the index name. It must be unique. Then pick the first field - StudentID and set the index to be unique. Go to the next line. Skip the index name field and pick the second field - YearGradeID. Leaving the index name blank is the way you tell Access that the two fields are part of a multi-field index. in tblGradeYears, you also need a unique index. on the three fields - GradeYearID,GradeID, and StreamID.

You also have a different problem with indexes on tblGradeYears. You have created indexes on GradeID, GradeYearID,StudentID, and StreamID. ALL of these indexes MUST be deleted. All of these fields are foreign keys to some other table. When you define relationships, Access automagically creates HIDDEN indexes on every FK. Therefore you have duplicated the indexes and this doubles the work to manage the indexes and also has the potential for corruption so just get rid of them. Leave the PK but remove the other four indexes.

Mercy Mercy · Feb 2, 2023

Pat Hartman said:
You need to remove StudentID from tblGradeYears. tblGradeYears defines the universe of your grade structure. Then you need a junction table to connect students to grade years. The junction table would be named tblStudentGradeYears. It would contain an autonumber PK and the two FKs StudentID and YearGradeID. Then you need a unique index on the two PKs. Open the indexes dialog. On the first blank line add a name for the index name. It must be unique. Then pick the first field - StudentID and set the index to be unique. Go to the next line. Skip the index name field and pick the second field - YearGradeID. Leaving the index name blank is the way you tell Access that the two fields are part of a multi-field index. in tblGradeYears, you also need a unique index. on the three fields - GradeYearID,GradeID, and StreamID.

You also have a different problem with indexes on tblGradeYears. You have created indexes on GradeID, GradeYearID,StudentID, and StreamID. ALL of these indexes MUST be deleted. All of these fields are foreign keys to some other table. When you define relationships, Access automagically creates HIDDEN indexes on every FK. Therefore you have duplicated the indexes and this doubles the work to manage the indexes and also has the potential for corruption so just get rid of them. Leave the PK but remove the other four indexes.

I am now confused more than before.

Mercy Mercy · Feb 2, 2023

I guess what is left is now me, myself and I alone. Thanks

tblGradeStreamID	GradeID	StreamID
12		1
2	1	2
4	1	3
5	1	1
9	1	2
11	1	3
13	1	1
14	1	1
16	1	1
18	1	1
20	1	1
21	1	1
22	1	1
3	2	2
6	2	4
10	2	4
15	2	3
17	2	1
19	2	2
8	3	3
7	4	2

tblGradeStreamID	GradeID	StreamID
12		1
2	1	2
4	1	3
5	1	1
9	1	2
11	1	3
13	1	1
14	1	1
16	1	1
18	1	1
20	1	1
21	1	1
22	1	1
3	2	2
6	2	4
10	2	4
15	2	3
17	2	1
19	2	2
8	3	3
7	4	2

tblGradeStreamID	GradeID	StreamID
12		1
2	1	2
4	1	3
5	1	1
9	1	2
11	1	3
13	1	1
14	1	1
16	1	1
18	1	1
20	1	1
21	1	1
22	1	1
3	2	2
6	2	4
10	2	4
15	2	3
17	2	1
19	2	2
8	3	3
7	4	2

tblGradeStreamID	GradeID	StreamID
12		1
2	1	2
4	1	3
5	1	1
9	1	2
11	1	3
13	1	1
14	1	1
16	1	1
18	1	1
20	1	1
21	1	1
22	1	1
3	2	2
6	2	4
10	2	4
15	2	3
17	2	1
19	2	2
8	3	3
7	4	2

Solved Yearss (1 Viewer)

Member

Attachments

You've got your good things, and you've got mine.

Member

Member

You've got your good things, and you've got mine.

Registered User.

Attachments

Member

Member

Attachments

Registered User.

Member

Registered User.

Member

Member

Registered User.

Member

Member

Member

Super Moderator

Member

Member

Similar threads

Users who are viewing this thread

tblGradeStreamID	GradeID	StreamID
12		1
2	1	2
4	1	3
5	1	1
9	1	2
11	1	3
13	1	1
14	1	1
16	1	1
18	1	1
20	1	1
21	1	1
22	1	1
3	2	2
6	2	4
10	2	4
15	2	3
17	2	1
19	2	2
8	3	3
7	4	2

tblGradeStreamID	GradeID	StreamID
12		1
2	1	2
4	1	3
5	1	1
9	1	2
11	1	3
13	1	1
14	1	1
16	1	1
18	1	1
20	1	1
21	1	1
22	1	1
3	2	2
6	2	4
10	2	4
15	2	3
17	2	1
19	2	2
8	3	3
7	4	2