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

    Monday, April 16, 2012 9:39 AM

Answers

  • You can use a crosstab query;

    TRANSFORM 
    SELECT 
    FROM 
    GROUP BY 
    PIVOT 
    ;

    This site will show you through some example;http://office.microsoft.com/en-us/access-help/make-summary-data-easier-to-read-by-using-a-crosstab-query-HA010229577.aspx

    • Proposed as answer by Nick Vittum Monday, April 16, 2012 12:53 PM
    • Marked as answer by Bruce Song Monday, April 23, 2012 7:20 AM
    Monday, April 16, 2012 11:11 AM
  • I left the week out of the formula and year

    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:06 AM
    • Marked as answer by Bruce Song Monday, April 23, 2012 7:20 AM
    Monday, April 16, 2012 6:00 PM
  • hI,

    You need a query to look like this;

    SELECT [Attendance_Id], [2012-03-04],[2012-04-03],[2012-04-04]
    FROM
    (SELECT  [Attendance_Id]
          , [Attendance_Date]
          , [Attendance_Status]
    FROM    StudentRoll 
           )   p
    PIVOT
    (
    MAX([Attendance_Status])
    FOR [Attendance_Date] IN
    ( [2012-03-04],[2012-04-03],[2012-04-04] )
    ) AS pvt
    ORDER BY [Attendance_Id];

    To make it easier for you; I build a dynamic query that can create the Dates columns for you without specifying each one;

    Just replace the table name "StudentRoll" used in the query below;

    DECLARE @query NVARCHAR(4000)
    DECLARE @cols VARCHAR(8000)
    SELECT  @cols =   COALESCE(@cols + ',[' +  CAST(Attendance_Date AS varchar(30)) + ']',
                             '[' +  CAST(Attendance_Date AS varchar(30)) + ']')
    FROM    StudentRoll
    WHERE Attendance_Date IS NOT NULL
    GROUP BY [Attendance_Date]
    ORDER BY [Attendance_Date]
    SET @query = N'SELECT [Attendance_Id], ' +
    @cols +'
    FROM
    (SELECT  [Attendance_Id]
          , [Attendance_Date]
          , [Attendance_Status]
    FROM    StudentRoll 
           )   p
    PIVOT
    (
    MAX([Attendance_Status])
    FOR [Attendance_Date] IN
    ( '+ @cols +' )
    ) AS pvt
    ORDER BY [Attendance_Id];'
    EXECUTE(@query)

    Hope that helps

    P.s Please mark as Answered if this your issue


    • Edited by Joscion Tuesday, April 17, 2012 12:45 AM
    • Marked as answer by unseendreamzzz Sunday, April 29, 2012 2:29 PM
    Tuesday, April 17, 2012 12:22 AM

