Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2021
    Posts
    23

    [VBA & SQL] Complex Problem: Append IDs based on content of other table

    Hello guys,

    I have a rather "complex problem" and I have problems on one of the last steps of automation and I will try to explain it as clearly as possible I'd be really grateful for your help/advice!
    I a DB for the creation of people and the assignment of different roles and combinations of roles and codes (which is sort of their qualification).

    What I managed so far is:


    1) Create users automatically with a form (data in tbl_Person)
    2) Assign an already created user a role via combobox and write this combination of PersonID, RoleID and StatusID into tbl_PersonRoleStatus, also automatically).
    3) Assign an already created user a combination of role and code (because most codes are tied to a role but there are also codes which are not tied to any role). So that also kind of works. Data are written automatically into tbl_PersonRoleCodeStatus.

    Now here is my problem:

    In the background, there is a tbl_Requirements which just lists requirements which are tied to tbl_Criterion via a foreign key (see DB-Model). This is because to save redundancy.
    In tbl_Criterion, there are the combinations of requirements, roles and codes. The specific thing is that RoleID and CodeID in tbl_Criterion is nullable which means that you can have either criteria which are tied to only RoleID (so independant of any CodeID), which are tied to both RoleID and CodeID (which is the case when an already created user gets a code in tbl_PersonRoleCodeStatus) or a criterion can be only tied to a CodeID (independant of RoleID).

    My problem is that I have a big difficulty to write an SQL INSERT INTO ...SELECT... Statement based on what is in tbl_PersonRoleStatus and tbl_PersonRoleCodeStatus. The task is: if a selected person has chosen a role with RoleID = 1, then I want to get all CriteriaID which are tied to ONLY Role = 1 but which CodeID-Fields are empty (Null). I have to get the PersonID into tbl_Question (which is the target table of all this).

    I know that my statement should begin lke this:

    "INSERT INTO tbl_Questions (PersonID, CriterionID) SELECT ...

    This is difficult for me because I don't exactly know how to formulate a SELECT statement that retrieves CriterionID which equals the selected role and the relevant criteria from tbl_Criterion.

    the SELECT-Part is very difficult for me because I am an SQL beginner but I know that since in tbl_Criterion, there are the nullable RoleID and nullable CodeID, you have to use a LEFT JOIN without WHERE-Statement because if there is a recordset in tbl_Criterion which is for example RoleID = 1 and CodeID = null and you use an LEFT JOIN with WHERE condition, the QUERY automatically becomes an INNER JOIN and the whole recordset is not apprehended. I also thought about just do a normal query for this part and then copy the SQL-Code it didn't work for me. I once managed to append ALL criteria for example where RoleID=1 which is obviously also not what I wanted because there were also recordsets which were tied to both RoleID = 1 AND CodeID = 1 for example but I only wanted the RoleID-tied criteria into tbl_Questions so that I can assign each Role-selection of a Person relevant questions that are to be answered.

    This is my problem. Can anyone give advice on this INSERT-Statement? I'd be very grateful!


    Click image for larger version. 

Name:	RoleAssignmentForm.png 
Views:	21 
Size:	12.2 KB 
ID:	44620Click image for larger version. 

Name:	RoleAssignmentFunctionality.png 
Views:	22 
Size:	10.3 KB 
ID:	44621Click image for larger version. 

Name:	tbl_Criterion.png 
Views:	22 
Size:	18.5 KB 
ID:	44622Click image for larger version. 

Name:	tbl_Question.png 
Views:	21 
Size:	9.9 KB 
ID:	44623Click image for larger version. 

Name:	DBModelRelationships.jpg 
Views:	22 
Size:	68.5 KB 
ID:	44619

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I suggest you post a cut down version of your database and someone will look at it.
    However your comments about LEFT joins with WHERE clauses being changed to INNER joins is incorrect.
    That combination is definitely OK and indeed commonly used in both SELECT and INSERT queries
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    Join Date
    Jan 2021
    Posts
    23
    here is my zipped DB :=)

    Thank you!
    Attached Files Attached Files

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    OK so guide anyone downloading it on what exactly they need to look at and what you hope to happen
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    Join Date
    Jan 2021
    Posts
    23
    Quote Originally Posted by isladogs View Post
    OK so guide anyone downloading it on what exactly they need to look at and what you hope to happen
    Okay

    1) Open Form f_qry_PersonRoleCodeStatus. Use combobox "Name" and select the user. Then use Combobox "Rolle" and select "Editor". Then use combobox Scope and select "Code1". Last but not least Select combobox Status "Applicant".
    2) Open tbl_PersonRoleCodeStatus and see, if a recordset has been appended (it should be).
    3) Switch Back to Form f_qry_PersonRoleCodeStatus and click Button "Create relevant questions". A similar button needs to be implemented in Form f_qry_PersonRoleStatus.

    1) Now here is the thing: I want the last button to have the functionality to append the relevant PersonID and CriterionID into tbl_Question. There needs to be an INSERT INTO-Statement tied to this button and I don't know to figure it out since I am a beginner.
    The important information on this one is: it should append PersonID = 1 and CriterionID = 7 in this example. in tbl_Requirement, there are Requirements tied not to RoleID neither CodeID. But the primary key RequirementID is a foreign key in tbl_Criterion. A recordset in tbl_Criterion can be either tied to only RoleID, to both RoleID AND CodeID OR Only to CodeID. And based on these nullable foreignkeys RoleID and CodeID in tbl_Criterion, I want the INSERT-INTO-Statement that automatically inserts the relevant CriterionID together with the PersonID of the selected Person in the combobox (and also the PersonID has been appended of cource due to the Role-Assignment OR Code-Assignment). The button should also allow to only select "Code 3" for example without RoleID and without an Error (sorry for that one, didn't know how to allow "NULL" in the INSERT INTO-Statement so far of the last combobox "Status" and should append PersonID = 1 together with CriterionID = 31 into tbl_Question because CriterionID = 31 is the relevant one for this code.

    RoleID in tbl_RoleCodeStatus is also nullable since there are Codes that can be selected that are not tied to a RoleID but in most cases, it's a combination of both RoleID and CodeID.

    2) In the other case, I need a button with the exact same functionality in Form f_qry_PersonRoleStatus.
    Steps are: Select a user in combobox Name, then select "Editor" in combobox Role and then select "Applicant" in combobox Status. A recordset should be inserted into tbl_PersonRoleStatus with the IDs from the comboboxes.
    Now the button with the functionality should append PersonID = 1 and all recordsets with CriterionID = 1 till 3 and only them the criteria for a role selection are only tied to RoleIDs in tbl_Criterion and the neighboring CodeID fields are NULL


    Thank you for reading everyone. I hope that somebody can take a look at this one! Many thanks!

  6. #6
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I'm not sure I understand correctly, but maybe this will help you.
    I couldn't come up with a single SQL query, I'm getting better at SQL, but still struggling. I couldn't figure out how to include the PersonID value from the form, so I used VBA code.
    Also, there are several issues I think you should remedy: you should have "Option Explicit" as the 2nd line in every Module.
    You used "Name" as a variable. "Name" is a reserved word in Access and shouldn't be used for an object name.
    Since I an using O365, I had to add a reference to be able to use "dbFailOnError" in code.

    I think you are making a lot of work for yourself by using unbound forms, but.......

    1) For the button on form "f_qry_PersonRoleCodeStatus" (still haven't figured out your naming scheme), this is the code for the button "Befehl39_Click()" I came up with:
    Code:
    Private Sub Befehl39_Click()
        Dim d As DAO.Database
        Dim r As DAO.Recordset
        Dim sSQL As String
        Dim RC As Long         'recordset record count
        Dim lngPerson As Long
        Dim lngRolle As Long
        Dim lngCode As Long
        
        
        If Not IsNumeric(Me.cboName) Then
            'missing name
            MsgBox "Please select a Name, then try again."
            Exit Sub
        End If
        
        If Not IsNumeric(Me.cboRolle) And Not IsNumeric(Me.cboScope) Then
            'both null so do nothing
            MsgBox "Please select Rolle and/or Scope, then try again."
            Exit Sub
        End If
        
        Set d = CurrentDb
        
        lngPerson = Me.cboName
        
        If IsNumeric(Me.cboRolle) And Not IsNumeric(Me.cboScope) Then
            'Me.cboRolle Not Null - Me.cboScope Is Null
            sSQL = "SELECT tbl_Criterion.CriterionID"
            sSQL = sSQL & " FROM tbl_Criterion"
            sSQL = sSQL & " WHERE (tbl_Criterion.RoleID =" & Me.cboRolle & ") AND (IsNull(tbl_Criterion.CodeID));"
        ElseIf Not IsNumeric(Me.cboRolle) And IsNumeric(Me.cboScope) Then
            'Me.cboRolle Is Null - Me.cboScope Is Not Null
            sSQL = "SELECT tbl_Criterion.CriterionID"
            sSQL = sSQL & " FROM tbl_Criterion"
            sSQL = sSQL & " WHERE (tbl_Criterion.CodeID = " & Me.cboScope & ") AND (IsNull([tbl_Criterion].[RoleID]));"
    
        ElseIf IsNumeric(Me.cboRolle) And IsNumeric(Me.cboScope) Then
            'Me.cboRolle Is Not Null - Me.cboScope Is Not Null
            sSQL = "SELECT tbl_Criterion.CriterionID"
            sSQL = sSQL & " FROM tbl_Criterion"
            sSQL = sSQL & " WHERE tbl_Criterion.RoleID = " & Me.cboRolle & " AND tbl_Criterion.CodeID = " & Me.cboScope & ";"
        End If
        
        If Len(sSQL) > 0 Then
    '    Debug.Print sSQL
            Set r = d.OpenRecordset(sSQL)
            If Not r.BOF And Not r.EOF Then
                r.MoveLast
                r.MoveFirst
                RC = r.RecordCount
        
                Do While Not r.EOF
                    sSQL = "INSERT INTO tbl_Question (PersonID, CriterionID)"
                    sSQL = sSQL & " VALUES (" & lngPerson & "," & r!CriterionID & ");"
    '                Debug.Print sSQL
                    d.Execute sSQL, dbFailOnError
    '                Debug.Print r!CriterionID
                    r.MoveNext
                Loop
            End If
        End If
        
        'clean up
        On Error Resume Next
        
        r.Close
        Set r = Nothing
        Set d = Nothing
        
    End Sub
    Seems to insert records correctly for me........


    2) Still thinking about it.......




    EDIT: I would not use two checkboxes for Pass/Fail. (tblQuestions)
    What if both checkboxes get checked? I would use a combo box with "Pass", "Fail" as options. Or you could use a Value List of "0;Fail,1,Pass" if you wanted to save values (like tables) instead of text.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 12-23-2018, 04:22 PM
  2. Replies: 1
    Last Post: 12-04-2016, 05:43 PM
  3. Replies: 15
    Last Post: 07-20-2013, 12:42 PM
  4. Linked Table Append Query Problem.
    By dlab85 in forum Queries
    Replies: 4
    Last Post: 03-13-2013, 07:30 AM
  5. Replies: 1
    Last Post: 12-01-2010, 11:01 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums