Lets look at a few fundamental principles of relational database design and
apply these to your situation.
Firstly a relational database models a part of the real world in terms of
'entity types' and the relationships between them. Each entity type is
modelled by a table. The attribute types of each entity type are represented
by columns in the table, e.g. FirstName, LastName are attribute types of an
entity type People. Some attribute values will be unique to each row, e.g a
person's Social Security Number; this is a 'candidate key' of the table so
could be used as its primary key, though its common for a separate autonumber
column, e.g. PersonID, to be used as the primary key.
Its important that all non-key attributes of an entity type are specific to
the entity type; in the jargon thy are said to be functionally dependent
solely on the whole of the primary key. Firstname and LastName obviously
are, but say some people in the table share an address then if you repeat all
the address columns for each person you are creating a lot of redundancy.
This is undesirable because discrepancies could creep in and the same address
be represented differently in two or more rows, by a simple typo for
instance. The solution is to have an AddressID foreign key column in the
People table, referencing the primary key of an Addresses table in which each
address is represented by one row. No redundancy and no opportunity for
discrepancies.
Entity types can be related to each other in one-to-one, one-to-many or
many-to many relationships. The first two are implemented simply by having a
foreign key column reverencing a primary key of another table as with people
and Addresses. In a one-to-one relationship the foreign key of the
referencing table is also its primary key, or at last a candidate key.
Many-to-many relationships are not created directly between two tables, but
by a third table which resolves the many-to-many relationship into two
one-to-many relationships. More of this later.
Looking at your situation it seems to me that for what we are currently
concerned with we have the following entity types:
1. Schools
2. AcademicYears
3. EnrolmentYears
Essential these are just lists of the values of each entity type, Schools
being a list of school names with other data per school as its columns,
AcademicYears being simple a list of all the school years over a period, e.g.
2006-07, 2007-08, 2008-09 etc. and EnrolmentYears being Year 1, Year 2 etc.
Together these constitute the framework in which the real meat of the
database, the enrolment data are entered.
Enrolments is actually a relationship type between the above three entity
types. A relationship type is really a special kind of entity type, of a
more abstract nature, and is, as mentioned above modelled by a table (any
relationship type, even a one-to-many one can be modelled by a table, though
normally its only many-to-many ones which are, though it is done with
one-to-many relationship types in some special circumstances). To model the
relationship type between the above three entity type, therefore, a table is
created with three foreign key columns referencing the primary keys of the
referenced tables. An additional column is added to represent the
NumberEnrolled attribute, so we have the following columns:
SchoolID
AcademicYear
EnrolmentYear
NumberEnrolled
Note that SchoolID is a numeric 'surrogate key', i.e. an arbitrary unique
number per school but with no meaning per se, while AcademicYear and
EnrolmentYear are 'natural keys', i.e. the meaningful values. The reason
they differ is that its possible that two schools could have the same name so
using an arbitrary number distinguishes them (I attended Walton school in
Liverpool as a kid, and there is also a Walton school here in Stafford, so it
does happen), whwreas the AcademicYear and EnrolmentYear values are by
definition unique, so can be used as natural keys. SchoolID, AcademicYear
and EnrolmentYear should be made the composite primary key of this table –
this is important to prevent inadvertent duplication.
With the above 'logical model' data entry of the enrolment numbers can be
done by means of an unbound form with a bound subform, in continuous form or
datasheet view, in it based on the Enrolments table. The main parent form
would have two unbound combo boxes, one which listed all schools by means of
a RowSource such as:
SELECT SchoolID, School
FROM Schools
ORDER BY School;
By setting its BoundColumn property to 1, its ColumnCount to 2 and its
ColumnWidths to 0cm;8cm (Access will automatically convert this to inches if
you enter the metric dimensions, but the important thing is that the first is
zero and the second at least as wide as the combo box) the school names will
show but the hidden SchoolID will be the value of the control.
The other combo box will list academic years with a RowSource:
SELECT AcademicYear
FROM AcademicYears
ORDER BY AcademicYear;
As this only returns one column you can leave its other properties as the
defaults.
The subform will be based on the Enrolments table but you only need to
include controls for the EnrolmentYear and the NumberEnrolled columns as the
other two columns are automatically given values in the table by the link to
the main parent form. This is done by setting the subform control's
LinkmasterFields property to the names of the controls on the parent form
like so:
cboSchool;cboAcademicYear
and the LinkChildFields property to the names of the corresp[onding columns
in the Enrolments table:
SchoolID;AcademicYear
To return to your original question here you can get the highest enrolment
number per shooll per academic year with a simple aggregating query, grouping
by academic year and school, e.g.
SELECT AcademicYear, Schools.SchoolID, School,
MAX(NumberEnrolled) As HighestEnrolment
FROM Enrolments INNER JOIN Schools
ON Enrolments.SchoolID = Schools.SchoolID
GROUP BY AcademicYear, SchoolID, School;
To produce a result table of the type you illustrated with each enrolment
year as the column headings and the academic years and schools as the row
headings you could use a crosstab query. You can then join this to the above
query on AcademicYear and SchoollD to add the column of totals to the final
result table.
I would not use a crosstab query myself, however, if I were in your shoes,
but do it all in a report, where you can present the data much better. To do
this base a report on the Schools table and group it first by School and the
by SchoolID, possibly giving the SchoolID group a Group Header (see below).
Then create a subreport based on the Enrolments table, sort it by
EnrolmentYear and link it to the main report on SchoolID (i.e. reflect the
form/subform I described above). To get the data into columns per enrolment
year set the subform's layout so it has 10 columns and these are 'across then
down'. Put the subreport in the main report's detail section. If you want
the report to be for just one academic year then put a text box bound to the
school name at the left of the detail section and the subreport immediately
to its right and base the subreport on a query which restricts the result to
the academic year in question (you won't need the group header if you do
this). If you want it to show all academic years then put the shooll name
control in the group header and the subreport in the detail section below
this so the school name appears only once. In the page header put labels
Year1, Year 2 etc being careful to align theses so they show above the
figures returned by the subreport.
The above does mean that data must be entered for each enrolment year per
school of course, so be sure that for each school a value is entered for each
enrolment year in the Enrolments table, even if its only zero. In fact you
could write an INSERT INTO query which automatically inserts a row with zero
for each enrolment year per school for a particular academic year, ready for
the zeros to be updated to the real figures in the subform described above.
I'm sure that by now you must be recoiling in horror at the thought of
implementing this, but if you take it step by step and test it art each stage
its not nearly as scary as it sounds. Let me know if you need further advice.
BTW when I was working for a research unit at Oxford many years ago there
was some guy on a Rhodes Scholarship at the University from you neck of the
woods, a certain William J Clinton. Don't know what became of him but I
heard he married some smart lawyer who could make a name for herself before
long<G>.
Ken Sheridan
Stafford, England above
Golfinray said:
Thanks so much. I'm fairly new so please give me a little more information on
"encoding data as column headings." How could I break my table down to make
this better? Thanks!
Ken Sheridan said:
You can get the highest value from a list of a variable number of values with
a function like this:
Public Function GetMax(ParamArray aVals() As Variant) As Long
' returns highest value from list of positive integer values
Dim lngMax As Double
Dim var As Variant
' first set lngMax variable zero
lngMax = 0
' loop through array of values and compare each
' with value of lngMax variable.
' If higher then assign current value to the variable
For Each var In aVals
If var > lngMax Then
lngMax = var
End If
Next var
GetMax = lngMax
End Function
Paste it into a standard module in the database and call it in the query,
passing the values for each enrolment year into it. In query design view
you'd enter something like this in the 'field' row of a blank column - all
one one line; it will be split here:
[Highest Enrolment]:GetMax([Year 1],[Year 2],[Year 3],[Year 4],[Year
5],[Year 6],[Year 7],[Year 8],[Year 9],[Year 10])
However, having to resort to this results from a design flaw in the
database. By having a column for each year you are doing what's known in the
jargon as 'encoding data as column headings'. In a relational database data
should be stored only as values at column positions in rows in tables, what's
called the 'Information Principle'. Your table should really be decomposed
into related tables so that for each enrolment year per school per academic
year there would be one row in the related table with columns for School,
AcademicYear, EnrollmentYear and NumberEnrolled. With such a model
aggregation over a single column is all that's required to get the
MAX(NumberEnrolled) per school per academic year.
You don't mention the academic year, so I assume that you are either
deleting the data each year and refilling the table, or have separate tables
for each academic year. If the latter this is a similar design flaw, in this
case encoding data as table names. There is no reason why data over many
years cannot be stored in one set of related tables. When dealing with one
year you simply restrict it on the basis of an AcademicYear column, but it
also gives the opportunity for comparative analyses over a range of years.
Ken Sheridan
Stafford, England
Golfinray said:
We could use the table but I need the results to show as a field in my
current query. The table has the same fields as the query and some additional
fields that I don't need in my query, like school address, campus name, etc.
Thanks!
:
It'll be much easier if we can query the "Db0_District_Enrollments" table
directly instead of working through your query. Is this possible? If yes,
post the fields that are in the "Db0_District_Enrollments" table that
contain the appropriate enrollment and year and school information.
If not, post back and then we'll craft a query that uses your current query.
--
Ken Snell
<MS ACCESS MVP>
I have 10 fields, year 1 through year 10, so I need to search 10 fields. I
built the query from a table called "Db0_District_Enrollments which
contains
aal the enrollment of all the school districts in Arkansas for the last 10
years. Thanks!
:
How many "Year x" fields do you have? To write a query to return the data
you seek, the query must know exactly how many fields need to be
"searched".
Is this initial query getting its data from another source? Can we use
that
source for this new query?
--
Ken Snell
<MS ACCESS MVP>
I have a query that holds school districts total enrollment for the last
10
years so the fields look something like this:
School Year 1 Year 2 Year 3 Year 4 Year 5
..........
Little Rock 20,154 20,102 22,090 21,487 20,989
Conway 8,174 8.900 9,102 10,001 9,980
There are 50 or more districts, each with data for year 1 through year
10.
I
want a column at the end that tells me the number of students in the
highest
enrollment year. I don't need to know the year, just the number.
Thanks!
:
It's not clear what you want the query to do. Do you want the maximum
value
for each school? the maximum value from all the schools? the maximum
value
for each year for each school?
Post the information about the query's SQL statement or the table's
structure; whichever is the actual source of data that you want to use
for
your "max" query.
--
Ken Snell
<MS ACCESS MVP>
I have 10 enrollments of about 50 schools, year 1, year 2, year 3,
etc.
in
a
query. I would like to know which one is highest and return that
value
(maybe
year 6 is 850 for that particular school and is the highest and so
it
returns
850.) Any help would be greatly appreciated.
