query with Union ALL

[ThaoVy]

Hi,

When I do a search, I would like my form to show all the projects that a person is assigned to + Projects that he/she Performed By. Below is what I have; however, it does not give me any record.

SQLquery = SQLquery & "tblEmployees.LastName "

SQLquery = SQLquery & " ((FROM (tblProjects left JOIN tblProjectBusinessTypes on tblProjects.ProjectNum = tblProjectBusinessTypes.ProjectNum) LEFT JOIN tblEmployees ON tblProjects.AssignedToID = tblEmployees.EmployeeID) UNION ALL FROM (tblProjectBusinessTypes NNER JOIN tblemployees as tblEmployees_1 ON tblemployees_1.EmployeeID = tblProjectBusinessTypes.InstalledBy)"

Thanks so much in advance for looking into this!

 

[PHV]

Correct syntax:
SELECT ... FROM ... WHERE ...
UNION ALL SELECT ... FROM ... WHERE ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ThaoVy]

Hi,

This is what I currently have for this form and it works just fine:

SQLquery = SQLquery & "tblEmployees.LastName "
SQLquery = SQLquery & "FROM (((((tblProjects INNER JOIN tblAccounts ON tblProjects.CustomerID = tblAccounts.CustomerID) left JOIN tblProjectBusinessTypes on tblProjects.ProjectNum = tblProjectBusinessTypes.ProjectNum) left JOIN tblWorkGroups ON tblProjects.WorkGroupID = tblWorkGroups.WorkGroupID) LEFT JOIN tblEmployees ON tblProjects.AssignedToID = tblEmployees.EmployeeID) INNER JOIN tblEmployees as tblEmployees_1 ON tblProjects.RequestedByID = tblEmployees_1.EmployeeID) Inner join tblDeliverables on tblProjects.DeliverableID = tblDeliverables.DeliverableID "


However, I want to modify it so it would show the person with NOT just projects he/she assigned to_tblProjects.AssignedToID, it would also show projects he is performed_tblProjectBusinessTypes.InstalledBy.

I know that I will need to do UNION ALL so I will have all projects the person is assigned to + projects he/she performed.

When I run a sql statement using SELECT ... FROM ... WHERE ...
UNION ALL SELECT ... FROM ... WHERE ... it would give a the data I want. However, I just don't know how to interpret into ACESS Form using SQLquery = SQLquery &...........

 

[PHV]

What is SQLquery ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ThaoVy]

it is a string.

Dim MyDb As Database, MySet As Recordset, SQLquery As String

 

[ThaoVy]

Hello,

I just want to see if anyone would be able to help me to incorporate UNION ALL to the above SQLquery ?

Below is my current SQLqauery and it works just fine.

SQLquery = SQLquery & "tblEmployees.LastName "
SQLquery = SQLquery & "FROM (((((tblProjects INNER JOIN tblAccounts ON tblProjects.CustomerID = tblAccounts.CustomerID) left JOIN tblProjectBusinessTypes on tblProjects.ProjectNum = tblProjectBusinessTypes.ProjectNum) left JOIN tblWorkGroups ON tblProjects.WorkGroupID = tblWorkGroups.WorkGroupID) LEFT JOIN tblEmployees ON tblProjects.AssignedToID = tblEmployees.EmployeeID) INNER JOIN tblEmployees as tblEmployees_1 ON tblProjects.RequestedByID = tblEmployees_1.EmployeeID) Inner join tblDeliverables on tblProjects.DeliverableID = tblDeliverables.DeliverableID "


However, I need to add UNION ALL to this SQLquery so it would show the person with NOT just projects he/she assigned to_tblProjects.AssignedToID, it would also show projects he is performed_tblProjectBusinessTypes.InstalledBy.

Thanks so much.

 

[Remou]

You need to answer the question: what is SQLquery? That is, what does SQLquery contain?

 

[ThaoVy]

Hi,

I have it as
it is a string.

Dim MyDb As Database, MySet As Recordset, SQLquery As String. Is it what you need? Thanks

 

[Remou]

You say:

SQLquery = SQLquery & "tblEmployees.LastName "

This will not work as a query, so what is SQLquery equal to?

for example

SQLquery ="SELECT * From ????

 

[ThaoVy]

In my form under search by employee last name button,I have something like this for a Event Procedure:

Private Sub B2_Click()
On Error GoTo B2_Click_Error
Me![FLastName] = ""

it would display data based where tblProjects.AssignedToID = tblEmployees.EmployeeID ........... I would need it also display based where tblProjectBusinessTypes.InstalledBy = tblEmployees_2.EmployeeID.

 

[ca8msm]

Is this really so hard to understand? You need to provide the full SQL statement so people can see what it looks like. It will start with SELECT, then it will have a FROM...



____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[ThaoVy]

I am so sorry for the confusions. Hope the below would help. Also, I am so sorry for the long queries.

Private Sub Find_Click()
On Error GoTo Find_Click_Error
'_____________________
'| |
'| Find Activity |
'|___________________|

