Query Help!

ezio

New member
Local time
Today, 12:17
Joined
Oct 24, 2011
Messages
3
I've been stuck on this for hours! There are 3 tables in the database Students, Units, and Enrolment

Student table

STUDENT%20TABLE.PNG


Unit Table

UNIT%20TABLE.PNG


Enrolment table

ENROLMENT%20TABLE.PNG


I need to use "expressions builder" to write a query to:


Show the total tuition fee for each Student enrolled (credit points * credit point fee) along with the student’s information (StudentID, Student First name and Family name).

Please help!
 
Please show us the query you tried, and what error/issue you are having exactly.
 
i tried this in expression builder to find the total tuition fee of each student

SUM([UNITS]![Credit Points]*[UNITS]![Credit Point Fee])

but ACCESS gives me the error "you tried to execute a query that does not include the specified expression "StudentID" as part of an aggregate function"

Heres the SQL code I tried:

SELECT STUDENTS.StudentID, STUDENTS.[First Name], STUDENTS.[Family Name], Sum([UNITS]![Credit Points]*[UNITS]![Credit Point Fee]) AS [Each Unit Total]
FROM STUDENTS, UNITS;
 
I see two issues to deal with right up front:
  1. Your final query will require information from all three of the Tables, and onf of the Tables is missing.
  2. There is no apparent relationship between The Students Table and the Units Table. The Enrollments Table, however has a relationship with both the Students Table and the Units Table. Adding this table to the picture should clear up a lot of problems.
 
Hi Ezio,

That should be an easy one.

First you need to join all 3 tables in a select query

[Student].[Student ID] joining [Enrolment].[StudentID]

and

[Enrolment].[Unit Code] joining [Unit].[Unit Code]

Once you have them in a chain, drag the Student ID, Name, family Name down to the query grid.

Do not drag any field from the Enrolment or UNITS table.

Create the subtotal expression as a column that you have done correctly:
Each Unit Total:[UNITS]![Credit Points]*[UNITS]![Credit Point Fee]

Now press the Sigma (Sum) button, but make sure the "Each Unit Total" column is "Sum" not "Group By". The others should be left as "Group By"

Now you should have the total for each student.
 
Hi Ezio,

That should be an easy one.

First you need to join all 3 tables in a select query

[Student].[Student ID] joining [Enrolment].[StudentID]

and

[Enrolment].[Unit Code] joining [Unit].[Unit Code]

Once you have them in a chain, drag the Student ID, Name, family Name down to the query grid.

Do not drag any field from the Enrolment or UNITS table.

Create the subtotal expression as a column that you have done correctly:
Each Unit Total:[UNITS]![Credit Points]*[UNITS]![Credit Point Fee]

Now press the Sigma (Sum) button, but make sure the "Each Unit Total" column is "Sum" not "Group By". The others should be left as "Group By"

Now you should have the total for each student.

Hey that solved it! Yaay! the problem was I didnt press the sigma button(totals) in access. Thanks a lot! And to everyone else here as well :)
 
Be careful of what looks too easy. There could be trick data in your Tables, and as a result, the information for the student named Tania Landro (Student ID 40000004) may wind up not being correct. If you take a good look, her courses are not all in the same year like the rest of the students, and are spread over three years (2009, 2010, and 2011). If you sum them all up, you will not get the correct answer if it is supposed to be by year. Make sure that this is not important.
 

Users who are viewing this thread

Back
Top Bottom