locked
weekly Student attendance system RRS feed

  • Question

  • HI

    i have a problem

    i m curently on a attendance system project i want to show the student attendance status in a weekly view

    like

          student      day1        day2    day3        day4          day5           day6

             123          P              P          P              P            A                 L

              245        P               A           L            P             P                 P

              212        P               A           L            P             P                 P

              466       P               A           L            P             P                 P

    so i want to show attendance in that style so how can i do that plz help me plz plz plz

    i have the data of each and every student in my database tables but how can i show that data in the above style


    unseendreamzzz

    Saturday, April 14, 2012 8:04 AM

Answers

  • Hi Unseend,

    Welcome to the MSDN Forum.

    Based on your table schema, I think you need such kind of SQL statement to return you expected result:

    Select Student_id, (case Attendance_date = #2012/4/16# then Attendance_Status end) as Day1,(case Attendance_date = #2012/4/17# then Attendance_Status end) as Day2,(case Attendance_date = #2012/4/18# then Attendance_Status end) as Day3,(case Attendance_date = #2012/4/19# then Attendance_Status end) as Day4 from Attendance
    where 1=1

    I hope such sql statement will help you to resolve this issue.

    If anything is unclear, please feel free to let me know.

    Best regards,


    Mike Feng
    MSDN Community Support | Feedback to us
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Proposed as answer by Mike Feng Wednesday, April 18, 2012 5:07 AM
    • Marked as answer by Mike Feng Saturday, April 21, 2012 7:46 AM
    Tuesday, April 17, 2012 8:00 AM
  • the discussion has been going on at the acces forum

    http://social.msdn.microsoft.com/Forums/en-US/accessdev/thread/4dba512d-b4be-46f6-b468-0fc7a18aaa9b

    A solution already has been posted that works.  The user is using SQL Server not ACCESS.

    One solution is as follows that doesn't use Pivot Tables.

    SELECT Attendance.Student_Id, [Day_1] AS Expr1, [Day_2] AS Expr2, [Day_3] AS Expr3, [Day_4] AS Expr4, [Day_5] AS Expr5, [Day_6] AS Expr6, DatePart([ww],[Attendance]![Attendance_Date]) AS Expr7, Year([Attendance]![Attendance_Date]) AS Expr8
    FROM Attendance
    WHERE ((([Day_1])=[Attendance]![Attendance_Status]) AND ((Weekday(CDate([Attendance]![Attendance_Date])))=2)) OR ((([Day_2])=[Attendance]![Attendance_Status]) AND ((Weekday(CDate([Attendance]![Attendance_Date])))=3)) OR ((([Day_3])=[Attendance]![Attendance_Status]) AND ((Weekday(CDate([Attendance]![Attendance_Date])))=4)) OR ((([Day_4])=[Attendance]![Attendance_Status]) AND ((Weekday(CDate([Attendance]![Attendance_Date])))=5)) OR ((([Day_5])=[Attendance]![Attendance_Status]) AND ((Weekday(CDate([Attendance]![Attendance_Date])))=6)) OR ((([Day_6])=[Attendance]![Attendance_Status]) AND ((Weekday(CDate([Attendance]![Attendance_Date])))=7));



    jdweng

    • Proposed as answer by Mike Feng Wednesday, April 18, 2012 5:07 AM
    • Marked as answer by Mike Feng Saturday, April 21, 2012 7:46 AM
    Tuesday, April 17, 2012 10:02 AM

All replies

  • What are your column in your original databae table? You need to create a SQL statement.  I think it would be better to post your request at the Access forum where people are much more familar with these type request.  Also specify which language (C#, C++, VB.net) that you are using and what type database you are connecting to (SQL Server, Access, Oracle).

    Post at this webpage

    http://social.msdn.microsoft.com/Forums/en-US/accessdev/threads


    jdweng

    Saturday, April 14, 2012 11:34 AM
  • i have the following tables

    Student : Student_Id , Student_Name ......

    Attendance: Attendance_Id,Attendance_Date,Attendance_Status,Student_Id

    how can i display thae weekly attendance

    so how can i do that

     student      day1        day2    day3        day4          day5           day6

             123          P              P          P              P            A                 L

              245        P               A           L            P             P                 P

              212        P               A           L            P             P                 P

              466       P               A           L            P             P                 P

    or if u have any other idea plz shear me that as well


    unseendreamzzz

    Monday, April 16, 2012 8:00 AM
  • Post the requestion at the Access forum.  Ask for the SQL commands.  I'm not great with SQL and would take me a while to get it correct.  They should be able to help very easily.

    http://social.msdn.microsoft.com/Forums/en-US/accessdev/threads

    Once you have the SQL it is easy to add to your visual studio project.  I can help with the visual Studio part. You still didn't specify the language you are using or the database.  Also need the operating system because the code would be different for X32 vs x64.  Do you already have a conenction to the database.  Would like to see what you already have.



    jdweng

    Monday, April 16, 2012 9:20 AM
  • Hi Unseend,

    Welcome to the MSDN Forum.

    Based on your table schema, I think you need such kind of SQL statement to return you expected result:

    Select Student_id, (case Attendance_date = #2012/4/16# then Attendance_Status end) as Day1,(case Attendance_date = #2012/4/17# then Attendance_Status end) as Day2,(case Attendance_date = #2012/4/18# then Attendance_Status end) as Day3,(case Attendance_date = #2012/4/19# then Attendance_Status end) as Day4 from Attendance
    where 1=1

    I hope such sql statement will help you to resolve this issue.

    If anything is unclear, please feel free to let me know.

    Best regards,


    Mike Feng
    MSDN Community Support | Feedback to us
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Proposed as answer by Mike Feng Wednesday, April 18, 2012 5:07 AM
    • Marked as answer by Mike Feng Saturday, April 21, 2012 7:46 AM
    Tuesday, April 17, 2012 8:00 AM
  • I think you'll need to use PIVOT query to join the results, or they will appear on seperate lines.
    Tuesday, April 17, 2012 9:55 AM
    Answerer
  • the discussion has been going on at the acces forum

    http://social.msdn.microsoft.com/Forums/en-US/accessdev/thread/4dba512d-b4be-46f6-b468-0fc7a18aaa9b

    A solution already has been posted that works.  The user is using SQL Server not ACCESS.

    One solution is as follows that doesn't use Pivot Tables.

    SELECT Attendance.Student_Id, [Day_1] AS Expr1, [Day_2] AS Expr2, [Day_3] AS Expr3, [Day_4] AS Expr4, [Day_5] AS Expr5, [Day_6] AS Expr6, DatePart([ww],[Attendance]![Attendance_Date]) AS Expr7, Year([Attendance]![Attendance_Date]) AS Expr8
    FROM Attendance
    WHERE ((([Day_1])=[Attendance]![Attendance_Status]) AND ((Weekday(CDate([Attendance]![Attendance_Date])))=2)) OR ((([Day_2])=[Attendance]![Attendance_Status]) AND ((Weekday(CDate([Attendance]![Attendance_Date])))=3)) OR ((([Day_3])=[Attendance]![Attendance_Status]) AND ((Weekday(CDate([Attendance]![Attendance_Date])))=4)) OR ((([Day_4])=[Attendance]![Attendance_Status]) AND ((Weekday(CDate([Attendance]![Attendance_Date])))=5)) OR ((([Day_5])=[Attendance]![Attendance_Status]) AND ((Weekday(CDate([Attendance]![Attendance_Date])))=6)) OR ((([Day_6])=[Attendance]![Attendance_Status]) AND ((Weekday(CDate([Attendance]![Attendance_Date])))=7));



    jdweng

    • Proposed as answer by Mike Feng Wednesday, April 18, 2012 5:07 AM
    • Marked as answer by Mike Feng Saturday, April 21, 2012 7:46 AM
    Tuesday, April 17, 2012 10:02 AM
  • So there's field Day_1, Day_2, etc. in the Attendence table and you no longer need to devive it from Attendence_Date?

    If so, there's really no need for pivot table. If not, I think you've misread the question.

    Wednesday, April 18, 2012 2:20 AM
    Answerer
  • Day_1 is an alias which is derived from the Date using the Weekday formula and combined with the Attendance Status.

    jdweng

    Wednesday, April 18, 2012 3:41 AM
  • Do you realize the CDate() and Weekday() are not functions on SQL server, they are inline VBA code that is only valid on MS Access, and "!" notation is also Access only?

    And btw, you can't define columns in where clause... The SQL statement you provide won't run on any SQL server.

    And btw again, I'll comment that redirect a user asking SQL question without stating it's running on Access to and Access forum is a bit... extraordinary.

    Wednesday, April 18, 2012 4:25 AM
    Answerer