I'd also like to see the ERD (Entity Relationship Diagram) to get a better understanding of your problem.
I'm thinking if you're creating a report to present the data in this fashion, you can setup a grouping based on an expression, either using built-in functions or use a user-defined function in a VBA module.
When you have an aggregate function, you need a GROUP BY
statement. In your case, it would be
SELECT
LEERLINGEN.LLNR,
UITLENINGEN.LLNR,
LEERLINGEN.VOORNAAM,
LEERLINGEN.TUSSENVOEGSEL,
LEERLINGEN.ACHTERNAAM,
SUM(UITLENINGEN.BOETE)
FROM
LEERLINGEN
INNER JOIN UITLENINGEN ON LEERLINGEN.LLNR = UITLENINGEN.LLNR
WHERE
(((UITLENINGEN.BOETE)>25))
GROUP BY
LEERLINGEN.LLNR,
UITLENINGEN.LLNR,
LEERLINGEN.VOORNAAM,
LEERLINGEN.TUSSENVOEGSEL,
LEERLINGEN.ACHTERNAAM;
The GROUP BY clause comes after a WHERE clause but before an ORDER BY (or HAVING) clause on a SELECT statement. In this case, you want to get the sum of "Boete" for each combination of your other columns, hence the need to group by all of those other rows.
In short, if you have an aggregate function (like SUM, AVG, MIN, or MAX), you'll need to tell the database engine what to do with those non-aggregated columns. That's a simplistic way of explaining what GROUP BY does for you there.
Incidentally, if you want to find out cases where the sum of all Uitleningen.Boete is greater than 25 (rather than only including the records in which Uitleningen.Boete is greater than to 25), you actually have to use the HAVING clause. Then it would look like this:
SELECT
LEERLINGEN.LLNR,
UITLENINGEN.LLNR,
LEERLINGEN.VOORNAAM,
LEERLINGEN.TUSSENVOEGSEL,
LEERLINGEN.ACHTERNAAM,
SUM(UITLENINGEN.BOETE)
FROM
LEERLINGEN
INNER JOIN UITLENINGEN ON LEERLINGEN.LLNR = UITLENINGEN.LLNR
GROUP BY
LEERLINGEN.LLNR,
UITLENINGEN.LLNR,
LEERLINGEN.VOORNAAM,
LEERLINGEN.TUSSENVOEGSEL,
LEERLINGEN.ACHTERNAAM
HAVING
SUM(UITLENINGEN.BOETE)>25;
Think of HAVING as a WHERE for your groups. You filter rows using WHERE, and you filter groups using HAVING.
Best Answer
Rather than calculating the distance between two points on the fly, you can narrow your selection by approximation.
Since we know that one degree of latitude is a nearly-constant 111km, a search for locations within 100km should rule out points greater than one degree of latitude distant.
Longitude is more complex because of the latitudinal delta (~111km per degree at the equator to 0 at the poles), but there are ways to approximate it. One method would be to simply calculate, in advance, the distance between degrees of longitude for each whole degree of latitude and store the result in a table. That table could be joined on the search target table using a rounded value for the input latitude.
Movable Type has a well-known, informative page dedicated to great circle navigation and related calculations.
Note: I realize this doesn't answer the actual question, but it does address the underlying need for a performant method of lat-long distance calculation, which I hope is helpful.