By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,963 Members | 1,841 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,963 IT Pros & Developers. It's quick & easy.

Extra page with replicated controls on an Access 2007 report

P: 14
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.
Jun 13 '14 #1

✓ answered by twinnyfo

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

Expand|Select|Wrap|Line Numbers
  1. SELECT Students.*, AcademicInfos.*,
  2. [Firstname]+"  "+[Lastname] AS StudentName,
  3. ([monthlyCivics]+[midtermCivics]+[terminalCivics])/3 AS Civics,
  4. ...
  5. FROM Students INNER JOIN AcademicInfos ON Students.StudentID = AcademicInfos.StudentName
  6. 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....

Share this Question
Share on Google+
11 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,488
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.
Jun 13 '14 #2

P: 14
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.
Jun 13 '14 #3

twinnyfo
Expert Mod 2.5K+
P: 3,488
Could you send a screen shot of your result? Very strange that you should show the controls but no data. Thanks for your patience.
Jun 13 '14 #4

P: 14
Here it is:
1.Desired



2.Unwanted extra



3.With no data

Jun 13 '14 #5

twinnyfo
Expert Mod 2.5K+
P: 3,488
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!
Jun 13 '14 #6

P: 14
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
Expand|Select|Wrap|Line Numbers
  1. SELECT StudentByAcademicsQR1.*, 
  2. AcademicInfos.monthlyCivics, 
  3. AcademicInfos.monthlyBiology, AcademicInfos.monthlyMathematics, AcademicInfos.monthlyEnglish, 
  4. AcademicInfos.monthlyKiswahili, AcademicInfos.monthlyAccounting, AcademicInfos.monthlyGeography, 
  5. AcademicInfos.monthlyHistory, 
  6. AcademicInfos.monthlyPhysics, 
  7. AcademicInfos.monthlyChemistry, 
  8. AcademicInfos.monthlyDivinity, 
  9. AcademicInfos.monthlyCommerce, 
  10. AcademicInfos.[monthlyBook-keeping], AcademicInfos.monthlyAgriculture, AcademicInfos.monthlyComputer, 
  11. AcademicInfos.midtermCivics, 
  12. AcademicInfos.midtermBiology, AcademicInfos.midtermMathematics, AcademicInfos.midtermEnglish, 
  13. AcademicInfos.midtermKiswahili, AcademicInfos.midtermAccounting, AcademicInfos.midtermGeography, 
  14. AcademicInfos.midtermHistory, 
  15. AcademicInfos.midtermPhysics, 
  16. AcademicInfos.midtermChemistry, 
  17. AcademicInfos.midtermDivinity, 
  18. AcademicInfos.midtermCommerce, 
  19. AcademicInfos.[midtermBook-keeping], AcademicInfos.midtermAgriculture, AcademicInfos.midtermComputer, 
  20. AcademicInfos.terminalCivics, 
  21. AcademicInfos.terminalBiology, AcademicInfos.terminalMathematics, AcademicInfos.terminalEnglish, AcademicInfos.terminalKiswahili, AcademicInfos.terminalAccounting, AcademicInfos.terminalGeography, AcademicInfos.terminalHistory, 
  22. AcademicInfos.terminalPhysics, AcademicInfos.terminalChemistry, AcademicInfos.terminalDivinity, AcademicInfos.terminalCommerce, 
  23. AcademicInfos.[terminalBook-keeping], AcademicInfos.terminalAgriculture, AcademicInfos.terminalComputer
  24. FROM AcademicInfos, StudentByAcademicsQR1;
  25.  
  26.  
Query StudentByAcademicsQR1
Expand|Select|Wrap|Line Numbers
  1. SELECT Students.StudentID, 
  2. Students.Firstname, 
  3. Students.Middlename, 
  4. Students.Lastname, 
  5. [Firstname]+"  "+[Lastname] AS StudentName, 
  6. Students.Sex, 
  7. Students.Class, 
  8. AcademicInfos.AcademicYear, 
  9. AcademicInfos.Term, 
  10. ([monthlyCivics]+[midtermCivics]+[terminalCivics])/3 AS Civics, 
  11. ([monthlyBiology]+[midtermBiology]+[terminalBiology])/3 AS Biology, 
  12. ([monthlyMathematics]+[midtermMathematics]+[terminalMathematics])/3 AS Maths, 
  13. ([monthlyEnglish]+[midtermEnglish]+[terminalEnglish])/3 AS English, 
  14. ([monthlyKiswahili]+[midtermKiswahili]+[terminalKiswahili])/3 AS Kiswahili, 
  15. ([monthlyAccounting]+[midtermAccounting]+[terminalAccounting])/3 AS Accounting, 
  16. ([monthlyGeography]+[midtermGeography]+[terminalGeography])/3 AS Geography, 
  17. ([monthlyHistory]+[midtermHistory]+[terminalHistory])/3 AS History, 
  18. ([monthlyPhysics]+[midtermPhysics]+[terminalPhysics])/3 AS Physics, 
  19. ([monthlyChemistry]+[midtermChemistry]+[terminalChemistry])/3 AS Chemistry, 
  20. ([monthlyDivinity]+[midtermDivinity]+[terminalDivinity])/3 AS Divinity, 
  21. ([monthlyCommerce]+[midtermCommerce]+[terminalCommerce])/3 AS Commerce, 
  22. ([monthlyBook-keeping]+[midtermBook-keeping]+[terminalBook-keeping])/3 AS [Book-keeping], 
  23. ([monthlyAgriculture]+[midtermAgriculture]+[terminalAgriculture])/3 AS Agriculture, 
  24. ([monthlyComputer]+[midtermComputer]+[terminalComputer])/3 AS Computer, 
  25. ([Civics]+[Biology]+[Maths]+[English]+[Kiswahili]+[Accounting]+[Geography]+[History]+[Physics]+[Chemistry]+[Divinity]+[Commerce]+[Book-keeping]+[Agriculture]+[Computer])/15 AS Average
  26. FROM Students INNER JOIN AcademicInfos ON Students.StudentID = AcademicInfos.StudentName
  27. 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?]));
  28.  
  29.  
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.
Jun 13 '14 #7

twinnyfo
Expert Mod 2.5K+
P: 3,488
In your StudentByAcademicsQR1 query, why not do it this way:

Expand|Select|Wrap|Line Numbers
  1. SELECT Students.*, AcademicInfos.*,
  2. [Firstname]+"  "+[Lastname] AS StudentName,
  3. ([monthlyCivics]+[midtermCivics]+[terminalCivics])/3 AS Civics,
  4. ...
  5. FROM Students INNER JOIN AcademicInfos ON Students.StudentID = AcademicInfos.StudentName
  6. 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....
Jun 13 '14 #8

P: 14
Ok, thanks. I will work on it and let you know.
Jun 13 '14 #9

P: 14
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.
Jun 13 '14 #10

twinnyfo
Expert Mod 2.5K+
P: 3,488
I'm so glad I could help! Now, since it's getting late for you, please get some rest and enjoy your family!
Jun 13 '14 #11

zmbd
Expert Mod 5K+
P: 5,397

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.
Jun 13 '14 #12

Post your reply

Sign in to post your reply or Sign up for a free account.