+ Post New Thread
Results 1 to 7 of 7
Coding Thread, SQL - Help please in Coding and Web Development; My SQL skills are more than a little rusty (in fact, it's been at least 5 years since I last ...
  1. #1

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,432
    Thank Post
    1,432
    Thanked 1,160 Times in 794 Posts
    Rep Power
    705

    Question SQL - Help please

    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):

    Code:
    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!

  2. #2

    bossman's Avatar
    Join Date
    Nov 2005
    Location
    England
    Posts
    3,898
    Thank Post
    1,182
    Thanked 1,053 Times in 748 Posts
    Rep Power
    327
    @JoshJohnson:

    Is it a flat database or relational?

  3. #3

    bossman's Avatar
    Join Date
    Nov 2005
    Location
    England
    Posts
    3,898
    Thank Post
    1,182
    Thanked 1,053 Times in 748 Posts
    Rep Power
    327
    @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
    Last edited by bossman; 26th March 2010 at 01:24 PM.

  4. Thanks to bossman from:

    LosOjos (26th March 2010)

  5. #4

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,432
    Thank Post
    1,432
    Thanked 1,160 Times in 794 Posts
    Rep Power
    705
    Quote Originally Posted by bossman View Post
    @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

  6. #5

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,432
    Thank Post
    1,432
    Thanked 1,160 Times in 794 Posts
    Rep Power
    705
    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...
    Last edited by LosOjos; 26th March 2010 at 02:23 PM.

  7. #6

    Join Date
    May 2009
    Location
    Sheffield
    Posts
    242
    Thank Post
    21
    Thanked 35 Times in 26 Posts
    Rep Power
    20
    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:

    Code:
    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").

    Code:
    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:

    Code:
    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.

  8. Thanks to MattMitchell from:

    LosOjos (29th March 2010)

  9. #7

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,432
    Thank Post
    1,432
    Thanked 1,160 Times in 794 Posts
    Rep Power
    705
    That was a huge help, thanks for taking the time to explain all that!

SHARE:
+ Post New Thread

Similar Threads

  1. SQL Server
    By DSapseid in forum Windows
    Replies: 1
    Last Post: 18th May 2009, 12:48 PM
  2. repairing SQL
    By Hecate in forum MIS Systems
    Replies: 1
    Last Post: 9th November 2008, 05:34 PM
  3. SQL
    By mac_shinobi in forum How do you do....it?
    Replies: 5
    Last Post: 6th February 2008, 02:42 PM
  4. MS SQL 2005 - Dump SQL
    By tom_newton in forum Windows
    Replies: 7
    Last Post: 23rd January 2008, 05:10 PM
  5. SQL Anywhere
    By Jobos in forum MIS Systems
    Replies: 5
    Last Post: 12th November 2007, 01:05 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •