Results 1 to 10 of 10

Thread: Populating textboxes with appointments from database

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Jan 2016
    Posts
    775

    Populating textboxes with appointments from database

    Hello again VbForums
    I wish the appropriate forum for my needs is Database Development.
    My application is intended to organize appointments for doctors:
    The day is divided into 3 periods: (Morning- Afternoon and evening)
    Each period is allocated a set of time by a doctor.
    For example: Morning: 240 min
    Afternoon: 300 mn
    When a doctor sets an appointment for a patient, he sets the period (morning or afternoon) and he also sets the time that the appointment would take. (20 mn or 30mn)
    This time should be subtracted from the overall time devoted for the period of morning or afternoon.
    eg: Morning = 240 mn - 20 mn for the first appointment = 220 mn (time left for remaining appointments for a given day)
    When the time devoted for appointment is equal to the time assigned to the period (Morning), the doctor cannot set anymore appointments in this day in the slot time of the morning.
    For this I have 3 tables:

    Name:  25.png
Views: 94
Size:  13.0 KB

    Then I ran this query:
    Code:
    Select sum(Duration) as Duration, period, Date_appoint, COUNT(Date_appoint) AS AppCount, Period _tbl.ID, WorkTime_tbl.Period_ID,Full_Duration from WorkTime_tbl  " & _
    " inner join Period _tbl on WorkTime_tbl.Period_ID = Period _tbl.ID " & _
    " inner join Appointment_tbl on WorkTime_tbl.Period_ID = Appointment_tbl.Period_ID group BY Period _tbl.ID
          
    Do While Not Rs.EOF
    
    Text1(Day(Rs!Date_appoint)).Text = Rs!AppCount & " RDV" & vbNewLine & _
     "Period" & "           " & "Time left" & vbNewLine & _
    Rs!Period & "          " & Rs!Duration - Rs!Full_Duration
    Rs.MoveNext
    Loop
    End If
    The output is this:

    Name:  57.png
Views: 89
Size:  5.5 KB


    but I wish to get this output
    Name:  46.png
