Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2010
    Posts
    2

    Unanswered: calculating fields, with a STRANGE CAVEAT

    Thanks for reading!

    I need something like:
    When ( [Year1]:[District][School][GradeLevel] = [Year2]:[District][School][GradeLevel] +1, [Year2]:[Avg Score] - [Year1]:[Avg Score], KeepLooking )


    I have two tables, [Year1] and [Year 2], with the four relevant fields:
    [District ID], [SchoolName], [Grade Level], and [Avg Score]

    They do not have a consistent Unique Id, [District ID]&[SchoolName]&[Grade Level] could make one.


    The result would be a table like this:

    [District ID] [SchoolName] [Year1]:[Avg Score] [Year2]:[Avg Score] [Difference between Avgs]


    THIS IS EASY, EXCEPT FOR A CAVEAT:

    I want to create a table that compares [Avg Score] in a grade this year to the previous grade the previous year. For example, I want to subtract [Year1]:[Avg Score] 5th grade last year from [Year2]:[Avg Score] 6th grade this year.
    This calculation won't always be possible, as there is not always matching data (obviously there is no year before Kindergarten or after 12th grade).


    Here is my SQL so far:

    SELECT
    [Year1].DistrictID AS Year1_DistrictID,
    [Year1].SchoolName AS Year1_SchoolName,
    [Year1].GradeLevel AS Year1_GradeLevel,
    [Year1].AVGScore AS Year1_AVGScore,
    [Year2].DistrictID AS Year2_DistrictID,
    [Year2].SchoolName AS Year2_SchoolName,
    [Year2].GradeLevel AS Year2_GradeLevel,
    [Year2].AVGScore AS Year2_AVGScore, [Year2_AVGScore]-[Year1_AVGScore] AS AVGScoreYear2MinusAVGScoreYear1,
    [Year1_DistrictID] & [Year1_SchoolName] AS Year1DistSchool

    FROM
    Year1 INNER JOIN Year2 ON ([Year1].[DistrictID] = [Year2].[DistrictID]) AND ([Year1].[SchoolName] = [Year2].[SchoolName]);
    Last edited by alexjander; 09-30-10 at 15:05. Reason: clarification

  2. #2
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Please post some sample data so that I can help you with your question.

  3. #3
    Join Date
    Sep 2010
    Posts
    2
    Thanks for checking in Poppa.

    I figured out that the key to my problem was in DLookup. Actually, a better solution would have been to normalize the database and create some relationships from there.

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
  •