Dim MyDb As Database, MySet As Recordset, SQLquery As String

Me![FindList].RowSource = ""
If (IsNull(Me!FLastName) And Me!OpenProjectsOnly = False And Me!OpenAndActive = False And IsNull(Me!InDate) And IsNull(Me!EndDate) And IsNull(Me!CustomerName) And IsNull(Me!FProjectNum) And IsNull(Me!WorkGroup) And IsNull(Me!Deliverable) And IsNull(Me!Requester)) Or (Me!FLastName = "" And Me!InDate = "" And Me!EndDate = "" And Me!CustomerName = "" And Me!FProjectNum = "" And Me!WorkGroup = "" And Me!Deliverable = "" And Me!Requester = "") Then

MsgBox "Your search is too general, please specify a criteria."
Else
SQLquery = "SELECT DISTINCT tblProjects.ProjectNum, "
SQLquery = SQLquery & "tblProjects.EngReceivedDate as [RecvdDate], "
SQLquery = SQLquery & "tblProjects.ProjectCompletedDate as [CompletedDate], "
SQLquery = SQLquery & "tblProjects.ProjectNum as [Project#], "
SQLquery = SQLquery & "tblAccounts.CustomerName, "
SQLquery = SQLquery & "tblWorkgroups.WorkGroupName as [WorkGroup], "
SQLquery = SQLquery & "tblEmployees.LastName "
SQLquery = SQLquery & "FROM (((((tblProjects INNER JOIN tblAccounts ON tblProjects.CustomerID = tblAccounts.CustomerID) left JOIN tblProjectBusinessTypes on tblProjects.ProjectNum = tblProjectBusinessTypes.ProjectNum) left JOIN tblWorkGroups ON tblProjects.WorkGroupID = tblWorkGroups.WorkGroupID) LEFT JOIN tblEmployees ON tblProjects.AssignedToID = tblEmployees.EmployeeID) INNER JOIN tblEmployees as tblEmployees_1 ON tblProjects.RequestedByID = tblEmployees_1.EmployeeID) Inner join tblDeliverables on tblProjects.DeliverableID = tblDeliverables.DeliverableID " '& "OR UNION ALL FROM (tblEmployees as tblEmployees_2 INNER JOIN tblProjectBusinessTypes ON tblEmployees_2.EmployeeID = tblProjectBusinessTypes.InstalledBy)"

Me!Feedback.Caption = "Searching"
SQLquery = SQLquery & "Where tblAccounts.CustomerName Like '" & Me!CustomerName & "*' "
If Not (IsNull(Me!WorkGroup) Or Me!WorkGroup = "") Then
SQLquery = SQLquery & "AND tblWorkGroups.WorkGroupName Like '" & Me!WorkGroup & "*' "
End If
If Not (IsNull(Me!Requester) Or Me!Requester = "") Then
SQLquery = SQLquery & "AND tblEmployees_1.LastName Like '" & Me!Requester & "*' "
End If
If Not (IsNull(Me!Deliverable) Or Me!Deliverable = "") Then
SQLquery = SQLquery & "AND tblDeliverables.DeliverableName Like '" & Me!Deliverable & "*' "
End If
If Not (IsNull(Me!FLastName) Or Me!FLastName = "") Then
SQLquery = SQLquery & "AND tblEmployees.LastName Like '" & Me!FLastName & "*' "
End If
If Me!OpenProjectsOnly = True Then
SQLquery = SQLquery & " AND tblProjects.ProjectCompletedDate is Null " & " AND tblProjectBusinessTypes.CompletedDate is Null "
End If
If Me!OpenAndActive = True Then
SQLquery = SQLquery & " AND Not IsNull (tblProjectBusinessTypes.BillRecvdDate) " & "AND tblProjects.ProjectCompletedDate is Null "
End If
SQLquery = SQLquery & "AND tblProjects.ProjectNum Like '" & Me!FProjectNum & "*' "
If Not IsNull(Me!InDate) And Not IsNull(Me!EndDate) And Me!InDate <> "" And Me!EndDate <> "" Then
SQLquery = SQLquery & "AND tblProjects.EngReceivedDate Between #" & Me!InDate & "# and #" & Me!EndDate & "# "
End If

 

[ThaoVy]

Hi,

Hope this one would clarify a little bit.

Below is my codes for Microsoft Visual Basic:

Dim SQLquery as String

SQLquery = "SELECT DISTINCT tblProjects.ProjectNum, "

SQLquery = SQLquery & "tblProjects.EngReceivedDate as RecvdDate], "

SQLquery = SQLquery & "tblProjects.ProjectCompletedDate as [CompletedDate], "

SQLquery = SQLquery & "tblProjects.ProjectNum as [Project#], "

SQLquery = SQLquery & "tblAccounts.CustomerName, "

SQLquery = SQLquery & "tblWorkgroups.WorkGroupName as [WorkGroup], "

