Results 1 to 9 of 9
  1. #1
    Daryl2106 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    167

    Need your ideas and help...

    Hi Everyone,



    I have a student info, grades and attendance DB that many of you on this forum have helped design. I have not been able to solve my current challenge on my own and need help, please.

    From a table of course data that includes a row for each course a student is enrolled including a field for each week of classes (attendance), I would like to FIRST calculate a monthly average for each course for the 4 weeks of attendance data entered for that month (entered as an integer). That would mean a single "cell" for each month (jan-apr) for each course.

    I have tried some easy functions written into queries but I often get errors.

    Ideally I would like to place a button on the student data form that will open and run the query for the current student and diplay as a chart or pivot table.

    I tried using several approaches: crosstab querry, pivot table, query based on another query, etc.

    I welcome and appreciate your help

    Take care,

    Daryl

  2. #2
    Rawb is offline Expert
    Windows XP Access 2000
    Join Date
    Dec 2009
    Location
    Somewhere
    Posts
    875
    How do you enter attendance? For example: In the first week student A attends class 3 times. If you are supposed to attend 4 times each week, how would you enter that? is it entered as 3, or as -1, or as 75%, etc? Also, how do you know how many classes per week there are for each course?

  3. #3
    Daryl2106 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    167
    Hi,

    We just calculate a percent and enter that. If a student attends 4 of 5 classes we enter 80. This is not ideal but gives us a rough measure for attendance. I will be working on another way for the next academic year.

    For now there is a record for each student and each course they are in. If John is in four courses there would be four records with his Student ID and each would have a different course name. With each record their is a field for each week (ending) such as April 6, 2012.

    Currently each faculty member enters the attendance so they know how many classes there were in a given class in a given week.

    Take care,

    Daryl

  4. #4
    Rawb is offline Expert
    Windows XP Access 2000
    Join Date
    Dec 2009
    Location
    Somewhere
    Posts
    875
    Well, if you're dealing with a straight percentage, then you should be able to just average them out.

    If you want to use a straight Query, you can go with something like this:

    Code:
    SELECT
      [Week1]+[Week2]+[Week3]+[Week4]/4 AS Month1
    FROM
      AttendanceTable
    WHERE
      StudentID=n;

  5. #5
    Daryl2106 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    167
    Hi Rawb,

    Thanks! I havn't tried this yet but can I add successive lines like:


    SELECT [Week1]+[Week2]+[Week3]+[Week4]/4 AS Month1 [Week5]+[Week6]+[Week7]+[Week8]/4 AS Month2FROM AttendanceTableWHERE StudentID=n;

  6. #6
    Rawb is offline Expert
    Windows XP Access 2000
    Join Date
    Dec 2009
    Location
    Somewhere
    Posts
    875
    Well, I was just gonna say "Yup!" but the Forum wouldn't let me

    P.S.
    Yup!

  7. #7
    Daryl2106 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    167
    Hi Rawb,

    Thanks! That is working out very well. The only probelm is that the SQL is returning the sum rather than the avg even thought the SQL reads:

    [StudentEnrollmentTable].[Jan 06 2012]+[StudentEnrollmentTable].[Jan 13 2012]+[StudentEnrollmentTable].[Jan 20 2012]+[StudentEnrollmentTable].[Jan 27 2012] /4 AS JanAttAvg,

    Any thoughts?

    Take care,

    Daryl

  8. #8
    Rawb is offline Expert
    Windows XP Access 2000
    Join Date
    Dec 2009
    Location
    Somewhere
    Posts
    875
    What's the Type of those fields in the Table? Are they set up as Number or as Text?

    If they're a Number Type, then I'm not sure what's going wrong. For lack of any better ideas, you might want to try wrapping them in parens:
    Code:
    (([StudentEnrollmentTable].[Jan 06 2012]+[StudentEnrollmentTable].[Jan 13 2012]+[StudentEnrollmentTable].[Jan 20 2012]+[StudentEnrollmentTable].[Jan 27 2012])/4) AS JanAttAvg,

  9. #9
    Daryl2106 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    167
    Perfect! I tried bracketing it earlier but only used a single pair.

    Thanks soooooo much!

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

Similar Threads

  1. Password ideas w/o using VBA on Sharepoint?
    By Heatshiver in forum SharePoint
    Replies: 1
    Last Post: 03-02-2012, 02:39 AM
  2. Weather Database Ideas
    By bigroo in forum Database Design
    Replies: 4
    Last Post: 01-19-2012, 09:43 AM
  3. New to access, need ideas!
    By phenicie in forum Access
    Replies: 1
    Last Post: 12-27-2011, 05:55 PM
  4. Any ideas?
    By eripsni in forum Access
    Replies: 9
    Last Post: 08-25-2011, 08:33 AM
  5. Need some ideas
    By amauricio2 in forum Database Design
    Replies: 0
    Last Post: 03-02-2009, 11:03 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