Combining two queries or two different table on a same report .

  • Thread starter Thread starter sha
  • Start date Start date
S

sha

I have an access database that holds student exam results and I want to
create a report that would allow me to group/sort student exam results using
their second year results and followed by their 3rd year result. I am able
to do this
without a problem each year alone and print this on 1 page for each student.


But I want to do the same for the 3rd year results. So I will have same
student's 2nd year 3rd year results calculated on the same page, but show
the total for each year separately and do the subtotal for the 2nd and 3rd
year ( I want to achieve something similar to the example shown below) . I
can't seem to do this. Any help is welcome; please keep the instruction
simple, as I am new to access.
Many thanks,

Sha,uk



StudeID Subjects Year Mark
10025 Law1 2nd Year 101

10025 maths 2nd Year 100

10025 art 2nd Year 109

10025 RE 2nd Year 100

10011 law1 2nd Year 95

Total: 505
I can sort the records using filter studentID and do a total E.g:

StudeID Subjects Year Mark
10025 Law1 3rdYear 101

10025 maths 3rdYear 100

10025 art 3rdYear 109

10025 RE 3rdYear 100

Total Mark
410


Subtotal: 915
 
sha said:
I have an access database that holds student exam results and I want to
create a report that would allow me to group/sort student exam results using
their second year results and followed by their 3rd year result. I am able
to do this
without a problem each year alone and print this on 1 page for each student.


But I want to do the same for the 3rd year results. So I will have same
student's 2nd year 3rd year results calculated on the same page, but show
the total for each year separately and do the subtotal for the 2nd and 3rd
year ( I want to achieve something similar to the example shown below) . I
can't seem to do this. Any help is welcome; please keep the instruction
simple, as I am new to access.

StudeID Subjects Year Mark
10025 Law1 2nd Year 101

10025 maths 2nd Year 100

10025 art 2nd Year 109

10025 RE 2nd Year 100

10011 law1 2nd Year 95

Total: 505
I can sort the records using filter studentID and do a total E.g:

StudeID Subjects Year Mark
10025 Law1 3rdYear 101

10025 maths 3rdYear 100

10025 art 3rdYear 109

10025 RE 3rdYear 100

Total Mark
410

Subtotal: 915
Click to expand...


This is very simple to do by using Sorting and Grouping to
group first on StudentID and secondly on Year. Then groups
can each have thier own footer section which can calculate
the respecitive totals using a text box with an expression
like =Sum(Mark)

The subject of your question bothers me though. Are you
implying that you have a separate table for each year? If
so, this is not a proper relational database design and
should be corrected before you get too much farther down the
road. Generally, you should have a single table (with a
field for the year) for this kind of information. You'll
have to explain this part of your situation in more detail
before anyone can offer specific suggestions on how to deal
with this.
 
Thanks for the info, but it still does not solve my problem. Please do
excuse my inability to explain exact nature of my problem. But let me try
again. I have all the data on a database with the following table ( StudeID
Subjects , Year, Mark -this is not a relational database).





I want to sort records by StudentID and Year ( and do a total for the 2nd
year exam) And do the same for the 3rd exam. Then sub total for 2nd and 3d
year and print it out on single page showing their result for both years.

Currently I can only group them by StudentID , but can't divide them into
separate two year- instead it list all the results in a liner order. I want
achive something similar like this :



StudeID Subjects Year Mark
10025 Law1 2nd Year 101
10025 maths 2nd Year 100

Total 2nd year
:201


10025 maths 3rd Year 100

10025 Law1 3rd Year 120

Total 3rd
year: 220



Total 2nd + 3rd year:421



Many thanks in advance for your help and patience.
 
Hi Sha.
I don't want to interrupt, but may I can help.
You have four columns in your table:
StudeID; Subjects; Year; Mark
When you create the report Group first by StudeID than by
Year and than Sort by Subject.
If you than place the textboxes in the detail like:
StudeID - Year - Subjects - Mark
and set the hide duplicates for the StudeID and Year to
yes, than you will end up with a report like

Student1 2001 Subject1 3.6
Subject2 3.8
Subject3 4.0
2002 Subject1 3.2
Subject2 3.0
Subject3 3.9
etc. etc.
I think that is what you want.
Hope this helps.
Fons
 
