Query not returning all values

[whitelexi]

I'm still learning access, working with access 2007 on windows 8 64bit system. I've recently been building a school database which is designed to store students info, result info, fees info, and other stuff like lecturers and departments etc.



I've created data in excel sheets and uploaded them successfully into the tables created and linked.
I'm now at the testing stage and I realise that my queries are not giving the complete info. For instance: I run a query intended to show me what courses make up a particular degree program, and I get only the year 1 courses instead of the entire 4 year courses.

I've come too far to start taking this db apart piece by piece in order to locate the issue... Is there anyone with some idea about what I may be doing wrong?

[orange]

It would be helpful to readers if you would post a jpg of your tables and relationships.

[whitelexi]

Thanks, I've tried to squeeze it into one screenshot... I hope you can make out the relationships.

[burrina]

Can you pot a screenshot of the query? Explaining what is supposed with what fields and when.

Thanks,

[whitelexi]

Okay, notice that the SessionID field in the screenshot of the ProgramPlanCourses table above has both 1st year, 2nd year and 3rd year courses where departmentID= 24... However if i create a new query to find all courses offered in this department, i'll only get 1st year courses in the query result as seen below:


Here's the result of the query:

[whitelexi]

Please ignore the very last screenshot, i totally forgot i had attached it...

[burrina]

You did not give it any criteria for the year? Plus, we could not see a screenshot of the first qry in design mode.

[whitelexi]

The first screenshot is the ProgramPlanCourses table, not a query. The other 2 screenshots show the query and its result.
From your response I understand that I could indicate a specification for the session years, but I don't know how to go about doing that. The contents of the Sessions table is simply 1, 2, 3, 4, and 5 which represents year1 to year5.

I'd be glad to try anything you want me to.

[ssanfu]

In the second pic of post #5 (query designer), I would try deleting the link and table "StudentsDeg" and deleting the link between "Sessions' and "ProgramPlanCourses".


My $0.02.....

[Parsonshere]

I am not sure, but looking at the table "sessions" in the query screenshot, it looks like Session ID is Primary Key.
Does the same Session ID exist in each year.
If so, this may be the problem, if the Session ID and Year should both form the PK.

[Parsonshere]

There is really three tables to be queried, Sessions, StudentsDeg &
ProgramPlanCourses.
The rest are selection criteria and can be removed from the query,
but table restructure is in order for all tables.
Remove all ID fields except for the Primary Key ID field in all the
other tables, and make them all about their topic,
Semesters only needs the ID and the Semester Name,
Departments has dept. ID and describe the various departments.
ProgramsPlans should just have the ID and ProgramPlan.
StudentsDeg table should contain only students information, which
may includes some ID fields that define that student.
The only relationship field the table "studentsDeg" has is
ProgramPlanCourseID to ProgramPlanCourses.
Are the semesters not year specific? If so, put the year there
instead of in the Sessions table, and replace Sessions table with
the Semesters table in the query.
In the ProgramPlanCourses table, add all the ID fields that exist in
the query that are currently missing.
This may not be exactly how the restructure needs to be, but you get
the idea. You don't need any fields duplicated in selection
criteria tables except for the key fields.

[whitelexi]

I find your response very interesting, I started this project with just 5 tables but then I was advised to look at normalization and before I knew what was happening I had over 20 tables.

I will get back with results within 24hrs... From what you've said I understand that the following needs to be done:
1) the SessionYear needs to be changed into a year and not just a number.
2) I need to remove duplication of ID fields in some tables.
3) I need to merge the Semesters and Sessions tables into one single table.
4) I need to restructure the table and relationships structure in a way that will still ensure functionality.

I'm getting to work right away.

[whitelexi]

Quick question: is it possible to use the date/time data type to represent only the year. I tried changing the format to short date but Access is demanding a full date format and not just the year. Another way of going round this is to use the number data type and manually type each year in as a number... I can get Access to accept that but I'll like to know if its possible to use the date data type to represent only the year.