Search:

Type: Posts; User: newbie2

Page 1 of 13 1 2 3 4

Search: Search took 0.04 seconds.

  1. Replies
    124
    Views
    5,866

    Re: Another complicated query but perhaps possible

    I created this database

    Dim StrSql As String
    Dim DBFile As String
    DBFile = App.path & "\Example.db"
    Set Cnn = New RC6.cConnection
    If Dir(DBFile, vbNormal) = vbNullString Then
    If...
  2. Replies
    124
    Views
    5,866

    Re: Another complicated query but perhaps possible

    Sorry I confused teachers with students
    So instead of Steven Spielberg,James Cameron and Stanley Kubrick, we can put
    Adam Sandler; Elijah Wood and Nicole Kidman
    But the idea is the same
    thanks
  3. Replies
    124
    Views
    5,866

    Re: Another complicated query but perhaps possible

    I think there is slight thing to modify in this code.
    When I print RS!SumPayment, I get correct result, except if a subject is duplicated.
    In this scenario:
    Steven Spielberg's remaining = 0 (for...
  4. Replies
    124
    Views
    5,866

    Re: Another complicated query but perhaps possible

    Zvoni
    I think there is still misunderstanding.
    As you can see in the grids above:
    Some teachers have received completely their payments while others not.
    I want to get the total "sumpayment " ...
  5. Replies
    124
    Views
    5,866

    Re: Another complicated query but perhaps possible

    I wish you safety road
  6. Replies
    124
    Views
    5,866

    Re: Another complicated query but perhaps possible

    Zvoni
    Please look closely at the code.
    Certainly there something wrong but I couldn't find it.
    Please examine this scenario
    English SumPayment = 30
    Physics SumPayment = 50
    Maths SumPayment = 20...
  7. Replies
    124
    Views
    5,866

    Re: Another complicated query but perhaps possible

    The SumPayment of the student who have completed their payments is 54000 but the code is giving 24000


    WITH
    TR AS (SELECT SC.ID, SC.Student_ID, COALESCE(SC.Price, S.SubjectPrice) AS...
  8. Replies
    124
    Views
    5,866

    Re: Another complicated query but perhaps possible

    WHERE T.ID = 1
    Ok it worked
  9. Replies
    124
    Views
    5,866

    Re: Another complicated query but perhaps possible

    sorry Zvoni
    May be there is a misunderstanding.
    I want to calculate the TeacherTotal of a specific teacher.
    Somehing Like I'm doing here:

    StrSql = "SELECT " & _
    " TR.ID,...
  10. Replies
    124
    Views
    5,866

    Re: Another complicated query but perhaps possible

    Sorry sir perhaps I was not clear enough
    the first requirement:
    I want to calculate the SumPayment of all students who have completed their paments.
    i mean the receipts IRRESPECTIVE of the Subject...
  11. Replies
    124
    Views
    5,866

    Re: Another complicated query but perhaps possible

    Hello sir
    I want also to calculate the TeacherTotal of a specific teacher.
    For this I use this query:


    StrSql = "SELECT " & _
    " TR.ID, TR.Teacher_Subject_ID,SC.Price, TS.TeacherPercentage, "...
  12. Replies
    124
    Views
    5,866

    Re: Another complicated query but perhaps possible

    Zvoni
    I deeply feel ashamed but I'm sorry I have no other way.
    I need to calculate the SumPayment of all the students who have completed their payments but I'm stuck.
    When I add :
    Debug.Print...
  13. Replies
    124
    Views
    5,866

    Re: Another complicated query but perhaps possible

    Zvoni
    You are wonderful
    You’re the best
    Thank you million times
  14. Replies
    124
    Views
    5,866

    Re: Another complicated query but perhaps possible

    Take all your time sir
    thank you very much
  15. Replies
    124
    Views
    5,866

    Re: Another complicated query but perhaps possible

    Zvoni
    I deeply apologise for you and for Vbforums
    Maybe this is my last requirement
    I have been struggling for hours to adapt this last code on teachers' payments but without your help I'm...
  16. Replies
    124
    Views
    5,866

    Re: Another complicated query but perhaps possible

    Zvoni
    Not only you are generous but you are so brillant.
    Million thanks
  17. Replies
    124
    Views
    5,866

    Re: Another complicated query but perhaps possible

    Mr. Zvoni
    Another requirement if possible
    In addition to the code retreiving the students who have not completed their payments,
    I need another code that query for those who have not completed...
  18. Replies
    124
    Views
    5,866

    Re: Another complicated query but perhaps possible

    You made me feel happy.
    Thank you very much for your generosity
  19. Replies
    124
    Views
    5,866

    Re: Another complicated query but perhaps possible

    Zvoni
    I'm so grateful to you
    I managed to retrieve the students whose RemainingSubjectPrice= 0 with my code in my last post.
    I even tested your code and it is working perfect.
    My main ptoblem...
  20. Replies
    124
    Views
    5,866

    Re: Another complicated query but perhaps possible

    I tried this code , it seems to work but there is a problem.


    StrSql = " WITH " & _
    " SC AS ( SELECT SC.Student_ID, ST.ID,SC.Class_ID, SC.NumOfSessions,SC.Price," & _
    ...
  21. Replies
    124
    Views
    5,866

    Re: Another complicated query but perhaps possible

    Hello Zvoni
    Sorry sir to bother you again
    I was absent for a couple of weeks and now I'm back to my project.
    I want to ask if it is possible to get rid of the field "Paid" and to retrieve the...
  22. Replies
    124
    Views
    5,866

    Re: Another complicated query but perhaps possible

    Million thanks sir
  23. Replies
    124
    Views
    5,866

    Re: Another complicated query but perhaps possible

    I created a new database and used the initial insertion codes and changed some data to make prices equal to payments and it worked properly.
    I think I need to check my own database.
    But what is...
  24. Replies
    124
    Views
    5,866

    Re: Another complicated query but perhaps possible

    Is there something I would do?
  25. Replies
    124
    Views
    5,866

    Re: Another complicated query but perhaps possible

    Zvoni
    Sorry to disturb you again
    This code is showing only one record.
    I have three teachers who have completed their payments but only the first teacher is retrieved.
    When I examine the code,...
  26. Replies
    124
    Views
    5,866

    Re: Another complicated query but perhaps possible

    Sorry I didn't notice that we have moved to page 3

    No they are true tables
    I can even check in some sqlite tools and I could see that:
    FOREIGN KEY("Class_ID") REFERENCES...
  27. Replies
    124
    Views
    5,866

    Re: Another complicated query but perhaps possible

    I dropped the tbl_student_class and created a new table using this code but
    I still can delete a record in tbl_teacher_subject that has an entry in tbl_student_class.

    CREATE TABLE...
  28. Replies
    124
    Views
    5,866

    Re: Another complicated query but perhaps possible

    Zvoni
    thank you very much
    I'm following your guidelines but I still can delete records in tbl_teacher_subject though they have reference in tbl_student_class.
    Of course I can't do that manually,...
  29. Replies
    124
    Views
    5,866

    Re: Another complicated query but perhaps possible

    Hello Zvoni
    I've just noticed an issue.
    I think that the "tbl_teacher_subject" should have the "ID" field AUTOINCREMENT .
    Let's imagine this scenario:
    Teacher "X" has been attributed (Class_ID 2...
  30. Replies
    124
    Views
    5,866

    Re: Another complicated query but perhaps possible

    I think I got it but I need your confirmation

    WITH
    SC AS (SELECT SC.Class_ID, SUM(COALESCE(SC.Price, S.SubjectPrice)*SC.NumOfSessions) As TotalPrice,
    ...
  31. Replies
    124
    Views
    5,866

    Re: Another complicated query but perhaps possible

    Thank you very much sir
  32. Replies
    124
    Views
    5,866

    Re: Another complicated query but perhaps possible

    Hello Zvoni
    I need to retrieve the teachers who have completely received their pays.
    For that I used this code but it doesn't seem to work properly.

    WITH
    SC AS (SELECT SC.Class_ID,...
  33. Replies
    124
    Views
    5,866

    Re: Another complicated query but perhaps possible

    The the "100" is coming from the table subject
    I sent the wrong code.
    I fact in my original code, the student price is set to null
    I think I need some time to concentrate carefully.
    Perhaps due...
  34. Replies
    124
    Views
    5,866

    Re: Another complicated query but perhaps possible

    Dim sSQL As String
    DBFile = App.path & "\Example2.db"
    Set Cnn = New RC6.cConnection

    If Dir(DBFile, vbNormal) = vbNullString Then
    If Cnn.CreateNewDB(DBFile) = False Then
    ...
  35. Replies
    124
    Views
    5,866

    Re: Another complicated query but perhaps possible

    Dim sSQL As String
    DBFile = App.path & "\Example.db"
    Set Cnn = New RC6.cConnection

    If Dir(DBFile, vbNormal) = vbNullString Then
    If Cnn.CreateNewDB(DBFile) = False Then
    ...
  36. Replies
    124
    Views
    5,866

    Re: Another complicated query but perhaps possible

    I'm sorry sir I'm still having trouble.
    I can't figure out where the problem is.

    SQL = SQL & sCTE & ") UPDATE tbl_student_class As TSC SET Paid=CTE.CalcPaid FROM CTE WHERE CTE.ID=TSC.ID"
    Set Rs...
  37. Replies
    124
    Views
    5,866

    Re: Another complicated query but perhaps possible

    Zvoni
    I'm getting error in misplacing the bracket ")"
    Cannot compile Select statement ")" : syntax error



    Dim sCTE As String
    Dim sLJ As String 'For the LEFT JOIN
    Dim sIJ As String 'For...
  38. Replies
    124
    Views
    5,866

    Re: Another complicated query but perhaps possible

    Zvoni Excuse me
    I'm getting crasy to make the last code working.
    I spent a whole night trying to understand the code.

    StrSql = "WITH " & _
    " CTE AS (SELECT SC.ID,...
  39. Replies
    124
    Views
    5,866

    Re: Another complicated query but perhaps possible

    You are so generous
    Million thanks sir
  40. Replies
    124
    Views
    5,866

    Re: Another complicated query but perhaps possible

    Zvoni
    Thank you very much for the very sophisticated code .
    It worked like a charm.
    thank you again
Results 1 to 40 of 500
Page 1 of 13 1 2 3 4



Click Here to Expand Forum to Full Width