SQL - Help please

[LosOjos]

My SQL skills are more than a little rusty (in fact, it's been at least 5 years since I last looked at SQL!) and I'm struggling to get a statement to work the way I want it to, can anybody help?

It might help to know I'm doing this as an Access query, although I was under the impression that Access has no problems handling SQL in the same way a server would (correct me if I'm wrong!).

What I have is 3 tables, 'Students', 'Achievements' and 'Behaviour'. All 3 have a column for 'Admission', and this is how the 3 are joined. From 'Students', I want to get the 'Reg', 'Surname' and 'Forename'. From 'Achievements' and 'Behaviour', I want the sum of 'Points' for each individual student between certain dates (a form pops up before running the report this query is based on that passes these dates to the query).

So, on to the statement I've tried (It's not exactly as I originally typed it, Access seems to automatically format it when you exit SQL view):

SELECT Students.Admission, Students.Forename, Students.Surname, Students.Reg, Achievements.ADate, Behaviour.BDate, Sum(Achievements.Points) AS SumOfPoints, Sum(Behaviour.Points) AS SumOfPoints1
FROM Achievements INNER JOIN (Behaviour INNER JOIN Students ON Behaviour.Admission = Students.Admission) ON Achievements.Admission = Students.Admission
WHERE (((Achievements.ADate)>=[Forms].[ReportFilter]![txtStartDate] And (Achievements.ADate)<=[Forms].[ReportFilter]![txtEndDate]) AND ((Behaviour.BDate)>=[Forms].[ReportFilter]![txtStartDate] And (Behaviour.BDate)<=[Forms].[ReportFilter]![txtEndDate]))
GROUP BY Students.Admission;

If I try to run this query, I simply get the error "You tried to execute a query that does not include the specified expression 'Forename' as part of an aggregate function."

Any ideas? I'm getting a real headache over this one, I really wish I'd done a bit more with SQL over the years to keep myself up to scratch!

[bossman]

@JoshJohnson:

Is it a flat database or relational?

[bossman]

@JoshJohnson:

SQL syntax seems slightly different obviously you seem to know your way around it but here is a couple of websites that may or may not help you.

SQL for Access
http://bytes.com/topic/access/insigh...access-queries
http://databases.aspfaq.com/general/...ith-joins.html

[LosOjos]

@JoshJohnson:

Is it a flat database or relational?

It's relational, with 'Admission' being the common key, it's the students admission number from Sims, so it's unique in the 'Students' table and the 'Achievements' and 'Behaviour' tables use it to identify the student the entry is about

[LosOjos]

Solved it!

In case anybody else has this problem, you need to include all your non-aggregate (anything that's not based on a function such as SUM() for the plain English speaking like myself!) in your 'GROUP BY' statement.

EDIT:
Spoke too soon, the total points are coming out as cumulative and if you link it to a report it wants you to specify the admission rather than returning them all. Any ideas? I'm getting a bit sick of this now...

[MattMitchell]

You need to change it round a little bit...

First off, on the aggregate part, yes, anything that's not an aggregate function, i.e. COUNT(), FIRST(), MAX(), MIN(), SUM(), needs to be listed in the GROUP BY clause. Achievements.ADate and Behaviour.BDate also come under this category.

But secondly, you'll get too many points! The trick is to think about what rows you would see before you do any grouping. If you try this query:

SELECT S.Forename, S.Surname, A.ADate, B.BDate
FROM (Students AS S INNER JOIN Achievements AS A
ON S.Admission = A.Admission) INNER JOIN Behaviour AS B
ON S.Admission = B.Admission;

then you'll see what Access is grouping for you: it's all records from Students, with all records from Behaviour that match, PLUS all records that match from Achievements, in all possible combinations! Say, for a given student, there are 12 behaviour entries, and 20 achievements entries, you'll actually get 12*20 = 240 lines back!

One way to do it is with a few queries. One query calculates the numbers behaviour points for each student, one does the same for achievement, and then a third joins these onto the students table to show the numbers. (You can skip a step and make it two queries, but it's not as "clean").

query BehaviourCounts

SELECT Admission, COUNT(*) AS BehaviourPoints
FROM Behaviour
WHERE BDate BETWEEN [Forms].[ReportFilter]![txtStartDate] AND [Forms].[ReportFilter]![txtEndDate]
GROUP BY Admission;

query AchievementCounts

SELECT Admission, COUNT(*) AS AchievementPoints
FROM Achievements
WHERE ADate BETWEEN [Forms].[ReportFilter]![txtStartDate] AND [Forms].[ReportFilter]![txtEndDate]
GROUP BY Admission;

query SummaryInfo

SELECT S.Admission, S.Forename, S.Surname, S.Reg,
Nz(A.AchievementPoints, 0) AS AchevementTotal
Nz(B.BehaviourPoints, 0) AS BehaviourTotal
FROM (Students AS S LEFT OUTER JOIN BehaviourCounts AS B
ON S.Admission = B.Admission) LEFT OUTER JOIN AchievementCounts AS A
ON S.Admission=A.Admission;

That way, you can add as many fields from Students as you want, without the grouping issue.

You need the left outer join so that students with no behaviour codes, or with no achievement codes, will still appear in the query. If you do it all with an INNER JOIN, then you'll only see students with an entry in all three tables.

The Nz() converts a NULL to a zero, i.e. any student who has no negative behaviour points will come up as having a 0, rather than a blank value.


Another way to do it is with subqueries, which has the benefit of only needing one query, but makes it a bit less readable. I suspect, although I've not checked, that Access doesn't optimise these all that well either, but try it and see how it works for you:

SELECT S.Admission, S.Forename, S.Surname, S.Reg,
(SELECT COUNT(*) FROM Behaviour AS B WHERE B.Admission=S.Admission
AND BDate BETWEEN [Forms].[ReportFilter]![txtStartDate] AND [Forms].[ReportFilter]![txtEndDate]) AS BehaviourTotal,
(SELECT COUNT(*) FROM Achievements AS A WHERE A.Admission=S.Admission
AND ADate BETWEEN [Forms].[ReportFilter]![txtStartDate] AND [Forms].[ReportFilter]![txtEndDate]) AS AchievementTotal
FROM Students AS S;

One advantage to this approach, is that you could run it as a parameter query. But it does look nasty (especially in Access which reformats your SQL).

In terms of handling the date thing, if you start running multiple queries as part of a reporting procedure, you may like to store the date range in a separate table, and have "views" (really just saved queries in Access) that only show behaviour/achievement points that fit in the date range, but that's something for another time!

The BETWEEN operator can work differently in different SQL engines;
"X BETWEEN A AND B" means
"A<= X AND X <= B" in some engines, and
"A < X AND X < B" in others, so check it's giving you the results you expect. Also bear in mind that dates are funny, and "24/02/2010" on its own tends to mean "24/02/2010 00:00" which is earlier than "24/02/2010 09:30". Play with some queries and check that you get the records returned that you're expecting to.

[LosOjos]

That was a huge help, thanks for taking the time to explain all that!