sha said:
Thanks for the info, but it still does not solve my problem. Please do
excuse my inability to explain exact nature of my problem. But let me try
again. I have all the data on a database with the following table ( StudeID
Subjects , Year, Mark -this is not a relational database).
Click to expand...

But Access is a relational db tool. so it helps if the data
is at least somewhat normalized. On the other hand, at
least this table appears to have all the data needed by the
report, so we can ignore this issue for now.

I want to sort records by StudentID and Year ( and do a total for the 2nd
year exam) And do the same for the 3rd exam. Then sub total for 2nd and 3d
year and print it out on single page showing their result for both years.

Currently I can only group them by StudentID , but can't divide them into
separate two year- instead it list all the results in a liner order. I want
achive something similar like this :

StudeID Subjects Year Mark
10025 Law1 2nd Year 101
10025 maths 2nd Year 100
Total 2nd year: 201


10025 maths 3rd Year 100
10025 Law1 3rd Year 120
Total 3rd year: 220
Total 2nd + 3rd year:421
Click to expand...

This is what I addressed before, but you didn't add the
second level of grouping on the year field. Maybe I didn't
get the point across clearly, but I'm pretty sure this is
what you need to do. (Actually, as the Fons suggested, you
may want a third entry in Sorting and Grouping, without
header or footer, to sort on the Subject field.)
--
Marsh
MVP [MS Access]


Many thanks in advance for your help and patience.
Click to expand...
 
Your star! you saved many hours of work.
I think I'm on the right track now.

Many Thanks.
sha,uk


Marshall Barton said:
sha said:
Thanks for the info, but it still does not solve my problem. Please do
excuse my inability to explain exact nature of my problem. But let me try
again. I have all the data on a database with the following table ( StudeID
Subjects , Year, Mark -this is not a relational database).
Click to expand...

But Access is a relational db tool. so it helps if the data
is at least somewhat normalized. On the other hand, at
least this table appears to have all the data needed by the
report, so we can ignore this issue for now.

I want to sort records by StudentID and Year ( and do a total for the 2nd
year exam) And do the same for the 3rd exam. Then sub total for 2nd and 3d
year and print it out on single page showing their result for both years.

Currently I can only group them by StudentID , but can't divide them into
separate two year- instead it list all the results in a liner order. I want
achive something similar like this :

StudeID Subjects Year Mark
10025 Law1 2nd Year 101
10025 maths 2nd Year 100
Total 2nd year: 201


10025 maths 3rd Year 100
10025 Law1 3rd Year 120
Total 3rd year: 220
Total 2nd + 3rd year:421
Click to expand...

This is what I addressed before, but you didn't add the
second level of grouping on the year field. Maybe I didn't
get the point across clearly, but I'm pretty sure this is
what you need to do. (Actually, as the Fons suggested, you
may want a third entry in Sorting and Grouping, without
header or footer, to sort on the Subject field.)
Click to expand...
 
Your star! you saved many hours of work.
I think I'm on the right track now.

Many Thanks.
sha,uk






Marshall Barton said:
sha said:
Thanks for the info, but it still does not solve my problem. Please do
excuse my inability to explain exact nature of my problem. But let me try
again. I have all the data on a database with the following table ( StudeID
Subjects , Year, Mark -this is not a relational database).
Click to expand...

But Access is a relational db tool. so it helps if the data
is at least somewhat normalized. On the other hand, at
least this table appears to have all the data needed by the
report, so we can ignore this issue for now.

I want to sort records by StudentID and Year ( and do a total for the 2nd
year exam) And do the same for the 3rd exam. Then sub total for 2nd and 3d
year and print it out on single page showing their result for both years.

Currently I can only group them by StudentID , but can't divide them into
separate two year- instead it list all the results in a liner order. I want
achive something similar like this :

StudeID Subjects Year Mark
10025 Law1 2nd Year 101
10025 maths 2nd Year 100
Total 2nd year: 201


10025 maths 3rd Year 100
10025 Law1 3rd Year 120
Total 3rd year: 220
Total 2nd + 3rd year:421
Click to expand...

This is what I addressed before, but you didn't add the
second level of grouping on the year field. Maybe I didn't
get the point across clearly, but I'm pretty sure this is
what you need to do. (Actually, as the Fons suggested, you
may want a third entry in Sorting and Grouping, without
header or footer, to sort on the Subject field.)
Click to expand...
 
Back
Top