Jefro said:
the marks are all in a seperate table in the database called mark, so I don't quite understand your last bit.
But you have fields called Mark1, Mark2, Mark3, etc which is not the way to deal with a repeating group. Say you have 10 fields called Mark1, Mark2,..., Mark10 and you now need to add a further five fields for marks. You would think that you would simply add five new fields Mark11...Mark15 but this has an effect on every other item that makes use of the table in the database such as queries, forms, reports, and modules.
This is because marks, in this instance, are considered an entity within your database but have not been represented as such. An entity is considered an object (i.e. an order, an employee, a description, a code, etc. ) that needs to be modelled into the database structure.
The thinking in database design is that tables should
grow down and
not grow across. By growing down the field structure does not need to be changed and therefore affects no other part of the database objects.
You may have eight out of ten marks to put into the database so you'll have two blank fields. These blank fields still take up space and is why a table that grows down (records only when you need them) is the logical option.
A quick example I can think of is that you would not have a table called
Continents and then add fields such as
ContinentName,
FirstCountry,
SecondCountry,
ThirdCountry,...,
Twenty-SixthCountry because each continent has a different number of countries. South America, for example, has ten while Europe has many more. The record for South America would be full of blank cells.
The correct approach is to
normalize the data - at least to
Third Normal Form (search here and with Google for more info) so that no space (or as little as possible) is wasted.
So, in the continent/country example two tables are actually needed. Two because a country is one sort of entity and a continent is another sort of entity.
The first table is
tblContinents and it needs two fields. One is the ContinentID (an Autonumber) and the other is the ContinentName (a text field).
tblContinents
ContinentID (Primary Key; Autonumber)
ContinentName (Text)
The second table is
tblCountries and it needs three fields. One is the CountryID (an Autonumber), another is the CountryName (text), and the third is a foreign key to the Conitinents table, called ContinentID (a number field).
tblCountries
CountryID (Primary Key; Autonumber)
CountryName (Text)
ContinentID (Foreign Key; Number)
The idea is that in the relationships window the two fields called ContinentID are linked to create a one-to-many relationship as one continent can hold many countries.
I don't know anything about the rest of your tables so I can't comment on how to get the better structure - unless you detail further your current table structures.
However, when you take the continent/country problem with respect to your function above the following becomes obvious:
What happens if I want to know how many countries are within a continent?
The first example I gave:
ContinentName, FirstCountry, SecondCountry, ThirdCountry,..., Twenty-SixthCountry
This would require a custom function to look in each field, determine if there is a value and count it if there is. This is what you are trying to do with your marks.
Using a proper relational model, as I've detailed with the two table structures, I can simply use a domain aggregate function on the Countries table.
i.e.
=DCount("*", "tblCountries", "ContinentID = 1")
Where 1 is the Autonumber value (in tblContinents) for Europe, for example.
No need for custom functions.