SQLquery = SQLquery & "tblEmployees.LastName "
SQLquery = SQLquery & "FROM (((((tblProjects INNER JOIN tblAccounts ON tblProjects.CustomerID = tblAccounts.CustomerID) left JOIN tblProjectBusinessTypes on tblProjects.ProjectNum = tblProjectBusinessTypes.ProjectNum) left JOIN tblWorkGroups ON tblProjects.WorkGroupID = tblWorkGroups.WorkGroupID) LEFT JOIN tblEmployees ON tblProjects.AssignedToID = tblEmployees.EmployeeID) INNER JOIN tblEmployees as tblEmployees_1 ON tblProjects.RequestedByID = tblEmployees_1.EmployeeID) Inner join tblDeliverables on tblProjects.DeliverableID = tblDeliverables.DeliverableID "

Up here it works just fine. However, as I mentioned, I also need it shows not just the tblEmployees.LastName where tblProjects.AssignedToID = tblEmployees.EmployeeID; it needs to also show Employees.LastName where tblEmployees_2.EmployeeID = tblProjectBusinessTypes.InstalledBy.

Therefore, I modified the codes as follow:


SQLquery = SQLquery & "tblEmployees.LastName "
SQLquery = SQLquery & "FROM (((((tblProjects INNER JOIN tblAccounts ON tblProjects.CustomerID = tblAccounts.CustomerID) left JOIN tblProjectBusinessTypes on tblProjects.ProjectNum = tblProjectBusinessTypes.ProjectNum) left JOIN tblWorkGroups ON tblProjects.WorkGroupID = tblWorkGroups.WorkGroupID) LEFT JOIN tblEmployees ON tblProjects.AssignedToID = tblEmployees.EmployeeID) INNER JOIN tblEmployees as tblEmployees_1 ON tblProjects.RequestedByID = tblEmployees_1.EmployeeID) Inner join tblDeliverables on tblProjects.DeliverableID = tblDeliverables.DeliverableID "

SQLquery = SQLquery & "OR FROM (tblEmployees as tblEmployees_2 INNER JOIN tblProjectBusinessTypes ON tblEmployees_2.EmployeeID = tblProjectBusinessTypes.InstalledBy) "


However, when I tried to search for a person, it did not give me any record.

Any ideas is greatly appreciated.

 

[Remou]

Try this:

[tt]SQLquery = "SELECT tblProjects.ProjectNum, "

SQLquery = SQLquery & "tblProjects.EngReceivedDate as [RecvdDate], "

SQLquery = SQLquery & "tblProjects.ProjectCompletedDate as [CompletedDate], "

SQLquery = SQLquery & "tblProjects.ProjectNum as [Project#], "

SQLquery = SQLquery & "tblAccounts.CustomerName, "

SQLquery = SQLquery & "tblWorkgroups.WorkGroupName as [WorkGroup], "

SQLquery = SQLquery & "tblEmployees.LastName "
SQLquery = SQLquery & "FROM (((((tblProjects INNER JOIN tblAccounts ON tblProjects.CustomerID = tblAccounts.CustomerID) left JOIN tblProjectBusinessTypes on tblProjects.ProjectNum = tblProjectBusinessTypes.ProjectNum) left JOIN tblWorkGroups ON tblProjects.WorkGroupID = tblWorkGroups.WorkGroupID) LEFT JOIN tblEmployees ON tblProjects.AssignedToID = tblEmployees.EmployeeID) INNER JOIN tblEmployees as tblEmployees_1 ON tblProjects.RequestedByID = tblEmployees_1.EmployeeID) Inner join tblDeliverables on tblProjects.DeliverableID = tblDeliverables.DeliverableID "



SQLQuery = SQLquery & SQLquery = "UNION SELECT tblProjects.ProjectNum, "

SQLquery = SQLquery & "tblProjects.EngReceivedDate as RecvdDate], "

SQLquery = SQLquery & "tblProjects.ProjectCompletedDate as [CompletedDate], "

SQLquery = SQLquery & "tblProjects.ProjectNum as [Project#], "

SQLquery = SQLquery & "tblAccounts.CustomerName, "

SQLquery = SQLquery & "tblWorkgroups.WorkGroupName as [WorkGroup], "

SQLquery = SQLquery & "tblEmployees.LastName "
SQLquery = SQLquery & "FROM (((((tblProjects INNER JOIN tblAccounts ON tblProjects.CustomerID = tblAccounts.CustomerID) left JOIN tblProjectBusinessTypes on tblProjects.ProjectNum = tblProjectBusinessTypes.ProjectNum) left JOIN tblWorkGroups ON tblProjects.WorkGroupID = tblWorkGroups.WorkGroupID) LEFT JOIN tblEmployees ON tblProjects.AssignedToID = tblEmployees.EmployeeID) INNER JOIN tblEmployees as tblEmployees_1 ON tblProjectBusinessTypes.InstalledBy = tblEmployees_1.EmployeeID) Inner join tblDeliverables on tblProjects.DeliverableID = tblDeliverables.DeliverableID"[/tt]

It could do with some tidying.