Results 1 to 8 of 8
  1. #1
    ayupchap is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Dec 2013
    Posts
    107

    Help filtering totals query I have (help a few days ago making on here) by another field from anothe

    Hi all


    I posted here a few days ago with a totaling query I had an issue with and it was fixed, brilliant! Now I wanted to take my issue one step further as it seems to be giving me some bother.

    I had a query created which totals all the records, this works perfectly. I wanted to add the ability to limit it to options within a field from another table. The code which is =working great is here:

    Code:
    SELECT Options.Option, Sum(IIf([Option]=[Number and Place Value],1,0)) AS NumberandplacevalueCount, Sum(IIf([Option]=[Addition and Subtraction],1,0)) AS AdditionandSubtractionCount, Sum(IIf([Option]=[Multiplication and Division],1,0)) AS MultiplicationandDivisionCount, Sum(IIf([Option]=[Fractions],1,0)) AS FractionsCount, Sum(IIf([Option]=[Measurement],1,0)) AS MeasurementCount, Sum(IIf([Option]=[Geometry],1,0)) AS GeometryCount
    FROM Options, Strands
    GROUP BY Options.Option;

    NOTE: Options can be one of 3 options S,E or D

    Now I have another table called STUDENT and want to make the results only display if the student is status SEN, from the SEN field I have created. This is a text field which displays the text "SEN" if they are SEN (this means special educational needs). Is it possible to add this to the query above somehow. The table STUDENT is currently not in the above code at all. It would be used as a filter so to speak. So instead of all results being analysed it would show only those with the status SEN.

    Is this possible? It would really help analyze students who having learning difficulties so we can help them achieve by better monitoring their progress.

    In the most amazing world ever I would be able to show the resultrs of SEN asnd non-SEN in the same chart with the 3 options totaled (S,E or D) but this might be a stretch?




    Thanks again for any help, it really is for a good cause!!!

  2. #2
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    What value links table Options to table Student? I belileve you will need an INNER JOIN on those 2 tables using the key fields along with a WHERE clause for the "SEN" value.

  3. #3
    ayupchap is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Dec 2013
    Posts
    107
    Hi Bulzie thanks so much for your reply

    Ahh OK. So far there is no link what so ever.

    I was unsure whether a link was needed because the options are specific grades attained by the choices selected within the table STRANDs which is the SQL query code you can see in my original post. I am not sure what linking the OPTIONS table to the STUDENTS table would achieve. Currently the STUDENTS table is linked to the STRANDS table.

    Could it work from the setup I have?

    The STRANDS table contains fields which are items we award marks to so for example the fields multiply, subtract etc. These can be awarded a S,E or D grade only. thats why we made the OPTIONS table so that these can be totaled using the above SQL query. I now want to try and filter it by the field SEN in the STUDENTS table so only SEN results are shown. Is that possible?

  4. #4
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Not clear on your data structure, maybe someone else can see what it is you are needing. Can you list out an example of the tables, their fields and some sample data on what is in the tables? So is the field containing the value "SEN" in the Strands table? How are you linking Options to Strands? and Strands to Students?

  5. #5
    ayupchap is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Dec 2013
    Posts
    107
    No problem Bulzie, I think that might help. So sorry for not being super clear.


    STRANDS TABLE

    Counting Multiplying Subtracting Strands ID
    (PK)
    Student ID (Link)
    S S E 1 32
    S D E 2 33
    E D S 3 34
    D D D 4 35
    E D D 5 36
    D S D 6 37
    S S D 7 38
    S E D 8 39
    S E D 9 40
    D E E 10 41
    E E E 11 42
    S S S 12 43
    E D S 13 44


    OPTIONS TABLE

    Option
    D
    E
    S


    STUDENT TABLE


    Student ID Name Year Group SEN
    33 James 3 SEN
    34 Claire 3
    35 Wilson 4 SEN
    36 Janet 5


    Here are the relationships

    STUDENTS(1)-------------->(1)STRANDS OPTIONS (has no link to any of the objects - only linked via the sql query above)

  6. #6
    ayupchap is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Dec 2013
    Posts
    107
    To add Student ID in the STUDENT table is the PK and the SEN field is in the STUDENT table not the STRANDS table.

    Imagine the Students table being the basic info, name address etc, the Strands Table there assessment, so where their gardes are entered and the Options table are the 3 options that resemble the grade options so I can do the totaling (what I got help in a previous post a few days ago).

  7. #7
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    I think somewhere you will need to add this part to link in the Student table and have criteria to look for SEN. This is a simple select, linking Strands to Student and then having condition of SEN. Not real clear still on your initial SQL statement or how you are using the Options table.

    SELECT STRANDS.StudentID, STRANDS.Counting, STRANDS.Multiplying, STRANDS.Subtracting
    FROM STRANDS INNER JOIN STUDENT ON STRANDS.StudentID = STUDENT.StudentID
    WHERE (((STUDENT.SEN)="SEN"));

  8. #8
    ayupchap is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Dec 2013
    Posts
    107
    OK thanks so much Bulzie, I think this has helped me get onto the correct path

    I created this query. Its made up of a table and a query. Heres the code. It seems to run but displays nothing. What have I done wrong do you think?

    Code:
    SELECT Options.Option, Sum(IIf([Option]=[First Of Number and Place Value],1,0)) AS NumberandplacevalueCount, Sum(IIf([Option]=[First Of Addition and Subtraction],1,0)) AS AdditionandSubtractionCount, Sum(IIf([Option]=[First Of Multiplication and Division],1,0)) AS MultiplicationandDivisionCount, Sum(IIf([Option]=[First Of Fractions],1,0)) AS FractionsCount, Sum(IIf([Option]=[First Of Measurement],1,0)) AS MeasurementCount, Sum(IIf([Option]=[First Of Geometry],1,0)) AS GeometryCountFROM Options, [STUDENT BIO Query]
    WHERE ((([STUDENT BIO Query].[SEN Status])="SEN Suport"))
    GROUP BY Options.Option;
    The above code doesn't work but the following code below does, based on 2 tables to make this SQL query. Why can't I get this to work with the SEN part? (I am aware the titles of some of the fields are different with first of included but thats what they are called in the query i'm using with the new SQL above so thats correct

    Code:
    SELECT Options.Option, Sum(IIf([Option]=[Number and Place Value],1,0)) AS NumberandplacevalueCount, Sum(IIf([Option]=[Addition and Subtraction],1,0)) AS AdditionandSubtractionCount, Sum(IIf([Option]=[Multiplication and Division],1,0)) AS MultiplicationandDivisionCount, Sum(IIf([Option]=[Fractions],1,0)) AS FractionsCount, Sum(IIf([Option]=[Measurement],1,0)) AS MeasurementCount, Sum(IIf([Option]=[Geometry],1,0)) AS GeometryCountFROM Options, Strands
    GROUP BY Options.Option;
    
    
    Why does this one work but the very top one not?
    
    Thanks again for any help here

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 03-08-2016, 03:22 PM
  2. Replies: 9
    Last Post: 03-07-2016, 04:17 PM
  3. Replies: 1
    Last Post: 12-02-2015, 12:16 PM
  4. Replies: 7
    Last Post: 07-11-2013, 10:45 AM
  5. Making a new field in a query
    By mslieder in forum Queries
    Replies: 3
    Last Post: 05-14-2008, 11:44 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums