Pass through value to SQL multiple table query ...

[angey88dj]

I have two tables I am attempting to query based on user selections. I have very little knowledge of SQL and I am not even sure if this is possible. Any help would be much appreciated!

The tables and columns used are as follows:
Table – ProjListTeam (displays all team members assigned to a specific project)
Column - ProjListID (value = ProjNum column below)
Column - ProjectTeam
Table – ProjList
Column - ProjNum
Column - ProjPhase
Column - ProjSponsor

I have a form that gives users the option to search and limit the data based on 1 or all of the following specific criteria:
• By phase a project is currently in (ProjPhase)
• By who the project sponsor is (ProjSponsor)
• By what team member is assigned (ProjTeam)

I want to be able to pull back only projects in “X” phase with a sponsor of “X” assigned to “X” team member.

 

[dhookom]

There is nothing that describes the members of teams.

Is ProjectTeam the same as team member?


Duane
Hook'D on Access
MS Access MVP

 

[angey88dj]

Yes the ProjectTeam is the same as team member. Only thing to add is that there may be multiple team members assigned. This is why I placed it in it's own table.

 

[dhookom]

Have you tried creating a query based on the two tables with criteria set to the controls on your form?

Duane
Hook'D on Access
MS Access MVP

 

[angey88dj]

Yes I did. It only worked if all of the criteria was selected. If I select 1 or 2 of the 3 options, it doesn't work.

 

[dhookom]

Show us your SQL so we have a chance of helping you correct it.

Duane
Hook'D on Access
MS Access MVP

 

[angey88dj]

This is where I am right now .....

Private Sub btnRunTasksReport_Click()
Dim strSql As String
Dim strSqlWhere As String

strSql = "SELECT ProjList.*, ProjListTeam.* FROM ProjList INNER JOIN ProjListTeam ON ProjList.[ProjNum] = ProjListTeam.[ProjListID];"


If Not "" = cmdTasksbyProj.Value Then
strSqlWhere = "ProjNum = " & cmdTasksbyProj.Value
End If

If Not "" = cmdProjectTeam.Value Then

If Not "" = strSqlWhere Then
strSqlWhere = strSqlWhere + " and "
End If

strSqlWhere = "ProjNum = " & cmdProjectTeam.Value
End If


'DoCmd.RunSQL strSql
'DoCmd.OpenReport( ("SELECT * from ProjList")

End Sub

 

[angey88dj]

Here is the actual SQL I am trying to work with. I was trying to use the VBA above to modify the SQL based on user input. I need to be able to narrow the data by specifying what Phase and/or Sponsor and/or Team member the user inputs. I am not even sure if this is possible. SQL is not my thing ... if you can't already tell

SELECT ProjListTeam.ProjListID, ProjList.ProjNum, ProjList.ProjName, ProjList.BusUnit, ProjList.ProjSponsor, ProjList.ProjLead, ProjList.RequestDate, ProjList.ProjPhase, ProjListTeam.ProjectTeam
FROM ProjList, ProjListTeam
WHERE ProjList.[ProjNum] = ProjListTeam.[ProjListID];

 

[dhookom]

Private Sub btnRunTasksReport_Click()
    Dim strSql As String
    Dim strSqlWhere As String
    
    strSql = "SELECT ProjList.*, ProjListTeam.* " & _
	"FROM ProjList INNER JOIN ProjListTeam ON ProjList.[ProjNum] = ProjListTeam.[ProjListID] " & _
        "WHERE 1=1 "
    
    If Not IsNull(Me.cmdTasksbyProj) Then
    'this code assume ProjNum is numeric
        strSql =  strSQL & " AND ProjNum = " & Me.cmdTasksbyProj
    End If
    
    If Not IsNull(Me.cmdProjectTeam) Then
    'this code assume ProjectTeam is numeric
        strSql = strSQL & " AND ProjectTeam = " & cmdProjectTeam
    End If
    'you need something similar for Phase
    'the next line assumes you want to change the
    '  record source of the current form
    Me.RecordSource = strSQL
    
End Sub

Duane
Hook'D on Access
MS Access MVP