Help Me Please!! SQL Query, Desperatly.

Factual Questions
1

Hi there, I really need help very fast with this SQL query can anyone help me please??

This is for an Access97 Database about a school athletics carnival…

Here are the tables:

event = [age, event, record, apparatus]
eventdetails = [eventNo, age, event, division, time]
points = [division, place, points]
recordholder = [age, event, student, year]
results = [student, eventNo, place, result]
student = [student, house]
trophies = [trophy, requirement, age, event]

Assume that all the names have relationships correct.

Now here’s my problem, I have to write an SQL query that adds up all the points that each house gets (12 houses), every house has students that compete in one or more event and that students is given a place, if the division that student was competing in was the same as the division in the points table and the place the student got was the same then his house gets that many points.

I have written this but I get error messages that I can’t fix:

SELECT student.house, sum(points.points)
FROM student, points, eventdetails, results
GROUP BY student.house
HAVING events.division = points.division AND
results.place = points.place AND results.eventNo = events.eventNo;

It says “Can’t execute a query that doesn’t used the specified expression” and then gives me everything after the word “HAVING”.

Please help me, I know I shouldn’t have left it this long to ask all you people, I have learnt a valuable lesson from this… if you need help then ask. I have about 12 hours till I have to hand it in.

If you need any more info please ask for it.

PerfectDark

2

I was just working my way up to an answer and hit a brick wall…

I managed to make this query that gets the student name and the points they got for every eventno and their house. But What I want to do with this is add all the points from the same house together, that’s it… that’s all I want to do and I can’t do it… damn group by.

SELECT student.student, points.points, student.house
FROM student, results, points, eventdetails
WHERE student.student=results.student AND eventdetails.eventNo = results.eventNo AND eventdetails.division = points.division;

PLEASE HELP

PerfectDark

3

I am no Access expert but I was a dba in a former life. I’ll write this in standard SQL which hopefully is what Access uses.
SELECT
student.house
,sum(points.points)
FROM
student
,results
,eventdetails
,points
WHERE student.student = results.student
and results.eventNo = eventdetails.eventNo
and results.place = points.place
and eventdetails.division = points.division
GROUP BY student.house
;

4

Thankyou ever so much for helping me… It works like a dream. I’ll remember this and back you up in any debates.

Thanks once again… you don’t know how much you have saved me.

PerfectDark

5

No problem. It is a relatively easy error to make because SQL is somewhat english-like. I have seen all sorts of people confuse “having” with “where” because they have similar english meanings.

The “having” clause is primarily used to do processing on the groups. It is kind of like the “where” clause for the “group by”.

For example:

SELECT
student.house
,sum(points.points)
FROM
student
,results
,eventdetails
,points
WHERE student.student = results.student
and results.eventNo = eventdetails.eventNo
and results.place = points.place
and eventdetails.division = points.division
GROUP BY student.house
HAVING sum(points.points) > 10
;

would give you all houses that had more than 10 points.