Views: 95
Size:  5.0 KB

    Your help is very much appreciated
    thank you in advance

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

    Re: Populating textboxes with appointments from database

    1) You don't need to separate Slot_time and Worktime. Those belong together in one Table
    2) You need a "Calendar-Table". You will have days where there is no appointment set, yet
    3) You need a Left Join between Appointments and that "Calendar"
    4) Counting how many Appointemnts there are for a day needs to be a separate Query. You can't do that in one Query

    Here's an example for SQLite:
    https://www.db-fiddle.com/f/gbu7v9msesk9qxBLQKSEtU/0

    Setup
    Code:
    CREATE TABLE 'slot_time' (
      ID Integer,
      Period TEXT);
      
    CREATE TABLE 'WorkTime' (
      ID Integer,
      Period_ID Integer,
      Time_Start TEXT,
      Time_End TEXT,
      Full_Duration INTEGER);
    
    CREATE TABLE 'Appointments' (
      ID Integer,
      Patient_ID Integer,
      Date_Appoint TEXT,
      Period_ID Integer,
      Duration Integer);
      
    INSERT INTO 'slot_time' VALUES(1, 'Morning');
    INSERT INTO 'slot_time' VALUES(2, 'Afternoon');
    INSERT INTO 'slot_time' VALUES(3, 'Evening');
    
    INSERT INTO 'WorkTime' VALUES(1,1, '08:00', '12:00', 240);
    INSERT INTO 'WorkTime' VALUES(2,2, '13:00', '17:00', 240);
    INSERT INTO 'WorkTime' VALUES(3,3, '17:00', '22:00', 300);
    
    INSERT INTO 'Appointments' VALUES(1,1, '2024-04-17', 1, 20);
    INSERT INTO 'Appointments' VALUES(2,2, '2024-04-17', 2, 30);
    INSERT INTO 'Appointments' VALUES(3,2, '2024-04-24', 2, 30);
    Query
    Code:
    WITH
        DT(AppDate) AS (VALUES('2024-04-17'),('2024-04-24')),
        TT AS (SELECT WT.ID, ST.Period, WT.Full_Duration
               FROM slot_time AS ST 
               INNER JOIN WorkTime AS WT ON ST.ID=WT.Period_ID),
        DD AS (SELECT DT.AppDate, TT.Period, TT.ID As TTID, TT.Full_Duration FROM DT 
               INNER JOIN TT ON 1=1),
        AC AS (SELECT Date_Appoint, Period_ID, COUNT(*) AS AppCount
               FROM Appointments GROUP BY Date_Appoint, Period_ID)
    
    SELECT DD.AppDate, DD.Period, COALESCE(AC.AppCount,0) AS AppCount,
    DD.Full_Duration-COALESCE(A.Duration,0) As remain 
    FROM DD 
    LEFT JOIN Appointments AS A ON A.Date_Appoint=DD.AppDate AND A.Period_ID=DD.TTID
    LEFT JOIN AC ON AC.Date_Appoint=A.Date_Appoint AND AC.Period_ID=A.Period_ID
    GROUP BY DD.AppDate, DD.Period
    ORDER BY DD.AppDate, DD.TTID
    Returns

    AppDate Period AppCount remain
    2024-04-17 Morning 1 220
    2024-04-17 Afternoon 1 210
    2024-04-17 Evening 0 300
    2024-04-24 Morning 0 240
    2024-04-24 Afternoon 1 210
    2024-04-24 Evening 0 300
    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
    Jan 2016
    Posts
    775

    Re: Populating textboxes with appointments from database

    Quote Originally Posted by Zvoni View Post
    1) You don't need to separate Slot_time and Worktime. Those belong together in one Table
    2) You need a "Calendar-Table". You will have days where there is no appointment set, yet
    3) You need a Left Join between Appointments and that "Calendar"
    4) Counting how many Appointemnts there are for a day needs to be a separate Query. You can't do that in one Query

    Here's an example for SQLite:
    https://www.db-fiddle.com/f/gbu7v9msesk9qxBLQKSEtU/0

    Setup
    Code:
    CREATE TABLE 'slot_time' (
      ID Integer,
      Period TEXT);
      
    CREATE TABLE 'WorkTime' (
      ID Integer,
      Period_ID Integer,
      Time_Start TEXT,
      Time_End TEXT,
      Full_Duration INTEGER);
    
    CREATE TABLE 'Appointments' (
      ID Integer,
      Patient_ID Integer,
      Date_Appoint TEXT,
      Period_ID Integer,
      Duration Integer);
      
    INSERT INTO 'slot_time' VALUES(1, 'Morning');
    INSERT INTO 'slot_time' VALUES(2, 'Afternoon');
    INSERT INTO 'slot_time' VALUES(3, 'Evening');
    
    INSERT INTO 'WorkTime' VALUES(1,1, '08:00', '12:00', 240);
    INSERT INTO 'WorkTime' VALUES(2,2, '13:00', '17:00', 240);
    INSERT INTO 'WorkTime' VALUES(3,3, '17:00', '22:00', 300);
    
    INSERT INTO 'Appointments' VALUES(1,1, '2024-04-17', 1, 20);
    INSERT INTO 'Appointments' VALUES(2,2, '2024-04-17', 2, 30);
    INSERT INTO 'Appointments' VALUES(3,2, '2024-04-24', 2, 30);
    Query
    Code:
    WITH
        DT(AppDate) AS (VALUES('2024-04-17'),('2024-04-24')),
        TT AS (SELECT WT.ID, ST.Period, WT.Full_Duration
               FROM slot_time AS ST 
               INNER JOIN WorkTime AS WT ON ST.ID=WT.Period_ID),
        DD AS (SELECT DT.AppDate, TT.Period, TT.ID As TTID, TT.Full_Duration FROM DT 
               INNER JOIN TT ON 1=1),
        AC AS (SELECT Date_Appoint, Period_ID, COUNT(*) AS AppCount
               FROM Appointments GROUP BY Date_Appoint, Period_ID)
    
    SELECT DD.AppDate, DD.Period, COALESCE(AC.AppCount,0) AS AppCount,
    DD.Full_Duration-COALESCE(A.Duration,0) As remain 
    FROM DD 
    LEFT JOIN Appointments AS A ON A.Date_Appoint=DD.AppDate AND A.Period_ID=DD.TTID
    LEFT JOIN AC ON AC.Date_Appoint=A.Date_Appoint AND AC.Period_ID=A.Period_ID
    GROUP BY DD.AppDate, DD.Period
    ORDER BY DD.AppDate, DD.TTID
    Returns

    AppDate Period AppCount remain
    2024-04-17 Morning 1 220
    2024-04-17 Afternoon 1 210
    2024-04-17 Evening 0 300
    2024-04-24 Morning 0 240
    2024-04-24 Afternoon 1 210
    2024-04-24 Evening 0 300
    I greatly appreciate your help
    However your code looks so advanced and I have a little problem to understand this part of the code:
    Code:
    DT(AppDate) AS (VALUES('2024-04-17'),('2024-04-24')),
    The dates ('2024-04-17'),('2024-04-24') are just sample examples.
    What if I have dozens of appointment dates in my table?
    Code:
    INSERT INTO 'Appointments' VALUES(1,1, '2024-04-17', 1, 20);
    INSERT INTO 'Appointments' VALUES(2,2, '2024-04-17', 2, 30);
    INSERT INTO 'Appointments' VALUES(3,3, '2024-04-24', 2, 30);
    INSERT INTO 'Appointments' VALUES(4,4, '2024-04-25', 1, 15);
    INSERT INTO 'Appointments' VALUES(5,5, '2024-04-28', 1, 20);
    I attempted to do the following but I need your confirmation.
    Code:
    WITH DT AS (SELECT Date_Appoint  as AppDate FROM Appointments  " & _
            "   INNER JOIN WorkTime  ON Appointments.Period_ID=WorkTime.Period_ID),
    Thank you for confirming this point
    Last edited by Mustaphi; Apr 9th, 2024 at 07:28 PM.

  4. #4

    Thread Starter
    Fanatic Member
    Join Date
    Jan 2016
    Posts
    775

    Re: Populating textboxes with appointments from database

    Another issue is that
    On my form , I have an array of 31 texboxes.
    I need to display the number of appointments , the slot time or period and time remained on the corresponding textboxes.
    the slot time where there is no appointments are ignored.
    I did as follows but no success
    Code:
    Do While Not Rs.EOF
    Labl1(Day(Rs!AppDate)).Text = _
    "Period" & "                   " & "Time left" & vbNewLine & _
    Rs!Period & "    " & Rs!AppCount & " RDV" & "    " & Rs!remain
    Rs.MoveNext
    Loop
    End If
    this is what I"m having:
    Name:  10.png
Views: 67
Size:  4.3 KB
    For a table of appointment like this one
    Code:
    INSERT Into Appointments VALUES(1,1, '2024-04-17', 1, 20);"
    INSERT Into Appointments VALUES(2,2, '2024-04-17', 1, 20);"
    INSERT Into Appointments VALUES(3,2, '2024-04-17', 2, 30);"
    INSERT Into Appointments VALUES(4,4, '2024-04-17', 2, 30);"
    INSERT Into Appointments VALUES(5,5, '2024-04-24', 1, 20);"
    INSERT Into Appointments VALUES(6,6, '2024-04-24', 2, 20);"
    INSERT Into Appointments VALUES(7,7, '2024-04-24', 2, 20);"
    What I dream to produce is this outcome
    Name:  01-01.png
Views: 62
Size:  4.9 KB
    Last edited by Mustaphi; Apr 9th, 2024 at 07:33 PM.

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

    Re: Populating textboxes with appointments from database

    btw: Which DBMS are you using?
    My Example was for SQLite

    EDIT: And your sample is wrong.
    According to your INSERTS, you don't have any appointments in the evening
    Last edited by Zvoni; Apr 10th, 2024 at 01:46 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

  6. #6

    Thread Starter
    Fanatic Member
    Join Date
    Jan 2016
    Posts
    775

    Re: Populating textboxes with appointments from database

    Quote Originally Posted by Zvoni View Post
    btw: Which DBMS are you using?
    My Example was for SQLite

    EDIT: And your sample is wrong.
    According to your INSERTS, you don't have any appointments in the evening
    I'm using SQLITE too and RC5
    Code:
    INSERT Into Appointments VALUES(1,1, '2024-04-17', 1, 20);"
    INSERT Into Appointments VALUES(2,2, '2024-04-17', 1, 20);"
    INSERT Into Appointments VALUES(3,2, '2024-04-17', 2, 30);"
    INSERT Into Appointments VALUES(4,4, '2024-04-17', 2, 30);"
    INSERT Into Appointments VALUES(5,5, '2024-04-24', 1, 20);"
    INSERT Into Appointments VALUES(6,6, '2024-04-24', 2, 20);"
    INSERT Into Appointments VALUES(7,7, '2024-04-24', 2, 20);
    i believe 2 refers to the evening .Isn't it?

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

    Re: Populating textboxes with appointments from database

    Quote Originally Posted by Mustaphi View Post
    I'm using SQLITE too and RC5
    Code:
    INSERT Into Appointments VALUES(1,1, '2024-04-17', 1, 20);"
    INSERT Into Appointments VALUES(2,2, '2024-04-17', 1, 20);"
    INSERT Into Appointments VALUES(3,2, '2024-04-17', 2, 30);"
    INSERT Into Appointments VALUES(4,4, '2024-04-17', 2, 30);"
    INSERT Into Appointments VALUES(5,5, '2024-04-24', 1, 20);"
    INSERT Into Appointments VALUES(6,6, '2024-04-24', 2, 20);"
    INSERT Into Appointments VALUES(7,7, '2024-04-24', 2, 20);
    i believe 2 refers to the evening .Isn't it?
    You should know your own Data.
    "2" refers to Afternoon

    EDIT: Here's an updated version of how i would do it
    https://www.db-fiddle.com/f/gbu7v9msesk9qxBLQKSEtU/1
    Last edited by Zvoni; Apr 10th, 2024 at 04:46 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
    Jan 2016
    Posts
    775

    Re: Populating textboxes with appointments from database

    Quote Originally Posted by Zvoni View Post
    You should know your own Data.
    "2" refers to Afternoon

    EDIT: Here's an updated version of how i would do it
    https://www.db-fiddle.com/f/gbu7v9msesk9qxBLQKSEtU/1
    Thank you for taking the trouble to help me and I 'm very sorry for my lack of concentration.
    I tested the last code on a flexgrid and everything works like a charm.
    But when running the code on textboxes, it is showing only one slot time.

    Code:
    WITH
    	CT AS (SELECT DT.ID, DT.CalDate, ST.Period, ST.ID As Period_ID, ST.Full_Duration, ST.Time_Start 
               FROM Calendar AS DT INNER JOIN slot_time AS ST ON 1=1
               ORDER BY DT.CalDate, ST.Time_Start)
    
    SELECT CT.CalDate, CT.Period, COUNT(AP.ID) As AppCount, AP.Cal_ID, CT.Full_Duration-SUM(COALESCE(AP.Duration,0))  As Remain
    FROM CT 
    LEFT JOIN Appointments AS AP ON AP.Cal_ID=CT.ID AND AP.Period_ID=CT.Period_ID
    WHERE strftime('%Y',CT.CalDate) = '" & Format(Date, "yyyy") & "' AND  strftime('%m',CT.CalDate) = '" & Format(Date, "mm")
    GROUP BY CT.CalDate, CT.Period 
    ORDER BY CT.CalDate, CT.Time_Start
    Do While Not Rs.EOF
    text1(Day(Rs!Cal_ID)).Text = _
    "Period" & "                   " & "Time left" & vbNewLine & _
    Rs!Period & "    " & Rs!AppCount & " RDV" & "    " & Rs!remain
    Rs.MoveNext
    Loop
    Name:  2024.jpg
Views: 52
Size:  27.9 KB

    The first image is not clear enough this is a more clear image.

    Name:  2024.png
Views: 50
Size:  10.5 KB
    Last edited by Mustaphi; Apr 10th, 2024 at 07:33 AM.

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

    Re: Populating textboxes with appointments from database

    Because you are overwriting it
    (untested)
    Code:
    If text1(Day(Rs!Cal_ID)).Text = "" Then 
       text1(Day(Rs!Cal_ID)).Text = "Period" ...blablablabla
    Else
       text1(Day(Rs!Cal_ID)).Text = text1(Day(Rs!Cal_ID)).Text & vbNewLine & "Period" ...blablabla
    End If
    EDIT: Or the way i would do it
    (Untested)
    Code:
    Dim i As Long
    Dim ID As Long
    Dim r(0 To 3) As String
    
    Do While Not Rs.EOF
       ID = Day(Rs!Cal_ID)
       r(0)="Period                   Time left"
       For i=1 To 3
          r(i)=Rs!Period & "    " & Rs!AppCount & " RDV" & "    " & Rs!remain
          Rs.MoveNext
       Next
       text1(ID).Text = Join(r, vbNewLine)
    Loop
    Last edited by Zvoni; Apr 10th, 2024 at 08:46 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

  10. #10

    Thread Starter
    Fanatic Member
    Join Date
    Jan 2016
    Posts
    775

    Re: Populating textboxes with appointments from database

    Quote Originally Posted by Zvoni View Post
    Because you are overwriting it
    (untested)
    Code:
    If text1(Day(Rs!Cal_ID)).Text = "" Then 
       text1(Day(Rs!Cal_ID)).Text = "Period" ...blablablabla
    Else
       text1(Day(Rs!Cal_ID)).Text = text1(Day(Rs!Cal_ID)).Text & vbNewLine & "Period" ...blablabla
    End If
    EDIT: Or the way i would do it
    (Untested)
    Code:
    Dim i As Long
    Dim ID As Long
    Dim r(0 To 3) As String
    
    Do While Not Rs.EOF
       ID = Day(Rs!Cal_ID)
       r(0)="Period                   Time left"
       For i=1 To 3
          r(i)=Rs!Period & "    " & Rs!AppCount & " RDV" & "    " & Rs!remain
          Rs.MoveNext
       Next
       text1(ID).Text = Join(r, vbNewLine)
    Loop
    Thank you for such a wonderful contribution
    The second loop did not work but the first one worked like a charm.
    I modified a bit your code to meet my needs:
    Code:
    CT AS (SELECT DT.ID, DT.CalDate, ST.Period, ST.ID As Period_ID, ST.Full_Duration, ST.Time_Start 
               FROM Calendar AS DT INNER JOIN slot_time AS ST ON 1=1
               ORDER BY DT.CalDate, ST.Time_Start)
    
    SELECT CT.CalDate, CT.Period, COUNT(AP.ID) As AppCount, AP.Cal_ID, CT.Full_Duration-SUM(COALESCE(AP.Duration,0))  As Remain
    FROM CT 
    LEFT JOIN Appointments AS AP ON AP.Cal_ID=CT.ID AND AP.Period_ID=CT.Period_ID
    WHERE strftime('%Y',CT.CalDate) = '" & Format(Date, "yyyy") & "' AND  strftime('%m',CT.CalDate) = '" & Format(Date, "mm")
    And AP.Cal_ID IN ( SELECT CT.ID FROM CT )
    GROUP BY CT.CalDate, CT.Period 
    ORDER BY CT.CalDate, CT.Time_Start
    Do While Not Rs.EOF
    text1(Day(Rs!Cal_ID)).Text = _
    "Period" & "                   " & "Time left" & vbNewLine & _
    Rs!Period & "    " & Rs!AppCount & " RDV" & "    " & Rs!remain
    Rs.MoveNext
    Loop
    Code:
    text1(Day(Rs!CalDate)).Text = text1(Day(Rs!Cal_ID)).Text
    I am so very grateful for your time.

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