i cant put it in enrolment table for the reason that pre-school is not included in the grand total... i notice when i put the pre-school data in tblenrol pre-school is also included in the total
so i make a separate table for pre-school enrolment data...
No, you don't want to do that. Tables are intended to represent real world entities. Within the context of your database, pre-school is not it's own separate entity. It's just an attribute of the grade levels entity (in other words, it's just a grade like all the others). When you want to limit or restrict the results you get from a table, you don't split some of the data out into a separate table, you use a
query.
I do think it would be a good idea to add a table for grade levels, to eliminate data entry mistakes and to make sorting the grades easier (because you can sort by GradeID rather than by the description). I also think you should add a table for Districts for the same purposes, as well as accounting for the future possibility of re-districting. These tables would then be related to tblEnrol and tblRecord respectively, and you use combo boxes on your forms to select the Grade Level and the District.
There is also another advantage to this approach. For example, you have a form with two subforms. One of the subforms is for pre-school enrollment and the other is for elementary (or primary) enrollment. The correct way to handle this is to create queries for each subform. One query returns only pre-school records and the other returns only elementary records,
but it all comes from the same table. Also, since you are using a combo box to select the grade level, the combo box can easily be set up so that (for example) the users can only select grades I - VI when entering new records in the elementary enrollment subform. You could apply this same concept to a secondary enrollment subform if you had one.
In your report, since you need different totals for pre-school as opposed to the other grade levels, one approach is to use the Domain function DSum with some criteria to return the sums for only the grade levels you want. For the sub-totals for each school you restrict the results by both grade level and SchoolID, and for the grand totals you restrict the results by grade level only. The report itself should also be based on its own query, because the result set you want for the report is not the same as the result set you want for the forms. It's basically the same data but it's put together a little differently to make the report look the way you want.
As far as getting your subforms to look like your image, you need to make them
continuous forms, but you need to design them correctly so they display the way you want. The controls need to be laid out next to each other all in one row, left to right, and the height of the form itself should be just slightly more than the height of the controls.
I have re-attached another copy of your application so you can see the application of these methods. By the way, IMO you should not use spaces in your field names, so I took the liberty of correcting that for you.