All replies

  • You can use a crosstab query;

    TRANSFORM 
    SELECT 
    FROM 
    GROUP BY 
    PIVOT 
    ;

    This site will show you through some example;http://office.microsoft.com/en-us/access-help/make-summary-data-easier-to-read-by-using-a-crosstab-query-HA010229577.aspx

    • Proposed as answer by Nick Vittum Monday, April 16, 2012 12:53 PM
    • Marked as answer by Bruce Song Monday, April 23, 2012 7:20 AM
    Monday, April 16, 2012 11:11 AM
  • ok here i have Attendance Table

    Attendace: Attendance_Id, Attendance_Date, Attendance_Status.

    so how can i use the query in that table plz explain me with example if u dnt mind


    unseendreamzzz

    Monday, April 16, 2012 12:58 PM
  • The syntax looks like this;

    TRANSFORM First(table1.[Attendance_Status]) AS Attendance_StatusOfFirst
    SELECT table1.[Attendance_Id], First(table1.[Attendance_Status]) AS [Total Of Attendance_Status]
    FROM table1
    GROUP BY table1.[Attendance_Id]
    PIVOT table1.[Attendance_Date];

    Use the Query wizard for easist setup; Under the create menu->select query wizard->Cross tab

    1)First choose the ID (Row data)

    2)Then the Dates Column (Column Heading)

    3)Then the Text Column (values)


    -- Please mark my post as an answer if I helped you to resolve the issue or vote up if it helped, thank you--


    • Edited by Joscion Monday, April 16, 2012 1:10 PM
    Monday, April 16, 2012 1:09 PM
  • If this resolves the issue please select as Answered to close the question and also to help others see it  as answered. 


    • Edited by Joscion Monday, April 16, 2012 1:36 PM
    Monday, April 16, 2012 1:34 PM
  • actualy i m using it for visual studio or for sql  server and it is not working plz if u dnt mind i need a query which have same result as it and use in sql server and in visual studio


    unseendreamzzz

    Monday, April 16, 2012 3:58 PM
  • See if this works.  I'm not sure if the Weekday function is available on SQL Server.  this method doesn't use a Pivot Table.  I built the query from Access query builder.

    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
    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);
    Monday, April 16, 2012 5:28 PM
  • I left the week out of the formula and year

    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:06 AM
    • Marked as answer by Bruce Song Monday, April 23, 2012 7:20 AM
    Monday, April 16, 2012 6:00 PM
  • hI,

    You need a query to look like this;

    SELECT [Attendance_Id], [2012-03-04],[2012-04-03],[2012-04-04]
    FROM
    (SELECT  [Attendance_Id]
          , [Attendance_Date]
          , [Attendance_Status]
    FROM    StudentRoll 
           )   p
    PIVOT
    (
    MAX([Attendance_Status])
    FOR [Attendance_Date] IN
    ( [2012-03-04],[2012-04-03],[2012-04-04] )
    ) AS pvt
    ORDER BY [Attendance_Id];

    To make it easier for you; I build a dynamic query that can create the Dates columns for you without specifying each one;

    Just replace the table name "StudentRoll" used in the query below;

    DECLARE @query NVARCHAR(4000)
    DECLARE @cols VARCHAR(8000)
    SELECT  @cols =   COALESCE(@cols + ',[' +  CAST(Attendance_Date AS varchar(30)) + ']',
                             '[' +  CAST(Attendance_Date AS varchar(30)) + ']')
    FROM    StudentRoll
    WHERE Attendance_Date IS NOT NULL
    GROUP BY [Attendance_Date]
    ORDER BY [Attendance_Date]
    SET @query = N'SELECT [Attendance_Id], ' +
    @cols +'
    FROM
    (SELECT  [Attendance_Id]
          , [Attendance_Date]
          , [Attendance_Status]
    FROM    StudentRoll 
           )   p
    PIVOT
    (
    MAX([Attendance_Status])
    FOR [Attendance_Date] IN
    ( '+ @cols +' )
    ) AS pvt
    ORDER BY [Attendance_Id];'
    EXECUTE(@query)

    Hope that helps

    P.s Please mark as Answered if this your issue


    • Edited by Joscion Tuesday, April 17, 2012 12:45 AM
    • Marked as answer by unseendreamzzz Sunday, April 29, 2012 2:29 PM
    Tuesday, April 17, 2012 12:22 AM
  • HI sorry for late reply 

    my table is Attendance and its column is 

    Attendance_Id, Attendance_Date,Attendance_Status,Student_Id

    so plz now snd me query which is fit to my table

    plz


    unseendreamzzz

    Wednesday, April 25, 2012 12:59 PM
  • Joscion indicated in his original post: "Just replace the table name "StudentRoll" used in the query below;"

    So simply replace the term StudentRoll with Attendance wherever it is found in his code/proc.


    Daniel Pineault, 2010 Microsoft MVP
    http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net


    Wednesday, April 25, 2012 1:22 PM
  • plz Joscion will u explain this query for me i cant understand this


    unseendreamzzz

    Wednesday, April 25, 2012 2:49 PM
  • What part do you need help with? I wrote the query so you just have to change "StudentRoll" to your table name, try that and let me know the results.

    -- Please mark my post as an answer if I helped you to resolve the issue or vote up if it helped, thank you--

    Thursday, April 26, 2012 2:43 AM
  • joscion if i want to insert data in my table then what query i have to use

    i also want to insert data in that format 


    unseendreamzzz

    Thursday, April 26, 2012 9:09 AM
  • this select query help me but i want to insert and update my record also so is there is any query which can help me 


    unseendreamzzz

    Thursday, April 26, 2012 9:38 AM
  • i mean when i use your select query then it give me the format which i want but i need that format for update and insert also

    unseendreamzzz

    Thursday, April 26, 2012 9:40 AM
  • plz i want insertion in this for mate so plz give me any idea how can i do that 

    unseendreamzzz

    Sunday, April 29, 2012 2:30 PM
  • plz joscion help me ur query help me for selecting in the format which i want but now i have to insert also in that format so plz is there is any query or any thing else which help me plz 

    unseendreamzzz

    Monday, April 30, 2012 7:54 AM