merging two queries together

[dammy]

Hi everyone
I am actually working on a school database and am having some issues any help will be greatly appreciated.

I have a query to run students total marks per subject for a term.
E.g
Mary. First Term. English. 62
Mary. Second Term. English.78

For the third term report I want it to read something like this

First Term. Second Term. Third Term
English. 62. 78. 82
Please any help will be appreciated Thanks

 

[CJ_London]

you need to use a crosstab query

Set Name and Subject to group by - rowheading, term to group by - column heading and marks to sum - value

 

[dammy]

Thanks so much for your prompt reply it really saved a lot of headache.
It worked well and am really very grateful
What I have now is Name. Subject, First, second , and Third term.
Is there a way I can do it such dat my Third will show me the test score, exam score and total on the same report
Example
Name subject first term second term. third term
Mary English. 55. 56. 23. 42. 65

Thanks and hope to read from you soon

 

[CJ_London]

You are lucky that First, Second and Third are 'sorted' correctly alphabetically! If you had a Fourth term, that would probably appear between First and Second!

With regards your question, this type of query can only return one value per column however there is a workaround.

1. You need to 'fix' the column headings. You do this by opening the query properties (right click and select properties) then click on the top half of the query builder form and you will see a property called column headings - enter into here:

First Term, Second Term, Third Term

Then save the query and close

2. Create a new query - since this is for the third term only it does not need to be a crosstab - based on the same table as your crosstab

3. Whilst in this query, drag your crosstab onto the top part of the query builder and create joins between the student name fields and the subject fields.

4 Select each of the fields from the crosstab plus the Exam field from the other table

4. if required, reorder the columns to how you want them

5. Add a final column for the total which will look something like:

Total:[First Term] + [Second Term] + [Third Term] + [Exam]

That should be all you require

 

[dammy]

Thanks so much
You really helped me and am very grateful
What I did in the other query was to set the criteria to Like Third* so it brings out only report with scores of third term only plus the summation of First and Second Term on seperate columns

Thanks Thanks Thanks

 

[dammy]

Thanks for the help so far am very happy
Please how do I so it such that I can automatically count the total for the terms a student attended a subject and divide the total marks obtained by the count
Example
Mary attented maths test n exam 2nd and 3rd Tern only so when summing up for the session, I want it to count only two and divide by the total markes generated
Hope to hear from you soon
Thanks

 

[CJ_London]

You can use a query to do that but without knowing what your query looks like it is difficult to advise

 

[dammy]

This is the query I joined with the crostab query
This query is a query from multiple tables
So it is like querying a query

StudentID
StudentName
SubjectID
TermID under the criteria like 'Third term'
SessionID
ClassID
TeacherID
Total
Test1
Test2
Exam
Total

This is what my query looks like, the query I linked to my crosstab query.
I want it to be able to pick Terms and subject which a student attended so that the student third term result will be based on the terms and the subjects with no zero
Thanks and hope to read from you soon

 

[CJ_London]

You need to post the SQL to the query

 

[dammy]

Thanks for your prompt reply



SELECT StudentStatus.StudentName, StudentStatus.GradeExam, StudentStatus.GradeAssignment1, StudentStatus.GradeAssignment2, StudentStatus.GradeAssignment3, StudentStatus.TermID, StudentStatus.SchoolYearID, StudentStatus.Total, StudentStatus.CourseTitle, StudentStatus.txtTerm, StudentStatus.ClassID, StudentStatus.SessionName
FROM StudentStatus
WHERE (((StudentStatus.txtTerm) Like "Third*"));


TRANSFORM Sum(StudentStatus.Total) AS SumOfTotal
SELECT StudentStatus.StudentName, StudentStatus.CourseTitle, StudentStatus.StudentID
FROM StudentStatus
GROUP BY StudentStatus.StudentName, StudentStatus.CourseTitle, StudentStatus.StudentID
PIVOT StudentStatus.txtTerm In ("First Term","Second Term","Third Term");

 

[CJ_London]

So are you saying you have another query which links these two together? If so, what is it? If not, please provide some example data and the result you are looking for - at the moment I do not understand what you are trying to do.

I would expect you to have a query which looks something like

SELECT * 
FROM Qry1 INNER JOIN qry2 
WHERE Qry1.StudentName=Qry2.StudentName AND Qr1.CourseTitle=Qry2.CourseTitle

 

[dammy]

Yes a query links the two together
Yes this is the query that links the two queries together

PARAMETERS N Long;
SELECT StudentStatus_Crosstab.StudentName, StudentStatus_Crosstab.CourseTitle, StudentStatus_Crosstab.StudentID, StudentStatus_Crosstab.[First Term], StudentStatus_Crosstab.[Second Term], StudentStatus_Crosstab.[Third Term], ThirdTerm.GradeExam, ThirdTerm.GradeAssignment1, ThirdTerm.GradeAssignment2, ThirdTerm.GradeAssignment3, [First Term]+[Second Term]+[Third Term] AS Total, ThirdTerm.ClassID, ThirdTerm.SessionName, ThirdTerm.SchoolYearID, [Total]/[N] AS [Avg]
FROM ThirdTerm INNER JOIN StudentStatus_Crosstab ON (ThirdTerm.StudentName = StudentStatus_Crosstab.StudentName) AND (ThirdTerm.CourseTitle = StudentStatus_Crosstab.CourseTitle);

Thanks for your prompt response

 

[CJ_London]

OK so from this query you want to calculate the below

Mary attented maths test n exam 2nd and 3rd Tern only so when summing up for the session, I want it to count only two and divide by the total markes generated

is [Total]/[N] AS [Avg] what you want to calculate? - i.e. replace N with a calculated value for the number of terms attended?

 

[dammy]

Yes if that will do the job

What I want it to be able to count the number of terms that mary attended for the each subject and divide by the total.

The easiest would have been to divide total\3. But I realize that some students may join in late or maybe absent due to one reason
So that was why I thought of counting the terms the student attended then divide by th total

Thanks so much for the help
Am very grateful

 

[CJ_London]

OK, What you want is

[Total]/(abs((nz([First Term])<>0)+(nz([Second Term])<>0)+(nz([Third Term])<>0)) AS [AvgTtl]

Note that Avg is a reserved word, so I have changed it

To explain

nz([First Term])<>0)

Assumes that if the candidate did not attend the first term (or missed another term for the other parts of the calculation) then there are no results or the result will be zero so the comparator <>0 will return false (i.e. 0) or true (-1) if they did attend. The abs function then converts the combined (negative) values to positive.

I have not included protection from the divide by zero error because someone who has no scores across three terms should not be appearing anyway.

 

[dammy]

Whao Thanks Thanks Thanks so much for the help
You are a genius
Am really very grateful.
It worked perfectly

I really appreciate your help.

 

[dammy]

Hello
Thanks so much for your help
I have made a lot of progress
What I am having challenge with now is getting the student with the highest score in class.
I mean highest overall score in all the subjects
I want to put that highest score on each student report card.

Thanks and hope to hear from you soon