Changing Values

[Fie]

Hi,


I have a database that allowes user to enter in different values
related to School each week. I have a problem with one of the reports
that i have created because during the financial Year (April to March)
The No Pupils at school changes in August at start of new school
terms. I have stored the No pupils relating to each school in a table
so that the user doesnt have to type in the no pupils each week. I know
i can go in a manually change the No Pupils at start of new term...
When i run reports for the whole Financal year the results are wrong
becuase the No pupils is changed thu affecting results entered form
April to August...the No pupils is used to do calculations in reports
i.e possible no meals = No pupils * No Days

Is there any way i can set a query/ macro or somit on the report that
uses the No pupils that basically says...

(1) Cullen = 120 pupils 1st April 2005 to 20th September 2005
Cullen = 130 pupils 1st August 2005 to 31st March 2006

(2) Portknockie = 110 pupils 1st April 2005 to 20th September 2005
Portknockie = 93 pupils 1st April 2005 to 20th September 2005

in total i have 52 schools

any ideas

fiona

 

[Jeff Boyce]

If I understand your description, you have a field that holds
NumberOfStudents and a field that holds School. You didn't mention anything
about a field that holds SchoolYear (or FinancialYear, or ... Year). If you
are only ever changing the NumberOfStudents, you don't have a "history" you
could use to generate that report.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Fie]

Hey,

The No pupils chages every August...
I have 2 tables

School Details
1) Name of School
2) No Pupils
3) Cost Centre
4) Managment Center

Meal Numbers
1) No Meals
2) No Tickets
3) No Days
4) Returned Tickets
5) Free Meals
6) Cash Banked


Table 1 stores details that are unique to each school. In the form
designed for table 2 it allowes users to select a School and not need
to enter in details related to school each week that are constant as
they are already sotred in table 1 i.e Cost Centre, Managment Center,
No Pupils...! The No Pupils is used in a report % Uptake of Meals which
generates No. Possible Meals for each school = [No Days]*[No Pupils].
The problem is that the No pupils chages each year at Start of new term
in August. How can i change the No pupils in August for each school, so
that it does not affect the figures produced before August with the
previous No pupils.

i.e Cullen School = 140 pupils from August 2004 to Sep 2005
Cullen School = 110 pupils from August 2005 to Sep 2006

 

[Jeff Boyce]

Please re-read my response. If your table doesn't have a field for
SchoolYear, you don't have a way to keep track of NumberOfStudents for each
SchoolYear. This design (with a SchoolYear) could be called a "history"
table, because you'll be able to look up a history of the information (i.e.,
NumberOfStudents for each SchoolYear).

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/