Extra page with replicated controls on an Access 2007 report

[MfanyaKitu]

I have got this report which I've designed with a query(having 60+ controls) as its record source. Its a one page report. The problem that I'm facing is that whenever I preview it, I get one extra page with all the controls on the main page(of course with the exception of the report header controls).
I get this result whenever I supply it with the query parameters and when I don't, I get the desired result, but with no data of course. I've been searching on the web for hours with no avail.
Anybody out there with the idea of what am I missing? My gratitude in advance.

[twinnyfo]

Are you by chance forcing a page feed after the report footer or detail section of your report? Is the record source one record or multiple records? Doese the report print the same way or does htis happen just in preview mode? Is this preview in "Print Preview" mode or "Report View"? All these factors may affect how your report appears.

Please provide additional information.

[MfanyaKitu]

No I'm not forcing the page feed after the report footer nor detail section. The record source is the single query that retrieve multiple records based on a single entity(student): the report is a student report card on his/her academic performance which include personal details and scores from three different types of tests on 15 subjects each. It also include 3 unbound textboxes for comments from the teacher and the parent. The report print the same way in both the Report View and Print Preview.
First I thought the problem may be related to report width but I've worked on it with no positive result.
Hope I have provided the additional information you were asking for.

[twinnyfo]

Could you send a screen shot of your result? Very strange that you should show the controls but no data. Thanks for your patience.

[MfanyaKitu]

Here it is:
1.Desired

2.Unwanted extra

3.With no data

[twinnyfo]

MfanyaKitu,

In post #3 above, you say, "The record source is the single query that retrieve multiple records based on a single entity". If there are multiple records being pulled, then the report will (should) produce a page for each record. Each report card (for each student) should be one page, and hence one record, correct?

What might be helpful to me would be to see:

1. Your Query
2. The results your query pulls on one student

Another question: Is it possible to produce a report that includes all students? If so, does the report print two pages for each student or one page with an included blank page at the end? This also might help us trouble shoot your problem.

Hope we can find a solution for this!

[MfanyaKitu]

Correct, each report card (for each student) should be one page, and hence one record; that's what I wanted to mean. Hence to edit my post #3 on that respect; the report is supposed to be a single page(report card) for a single student, and not many pages for many students.

1.My query
Record source

SELECT StudentByAcademicsQR1.*, 
AcademicInfos.monthlyCivics, 
AcademicInfos.monthlyBiology, AcademicInfos.monthlyMathematics, AcademicInfos.monthlyEnglish, 
AcademicInfos.monthlyKiswahili, AcademicInfos.monthlyAccounting, AcademicInfos.monthlyGeography, 
AcademicInfos.monthlyHistory, 
AcademicInfos.monthlyPhysics, 
AcademicInfos.monthlyChemistry, 
AcademicInfos.monthlyDivinity, 
AcademicInfos.monthlyCommerce, 
AcademicInfos.[monthlyBook-keeping], AcademicInfos.monthlyAgriculture, AcademicInfos.monthlyComputer, 
AcademicInfos.midtermCivics, 
AcademicInfos.midtermBiology, AcademicInfos.midtermMathematics, AcademicInfos.midtermEnglish, 
AcademicInfos.midtermKiswahili, AcademicInfos.midtermAccounting, AcademicInfos.midtermGeography, 
AcademicInfos.midtermHistory, 
AcademicInfos.midtermPhysics, 
AcademicInfos.midtermChemistry, 
AcademicInfos.midtermDivinity, 
AcademicInfos.midtermCommerce, 
AcademicInfos.[midtermBook-keeping], AcademicInfos.midtermAgriculture, AcademicInfos.midtermComputer, 
AcademicInfos.terminalCivics, 
AcademicInfos.terminalBiology, AcademicInfos.terminalMathematics, AcademicInfos.terminalEnglish, AcademicInfos.terminalKiswahili, AcademicInfos.terminalAccounting, AcademicInfos.terminalGeography, AcademicInfos.terminalHistory, 
AcademicInfos.terminalPhysics, AcademicInfos.terminalChemistry, AcademicInfos.terminalDivinity, AcademicInfos.terminalCommerce, 
AcademicInfos.[terminalBook-keeping], AcademicInfos.terminalAgriculture, AcademicInfos.terminalComputer
FROM AcademicInfos, StudentByAcademicsQR1;
 
 

Query StudentByAcademicsQR1

