Results 1 to 9 of 9

Thread: Again this query

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    813

    Again this query

    Hello VbForums
    Last time I had a similar post and I'm so grateful to the experts of VbForums who helped me.
    (Zvoni , techgnome, Olaf and others)
    I apologize because I was not clear enough, and I did not define my needs clearly.

    In fact I have a Class_tbl which holds all the pupils of a class.
    And an Another child table (Absence_tbl) that holds the absentees.

    Code:
    Set Cnn = New_c.Connection(, DBCreateInMemory)
    With Cnn.NewFieldDefs
     .Add "ID   Integer Primary key"
     End With
    Cnn.CreateTable "Class_tbl"
    
        Cnn.Execute "INSERT INTO Class_tbl (ID) VALUES ('1')"
        Cnn.Execute "INSERT INTO Class_tbl (ID) VALUES ('2')"
        Cnn.Execute "INSERT INTO Class_tbl (ID) VALUES ('3')"
        Cnn.Execute "INSERT INTO Class_tbl (ID) VALUES ('4')"
        Cnn.Execute "INSERT INTO Class_tbl (ID) VALUES ('5')"
        Cnn.Execute "INSERT INTO Class_tbl (ID) VALUES ('6')"
    Code:
    With Cnn.NewFieldDefs
          .Add "PID   Integer"
          .Add "FDate ShortDate"
      End With
    Cnn.CreateTable "Absence_tbl"
    
       Cnn.Execute "INSERT INTO Absence_tbl (PID, FDate) VALUES ('1', '2023-03-19')"
       Cnn.Execute "INSERT INTO Absence_tbl (PID, FDate) VALUES ('2', '2023-03-22')"
       Cnn.Execute "INSERT INTO Absence_tbl (PID, FDate) VALUES ('3', '2023-03-10')"
       Cnn.Execute "INSERT INTO Absence_tbl (PID, FDate) VALUES ('1', '2023-03-21')"
    I use this code to retrieve all pupils who are present today.

    Code:
     Dim StrSql As String
       StrSql = "Select *  from Class_tbl " & _
    " Where ID NOT IN (SELECT PID FROM Absence_tbl  " & _
      " Where FDate  = '" & Format(Date, "yyyy-mm-dd") & "')"
    
    Do Until Rs.EOF
              Debug.Print Rs!PID, Rs!FDate
              Rs.MoveNext
            Loop
    I'm getting the exact expected output.
    1
    3
    4
    5
    6
    Only one Record (ID = 2) is absent on 2023-03-22

    Now I want to retrieve all the sessions in which of a particular pupil was present ( ID = 1)

    Code:
    StrSql = "Select *  from Class_tbl " & _
    " Where ID NOT IN (SELECT PID FROM Absence_tbl  " & _
      " Where PID = 1) "
    Code:
    Do Until Rs.EOF
              Debug.Print Rs!PID, Rs!FDate
              Rs.MoveNext
            Loop
    I'm wishing to to get this output:

    1 ........... 2023-03-22
    1 .......... 2023-03-10

    Unfortunately I failed.
    thank you
    Last edited by newbie2; Mar 22nd, 2023 at 02:24 PM.

  2. #2
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    3,847

    Re: Again this query

    Donít use subselects.
    use a left join

    EDIT: remove the NOT in your WHERE clause
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  3. #3

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    813

    Re: Again this query

    Quote Originally Posted by Zvoni View Post
    Donít use subselects.
    use a left join

    EDIT: remove the NOT in your WHERE clause
    thank you
    Code:
    StrSql = "Select * from Absence_tbl  " & _
    " LEFT JOIN Class_tbl " & _
    " on Class_tbl.ID = Absence_tbl.PID " & _
    " where  ID = 1  "
    Set Rs = Cnn.OpenRecordset(StrSql)
    
    Do Until Rs.EOF
              Debug.Print Rs!PID, Rs!FDate
              Rs.MoveNext
            Loop
    the output is this
    1 ...............19/03/2023
    1................21/03/2023

    But I wish to get this:
    1 ...............22/03/2023
    1 ...............10/03/2023

    thank you

  4. #4

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    813

    Re: Again this query

    Quote Originally Posted by goliathmanstrong View Post
    Maybe I'm just not getting what you're describing... but if you insert the record into your absence table when someone is absent, do you even need to worry about the class table with your select statement?

    If you want only absences then don't they already exist within your absence table?

    SELECT * FROM ABSENCE_TBL WHERE PID = 1

    Would that not give you what you're looking for?
    thank you
    But I want to retrieve pupils who are present based on the absence table.

  5. #5
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    3,847

    Re: Again this query

    Oh boy. This setup is IMO all wrong.

    i read pupils and classes, so this is a classroom scenario with absence and presence

    you need a table just with (consecutive?) dates, your pupils-table and a third table connecting both in a m:m relation
    this third table has foreign key to the dates and another foreign key to the pupils. A third column is a boolean indicating absence/presence.
    easy enough
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  6. #6

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    813

    Re: Again this query

    Quote Originally Posted by Zvoni View Post
    Oh boy. This setup is IMO all wrong.

    i read pupils and classes, so this is a classroom scenario with absence and presence

    you need a table just with (consecutive?) dates, your pupils-table and a third table connecting both in a m:m relation
    this third table has foreign key to the dates and another foreign key to the pupils. A third column is a boolean indicating absence/presence.
    easy enough
    I tried to apply this scenario but I'm lost.
    May be because I did not understand this scenario well.
    Briefly
    I have a pupils table and Absence table
    I can retrieve the list of absentees easily from the asence table.

    But I'm trying to retrieve the list of attendees based on the absence table.
    Then getting all the dates of attendence or presence of a particular pupil.
    Can your scenario do the task?
    thanks

  7. #7
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    3,847

    Re: Again this query

    Quote Originally Posted by newbie2 View Post
    But I'm trying to retrieve the list of attendees based on the absence table.
    Then getting all the dates of attendence or presence of a particular pupil.
    Based on what criteria?
    If you only have a table, where a pupil is marked absent with its date, how do you think you're going to get anything for "presence" if there is no record?

    Can your scenario do the task?
    Yes. What database are you using? SQLite?

    EDIT: For SQLite:
    https://www.db-fiddle.com/f/xmnJixZD4cWzz2tsEmCPkA/1

    As i said: Easy enough
    In the fiddle on the right hand side pane you can play with the WHERE-Clause (lines 14+15)
    Last edited by Zvoni; Mar 23rd, 2023 at 03:33 AM.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  8. #8

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    813

    Re: Again this query

    Zvoni
    thak you for the link
    I followed your approach
    This is my Presence table
    Attachment 187232
    Name:  2-49-10.png
