Results 1 to 9 of 9
  1. #1
    Join Date
    May 2012
    Posts
    132

    Unanswered: can this be done by a query or?

    Student lectures, subjects and locations are stored in a table like this and now we are in record ID number 154.
    Code:
    ID   studentname    lectureplace       lecturesubject
    1         jack           A             politics    
    2         steve          A             politics    
    3         jack           B             math    
    4         steve          A             politics    
    5         jack           B             politics    
    6         joe            A             politics    
    .          .             .                .
    .          .             .                .
    154       Jane           B            Geography
    We know that the next 32 lectured will be presented by jack on politics ,but we don’t know about the exact location(lectureplace). Can we instantly fill the next 32 records with the data that we know, such as student name and subject, but the lectureplace is left blank and it will be filed later when we know about the location? The resultant table will have ID from 155 to 187, stunetname:jack, lecturesubject: politics but the location will be filled later when the place of the presented lecture is known .
    Please lets forget if this job makes sense or not, and lets think if this is possible to be done.
    Thank you
    ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
    I asked god to please give me everything to enjoy the life, God said: i gave you the life to enjoy everything.

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    That would depend upon whether your table design allows nulls in the location field. If not, you would need to have a default value for this field instead.

    Once you've got around that problem, what's required is a fairly simple loop structure that takes the student name and subject, and the number of required records. It then generates and run an INSERT INTO SQL statement.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    A. Using SQL:

    1) Create a Tally table (or Number table). It will be used to replace a loop since SQL is not procedural:
    Code:
    CREATE TABLE Tbl_Tally (
        RowNumber COUNTER(1,1)
      , Anything TEXT (50)
      , CONSTRAINT pk_Tbl_Tally PRIMARY KEY (RowNumber)
    );
    2) Fill Tbl_Tally with numbers from 1 to 1000 (more than enough):
    Code:
    INSERT INTO Tbl_Tally ( Anything )
        SELECT TOP 1000 Null
          FROM MSysObjects t1, MSysObjects t2;
    3) Fill the data table (here named Tbl_Lectures):
    Code:
    INSERT INTO Tbl_Lectures ( ID, studentname, lecturesubject )
        SELECT Tbl_Tally.RowNumber, 'jack', 'politics'
          FROM Tbl_Tally
         WHERE Tbl_Tally.RowNumber BETWEEN 155 AND 187;
    B. Using VBA:
    Code:
    Sub AddToTbl_Lectures()
    
        Dim rst As DAO.Recordset
        Dim i As Long
        
        Set rst = CurrentDb.OpenRecordset("Tbl_Lectures", dbOpenDynaset)
        With rst
            For i = 155 To 187
                .AddNew
                !ID = i
                !studentname = "jack"
                !lecturesubject = "politics"
                .Update
            Next i
            .Close
        End With
        Set rst = Nothing
        
    End Sub
    C. Using a mix of both:
    Code:
    Sub AddToTbl_Lectures()
    
        Const c_SQL As String = "INSERT INTO Tbl_Lectures ( ID, studentname, lecturesubject ) VALUES ( @I, 'jack', 'politics' );"
        
        Dim i As Long
        
        For i = 155 To 187
            CurrentDb.Execute Replace(c_SQL, "@I", i), dbFailOnError
        Next i
        
    End Sub
    Have a nice day!

  4. #4
    Join Date
    May 2012
    Posts
    132
    yes, the data table allows nulls for lectureplace.
    can you instruct me on " loop structure" and the INSERT INTO SQL statement plz? as i am so novice.
    ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
    I asked god to please give me everything to enjoy the life, God said: i gave you the life to enjoy everything.

  5. #5
    Join Date
    May 2012
    Posts
    132
    dear Sinndho,
    thank you veryyyy much, they are working really fine,
    on the VBA you mentioned the exact numbers 155 to 187.
    1- can VBA understand the last ID In the table ( that is 154 here) and then VBA starts from next available ID (here it is 155) to put the data automatically?
    2- can we put the last ID number ( here 187), from a form or something like that?
    i prefer to avoid going into VBA code eachtime for putting numbers.
    thank you very much
    ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
    I asked god to please give me everything to enjoy the life, God said: i gave you the life to enjoy everything.

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by ariansman View Post
    1- can VBA understand the last ID In the table ( that is 154 here) and then VBA starts from next available ID (here it is 155) to put the data automatically?
    Code:
    Sub AddToTbl_Lectures(Byval MaxNumber As Long)
    
        Dim rst As DAO.Recordset
        Dim lngStart As Long
        Dim i As Long
        
        LngStart = Nz(DMax("ID", "Tbl_Lectures", 0) + 1 ' Get the next ID in Tbl_Lectures.
        Set rst = CurrentDb.OpenRecordset("Tbl_Lectures", dbOpenDynaset)
        With rst
            For i = lngStart To MaxNumber 
                .AddNew
                !ID = i
                !studentname = "jack"
                !lecturesubject = "politics"
                .Update
            Next i
            .Close
        End With
        Set rst = Nothing
        
    End Sub
    Quote Originally Posted by ariansman View Post
    2- can we put the last ID number ( here 187), from a form or something like that?
    i prefer to avoid going into VBA code eachtime for putting numbers.
    thank you very much
    Provided you changed the Sub procedure AddToTbl_Lectures as described above, from the Form module:
    Code:
    AddToTbl_Lectures Me.TextMaxNumber.Value
    Have a nice day!

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you base question is fairly simple, it should be doable using an append query


    but this requirement shows all the symtoms of a flaky desing
    why would you want to create this sort of data ahead of a lecture, the only justification I can see for this is an attendance record OR a reminder to students to attend. neither of these requirements justify this approach
    ..its not an attendance record as this is some time in the future, if it was then it would be fake / meaningless data.

    at present this is just derived data (or it shoudl be)

    after all you should know what students are registered for what subjects.
    you should know what lecturers are available / capable of teaching a specific subject.
    you should know when the lecture schedule, even if you don't know which room its going to be in

    from that you can create a query at any time which shows what students should be taking a class
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    May 2012
    Posts
    132
    Thank you sindho
    Well, to just make a feedback and know your idea.
    When I put that code into module the whole line of
    Code:
    LngStart = Nz(DMax("ID", "Tbl_Lectures", 0) + 1 ' Get the next ID in Tbl_Lectures.
    went red and when I pushed the play (run) button in the module window a blank window named macros popped up with nothing in mocro name and elsewhere.
    So I manipulated that VBA code to this:
    Code:
    Sub AddToTbl_Lectures()
    
        Dim rst As DAO.Recordset
        Dim i As Long
        MaxNumber = [Forms]![form1]![MaxNumber]
        lngStart = Nz(DMax("ID", "Tbl_Lectures") + 1)
        Set rst = CurrentDb.OpenRecordset("Tbl_Lectures", dbOpenDynaset)
        With rst
            For i = lngStart To MaxNumber
                .AddNew
                !ID = i
                !studentname = "jack"
                !lecturesubject = "politics"
                .Update
            Next i
            .Close
        End With
        Set rst = Nothing
        
    End Sub
    This is working greatly. Thank you very much.
    As you see I deleted some lines and statements and added a parentheses. besides i put the reference to the form here.
    So do you think I did something wrong? Is this an incorrect way to get a result that seems correct?
    Thank you
    ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
    I asked god to please give me everything to enjoy the life, God said: i gave you the life to enjoy everything.

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're right: a closing parenthese was missing in the line using the DMax() and Nz() functions, sorry!
    Have a nice day!

Posting Permissions

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