SELECT Students.StudentID, 
Students.Firstname, 
Students.Middlename, 
Students.Lastname, 
[Firstname]+"  "+[Lastname] AS StudentName, 
Students.Sex, 
Students.Class, 
AcademicInfos.AcademicYear, 
AcademicInfos.Term, 
([monthlyCivics]+[midtermCivics]+[terminalCivics])/3 AS Civics, 
([monthlyBiology]+[midtermBiology]+[terminalBiology])/3 AS Biology, 
([monthlyMathematics]+[midtermMathematics]+[terminalMathematics])/3 AS Maths, 
([monthlyEnglish]+[midtermEnglish]+[terminalEnglish])/3 AS English, 
([monthlyKiswahili]+[midtermKiswahili]+[terminalKiswahili])/3 AS Kiswahili, 
([monthlyAccounting]+[midtermAccounting]+[terminalAccounting])/3 AS Accounting, 
([monthlyGeography]+[midtermGeography]+[terminalGeography])/3 AS Geography, 
([monthlyHistory]+[midtermHistory]+[terminalHistory])/3 AS History, 
([monthlyPhysics]+[midtermPhysics]+[terminalPhysics])/3 AS Physics, 
([monthlyChemistry]+[midtermChemistry]+[terminalChemistry])/3 AS Chemistry, 
([monthlyDivinity]+[midtermDivinity]+[terminalDivinity])/3 AS Divinity, 
([monthlyCommerce]+[midtermCommerce]+[terminalCommerce])/3 AS Commerce, 
([monthlyBook-keeping]+[midtermBook-keeping]+[terminalBook-keeping])/3 AS [Book-keeping], 
([monthlyAgriculture]+[midtermAgriculture]+[terminalAgriculture])/3 AS Agriculture, 
([monthlyComputer]+[midtermComputer]+[terminalComputer])/3 AS Computer, 
([Civics]+[Biology]+[Maths]+[English]+[Kiswahili]+[Accounting]+[Geography]+[History]+[Physics]+[Chemistry]+[Divinity]+[Commerce]+[Book-keeping]+[Agriculture]+[Computer])/15 AS Average
FROM Students INNER JOIN AcademicInfos ON Students.StudentID = AcademicInfos.StudentName
WHERE (((Students.StudentID)=[Type STUDENT ID]) AND ((Students.Class)=[Type CLASS]) AND ((AcademicInfos.AcademicYear)=[Type Academic Year]) AND ((AcademicInfos.Term)=[Which TERM are you looking for?]));
 
 

2. The screen shot is indeed the result for one student.

On your last question:
I have report that includes all students but I've limit it to show certain records since it is in list format, so I don't think if it will help us here; by the way it doesn't produce a blank page at the end.

[twinnyfo]

In your StudentByAcademicsQR1 query, why not do it this way:

SELECT Students.*, AcademicInfos.*,
[Firstname]+"  "+[Lastname] AS StudentName,
([monthlyCivics]+[midtermCivics]+[terminalCivics])/3 AS Civics,
...
FROM Students INNER JOIN AcademicInfos ON Students.StudentID = AcademicInfos.StudentName
WHERE (((Students.StudentID)=[Type STUDENT ID]) AND ((Students.Class)=[Type CLASS]) AND ((AcademicInfos.AcademicYear)=[Type Academic Year]) AND ((AcademicInfos.Term)=[Which TERM are you looking for?]));

This should gather all your necessary information, making the other Record Source query unnecessary. I htink one of the problems with the Record Source Query is that the Table and Query are not joined in any way. This may be producing the undesired results.

Let me know how this affects your end result....

[MfanyaKitu]

Ok, thanks. I will work on it and let you know.

[MfanyaKitu]

Thank you so much twinnyfo, it has worked at last. As you thought the problem was that Table and Query were not joined in any way, and by applying your proposed solution the desired result were obtained.
Thanks once again.

[twinnyfo]

I'm so glad I could help! Now, since it's getting late for you, please get some rest and enjoy your family!

[zmbd]

MfanyaKitu
Thank you so much twinnyfo, it has worked at last. As you thought the problem was that Table and Query were not joined in any way, and by applying your proposed solution the desired result were obtained.
Thanks once again.

So as not to mis-lead other new members:
Table and Query were not joined in any way

This is not the most correct statement:
You cannot have a query without a table at some point.
All Queries are always joined to a table
(where joined means "to be associated with")
at some point because, the query is, in simplest terms, a filter against the data stored in the table (or multiple tables). If you will, it is a special programming language that tells the database program to go to a data source, and display only those fields from specific records that match the filter requirements.

Now as Twinnyfo has done in post#8 Queries can also have "joins" between other queries and amongst other underlying tables; however, it must be made clear that these types of "joins" are "filtering" in nature... that is to say that these "joins" return data from the multiple record sources based upon mutually agreeing data within the records between the sources.

These "joins" do not associate a query to the table or other record source, the query is just a display of data from the table(s)

There is another form of joining... that is a join between tables in a normalized rational database...

MfanyaKitu, which,
After reviewing the SQL scripts posted in Post#7 I have the impression that your database is in need of normalization as described in > Database Normalization and Table Structures.