Views: 44
Size:  6.9 KB
    I can display the list of attendees on a grid easily
    For this I use:
    Code:
    StrSql = "Select Fname,  Lname , " & _
    " BirthDate, classe_name, subject from class_tbl" & _
     " left join Presence_tbl on class_tbl.ID = Presence.PID " & _
    " left join subject_tbl on Presence_tbl.subj_id = subject.id " & _
     " left join class_tbl on Presence_tbl.class_id = class_tbl.ID   " & _
      " Where Presence  = 1 and date_id = " & Lbl_Date(1).Caption
    Set Rs = Cnn.OpenRecordset(StrSql)
    However when I want to display the list of attendees with the number of sessions attended for each pupil, I can only get one record displayed on the grid.

    Code:
    StrSql = "Select Fname,  Lname , " & _
    " BirthDate, classe_name, subject,  sum (presence) as presences from class_tbl" & _
     " left join Presence_tbl on class_tbl.ID = Presence.PID " & _
    " left join subject_tbl on Presence_tbl.subj_id = subject.id " & _
     " left join class_tbl on Presence.class_id = class_tbl.ID   " & _
      " Where Presence  = 1 and date_id = " & Lbl_Date(1).Caption
    Set Rs = Cnn.OpenRecordset(StrSql)
    thank you

  9. #9
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    3,847

    Re: Again this query

    I don't remember anymore, but i think in SQLite if you don't specify a GROUP BY it sums up everything.
    Add a GROUP BY Fname, Lname, Birthdate, class_name, subject after the WHERE
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width