HELP!!! Union Query - Adding IF where condition

[jdraw]

You may get some ideas from the various Logistics data models at

http://www.databaseanswers.org/data_models/index.htm

 

[Shaunk23]

Could i include in my SQL Statements a "Convert" ??? Maybe convert that date (Whether it be sailing / transhipment or arrival ) to a general "date" ??? is that possible?

 

[Shaunk23]

I thought Union Queries Could have different "Fields" just had to be same datatype & the same number of fields in each SQL ??

 

[MSAccessRookie]

I thought Union Queries Could have different "Fields" just had to be same datatype & the same number of fields in each SQL ??

While this can be true (depending on the situation, the Best Practice is to have all of the Fields in the Union be exactly the same as all of their Peers in regards to FieldName and FieldType. You may need to verify that this is the case, and correct any instance where it is not.

Making The FieldName the same can be accomplished using Field Aliases in the SELECT Statements.

Making The FieldType the same can be accomplished using an appropriate Conversion Function in the SELECT Statements that convert all Peer Field Values to the same type.

-- Rookie

 

[Shaunk23]

Can you give me example of the "Field Aliases" how i could set that up in SQL statement.

 

[Shaunk23]

Alright Guys... I GOT IT! Finally... This is working with testing. Does what i need it to do atleast. Here is the last remaining part... I need to order these by "Days" which is an expression i guess. because in each statement it uses a different date i cant order by a date.. Can you do this?? Here is working code..


Private Function TrackingMonitorQuery()
Dim SQL1 As String
Dim SQL2 As String
Dim SQL3 As String
Dim SQL4 As String
Dim SQL5 As String
Dim SQL6 As String
Dim SQL7 As String
Dim SQL8 As String
Dim SQL9 As String
Dim SQL10 As String
Dim SQL11 As String
Dim WSQL1 As String
Dim WSQL2 As String
Dim WSQL3 As String
Dim WSQL4 As String
Dim WSQL5 As String
Dim WSQL6 As String
Dim WSQL7 As String
Dim WSQL8 As String
Dim WSQL9 As String
Dim WSQL10 As String
Dim WSQL11 As String
Dim WholeSQL As String
Dim SqlGroup As String
Dim SqlManager As String
Dim SQLDaterange As String
Dim SqlAge As String
Dim SQLLINESHORTCODE As String


'' Sets the criteria for SQL query for tracking group
Select Case (Me.ExpOBMQueryGroupCmbo)
Case "All Groups": SqlGroup = "(((ExpRecords.[Tracking Group]) <> [Forms]![ExpOutBoundManager]![ExpOBMQueryGroupCmbo]))"
Case Else: SqlGroup = "(((ExpRecords.[Tracking Group]) = [Forms]![ExpOutBoundManager]![ExpOBMQueryGroupCmbo]))"
End Select

'' Sets the criteria for SQL query for Job Manager
Select Case (Me.ExpOBMQueryManagerCmbo)
Case "All Users": SqlManager = "(((ExpRecords.[Job Manager]) <> [Forms]![ExpOutBoundManager]![ExpOBMQueryManagerCmbo]))"
Case Else: SqlManager = "(((ExpRecords.[Job Manager]) = [Forms]![ExpOutBoundManager]![ExpOBMQueryManagerCmbo]))"
End Select

'' Sets the criteria for SQL query for Shipment age
'Select Case (Me.ExpOBMQueryAgeCmbo)
'Case "Current 30 Days": SqlAge = "(((ExpBooking.[DateOfDeparture]) > Date() - 30))"
'Case "Current 6 Months": SqlAge = "(((ExpBooking.[DateOfDeparture]) > Date() - 183))"
'Case "Current Year": SqlAge = "(((ExpBooking.[DateOfDeparture]) > Date() - 365))"
'Case "All Shipments": SqlAge = "(((ExpBooking.[DateOfDeparture]) <> Date()-0))"
'End Select

'' Sets General Date range for traffic monitor going 20 days forward & 60 days back.
SQLDaterange = "((([ExpBooking].[DateOfDeparture]) < Date() + 20 And ([ExpBooking].[DateOfDeparture]) > Date() - 60))"

'' Sets the scac code for carrier.
SQLLINESHORTCODE = "(([ExpCarriers].[Location Name]=[ExpBooking]![UltimateCarrier]))"



'' SETTING THE WHERE CRITERIA FOR EACH OF THE SELECT STATEMENTS.
WSQL1 = " WHERE (" & SqlGroup & "AND" & SqlManager & "AND" & SQLDaterange & "AND" & SQLLINESHORTCODE & "AND" & "([Expbooking].[DateOfDeparture] Is Not Null)" & "AND" & "((IIf([ExpBooking].[SailedConfirm]=-1,'Yes','No'))= 'No')" & ")"
WSQL2 = " WHERE (" & SqlGroup & "AND" & SqlManager & "AND" & SQLDaterange & "AND" & SQLLINESHORTCODE & "AND" & "([Expbooking].[DateOfArrival] Is Not Null)" & "AND" & "((IIf([ExpBooking].[ArrivedPortConfirm]=-1,'Yes','No'))= 'No')" & ")"
WSQL3 = " WHERE (" & SqlGroup & "AND" & SqlManager & "AND" & SQLDaterange & "AND" & SQLLINESHORTCODE & "AND" & "([Expbooking].[OnCarriageDate] Is Not Null)" & "AND" & "((IIf([ExpBooking].[FinalArriveConfirm]=-1,'Yes','No'))= 'No')" & ")"
WSQL4 = " WHERE (" & SqlGroup & "AND" & SqlManager & "AND" & SQLDaterange & "AND" & SQLLINESHORTCODE & "AND" & "([Expbooking].[Transhipment1Event] Is Not Null)" & "AND" & "((IIf([ExpBooking].[Transhipment1Confirm]=-1,'Yes','No'))= 'No')" & ")"
WSQL5 = " WHERE (" & SqlGroup & "AND" & SqlManager & "AND" & SQLDaterange & "AND" & SQLLINESHORTCODE & "AND" & "([Expbooking].[Transhipment2Event] Is Not Null)" & "AND" & "((IIf([ExpBooking].[Transhipment2Confirm]=-1,'Yes','No'))= 'No')" & ")"
WSQL6 = " WHERE (" & SqlGroup & "AND" & SqlManager & "AND" & SQLDaterange & "AND" & SQLLINESHORTCODE & "AND" & "([Expbooking].[Transhipment3Event] Is Not Null)" & "AND" & "((IIf([ExpBooking].[Transhipment3Confirm]=-1,'Yes','No'))= 'No')" & ")"
WSQL7 = " WHERE (" & SqlGroup & "AND" & SqlManager & "AND" & SQLDaterange & "AND" & SQLLINESHORTCODE & "AND" & "([Expbooking].[Transhipment4Event] Is Not Null)" & "AND" & "((IIf([ExpBooking].[Transhipment4Confirm]=-1,'Yes','No'))= 'No')" & ")"
WSQL8 = " WHERE (" & SqlGroup & "AND" & SqlManager & "AND" & SQLDaterange & "AND" & SQLLINESHORTCODE & "AND" & "([Expbooking].[Transhipment5Event] Is Not Null)" & "AND" & "((IIf([ExpBooking].[Transhipment5Confirm]=-1,'Yes','No'))= 'No')" & ")"
WSQL9 = " WHERE (" & SqlGroup & "AND" & SqlManager & "AND" & SQLDaterange & "AND" & SQLLINESHORTCODE & "AND" & "([Expbooking].[Transhipment6Event] Is Not Null)" & "AND" & "((IIf([ExpBooking].[Transhipment6Confirm]=-1,'Yes','No'))= 'No')" & ")"
WSQL10 = " WHERE (" & SqlGroup & "AND" & SqlManager & "AND" & SQLDaterange & "AND" & SQLLINESHORTCODE & "AND" & "([Expbooking].[Transhipment7Event] Is Not Null)" & "AND" & "((IIf([ExpBooking].[Transhipment7Confirm]=-1,'Yes','No'))= 'No')" & ")"
WSQL11 = " WHERE (" & SqlGroup & "AND" & SqlManager & "AND" & SQLDaterange & "AND" & SQLLINESHORTCODE & "AND" & "([Expbooking].[Transhipment8Event] Is Not Null)" & "AND" & "((IIf([ExpBooking].[Transhipment8Confirm]=-1,'Yes','No'))= 'No')" & ")"


'' Depart Actual Sailing Date
SQL1 = " SELECT ExpRecords.AEC, ExpRecords.Shipper, ExpCarriers.ShortCode AS Line," & _
" [ExpBooking].[QuantityOfEquip] & ' x ' & [ExpBooking].[EquipmentType] AS Equipment, 'Sail On Board' & ' ' & [ExpBooking].[VesselName] & ' ' & [ExpBooking].[VesselVoy] AS [Event Description]," & _
" ExpBooking.DateOfDeparture AS [Event Date],ExpBooking.DateOfDeparture-Date() AS Days, IIf([SailedConfirm]=-1,'Yes','No') AS Confirm " & _
" FROM ExpCarriers,(ExpRecords INNER JOIN ExpBooking ON ExpRecords.AEC = ExpBooking.AEC) INNER JOIN ExpDockRec ON ExpRecords.AEC = ExpDockRec.AEC "


'' Arrive final Port Of Discharge
SQL2 = " SELECT ExpRecords.AEC, ExpRecords.Shipper, ExpCarriers.ShortCode AS Line, " & _
"[ExpBooking].[QuantityOfEquip] & ' x ' & [ExpBooking].[EquipmentType] AS Equipment,'Arrive Discharge Port:' & ' ' & [ExpBooking]![PortOfUnloading] AS [Event Description]," & _
" ExpBooking.[DateOfArrival] AS [Event Date], ExpBooking.DateOfArrival-Date() AS Days,IIf([ArrivedPortConfirm]=-1,'Yes','No') AS Confirm " & _
" FROM ExpCarriers,(ExpRecords INNER JOIN ExpBooking ON ExpRecords.AEC = ExpBooking.AEC) INNER JOIN ExpDockRec ON ExpRecords.AEC = ExpDockRec.AEC "


'' Sql To Add records to Traffic Monitor for tracking based on oncarriage delivery date.
SQL3 = " SELECT ExpRecords.AEC, ExpRecords.Shipper, ExpCarriers.ShortCode AS Line," & _
" [ExpBooking].[QuantityOfEquip] & ' x ' & [ExpBooking].[EquipmentType] AS Equipment, 'Arrive Final OnCarriage Point:' & ' ' & [ExpBooking].[OnCarriagePoint] AS [Event Description]," & _
" ExpBooking.OnCarriageDate AS [Event Date],ExpBooking.OnCarriageDate-Date() AS Days,IIf([FinalArriveConfirm],'Yes','No') AS Confirm" & _
" FROM ExpCarriers,(ExpRecords INNER JOIN ExpBooking ON ExpRecords.AEC = ExpBooking.AEC) INNER JOIN ExpDockRec ON ExpRecords.AEC = ExpDockRec.AEC "


'' Sql To Add records to Traffic Monitor for tracking based on Transhipment point1
SQL4 = " SELECT ExpRecords.AEC, ExpRecords.Shipper, ExpCarriers.ShortCode AS Line," & _
" [ExpBooking].[QuantityOfEquip] & ' x ' & [ExpBooking].[EquipmentType] AS Equipment, ExpBooking.Transhipment1Event AS [Event Description] ," & _
" ExpBooking.Transhipment1Date AS [Event Date],ExpBooking.Transhipment1Date-Date() AS Days,IIf([Transhipment1Confirm],'Yes','No') AS Confirm" & _
" FROM ExpCarriers,(ExpRecords INNER JOIN ExpBooking ON ExpRecords.AEC = ExpBooking.AEC) INNER JOIN ExpDockRec ON ExpRecords.AEC = ExpDockRec.AEC "

'' Sql To Add records to Traffic Monitor for tracking based on Transhipment point1
SQL5 = " SELECT ExpRecords.AEC, ExpRecords.Shipper, ExpCarriers.ShortCode AS Line," & _
" [ExpBooking].[QuantityOfEquip] & ' x ' & [ExpBooking].[EquipmentType] AS Equipment, ExpBooking.Transhipment2Event AS [Event Description] ," & _
" ExpBooking.Transhipment2Date AS [Event Date],ExpBooking.Transhipment2Date-Date() AS Days,IIf([Transhipment2Confirm],'Yes','No') AS Confirm" & _
" FROM ExpCarriers,(ExpRecords INNER JOIN ExpBooking ON ExpRecords.AEC = ExpBooking.AEC) INNER JOIN ExpDockRec ON ExpRecords.AEC = ExpDockRec.AEC "

'' Sql To Add records to Traffic Monitor for tracking based on Transhipment point1
SQL6 = " SELECT ExpRecords.AEC, ExpRecords.Shipper, ExpCarriers.ShortCode AS Line," & _
" [ExpBooking].[QuantityOfEquip] & ' x ' & [ExpBooking].[EquipmentType] AS Equipment, ExpBooking.Transhipment3Event AS [Event Description] ," & _
" ExpBooking.Transhipment3Date AS [Event Date],ExpBooking.Transhipment3Date-Date() AS Days,IIf([Transhipment3Confirm],'Yes','No') AS Confirm" & _
" FROM ExpCarriers,(ExpRecords INNER JOIN ExpBooking ON ExpRecords.AEC = ExpBooking.AEC) INNER JOIN ExpDockRec ON ExpRecords.AEC = ExpDockRec.AEC "

'' Sql To Add records to Traffic Monitor for tracking based on Transhipment point1
SQL7 = " SELECT ExpRecords.AEC, ExpRecords.Shipper, ExpCarriers.ShortCode AS Line," & _
" [ExpBooking].[QuantityOfEquip] & ' x ' & [ExpBooking].[EquipmentType] AS Equipment, ExpBooking.Transhipment4Event AS [Event Description] ," & _
" ExpBooking.Transhipment4Date AS [Event Date],ExpBooking.Transhipment4Date-Date() AS Days,IIf([Transhipment4Confirm],'Yes','No') AS Confirm" & _
" FROM ExpCarriers,(ExpRecords INNER JOIN ExpBooking ON ExpRecords.AEC = ExpBooking.AEC) INNER JOIN ExpDockRec ON ExpRecords.AEC = ExpDockRec.AEC "

'' Sql To Add records to Traffic Monitor for tracking based on Transhipment point1
SQL11 = " SELECT ExpRecords.AEC, ExpRecords.Shipper, ExpCarriers.ShortCode AS Line," & _
" [ExpBooking].[QuantityOfEquip] & ' x ' & [ExpBooking].[EquipmentType] AS Equipment, ExpBooking.Transhipment5Event AS [Event Description] ," & _
" ExpBooking.Transhipment5Date AS [Event Date],ExpBooking.Transhipment5Date-Date() AS Days,IIf([Transhipment5Confirm],'Yes','No') AS Confirm" & _
" FROM ExpCarriers,(ExpRecords INNER JOIN ExpBooking ON ExpRecords.AEC = ExpBooking.AEC) INNER JOIN ExpDockRec ON ExpRecords.AEC = ExpDockRec.AEC "


'' Sql To Add records to Traffic Monitor for tracking based on Transhipment point1
SQL8 = " SELECT ExpRecords.AEC, ExpRecords.Shipper, ExpCarriers.ShortCode AS Line," & _
" [ExpBooking].[QuantityOfEquip] & ' x ' & [ExpBooking].[EquipmentType] AS Equipment, ExpBooking.Transhipment6Event AS [Event Description] ," & _
" ExpBooking.Transhipment6Date AS [Event Date],ExpBooking.Transhipment6Date-Date() AS Days,IIf([Transhipment6Confirm],'Yes','No') AS Confirm" & _
" FROM ExpCarriers,(ExpRecords INNER JOIN ExpBooking ON ExpRecords.AEC = ExpBooking.AEC) INNER JOIN ExpDockRec ON ExpRecords.AEC = ExpDockRec.AEC "

'' Sql To Add records to Traffic Monitor for tracking based on Transhipment point1
SQL9 = " SELECT ExpRecords.AEC, ExpRecords.Shipper, ExpCarriers.ShortCode AS Line," & _
" [ExpBooking].[QuantityOfEquip] & ' x ' & [ExpBooking].[EquipmentType] AS Equipment, ExpBooking.Transhipment7Event AS [Event Description] ," & _
" ExpBooking.Transhipment7Date AS [Event Date],ExpBooking.Transhipment7Date-Date() AS Days,IIf([Transhipment7Confirm],'Yes','No') AS Confirm" & _
" FROM ExpCarriers,(ExpRecords INNER JOIN ExpBooking ON ExpRecords.AEC = ExpBooking.AEC) INNER JOIN ExpDockRec ON ExpRecords.AEC = ExpDockRec.AEC "

'' Sql To Add records to Traffic Monitor for tracking based on Transhipment point1
SQL10 = " SELECT ExpRecords.AEC, ExpRecords.Shipper, ExpCarriers.ShortCode AS Line," & _
" [ExpBooking].[QuantityOfEquip] & ' x ' & [ExpBooking].[EquipmentType] AS Equipment, ExpBooking.Transhipment8Event AS [Event Description] ," & _
" ExpBooking.Transhipment8Date AS [Event Date],ExpBooking.Transhipment8Date-Date() AS Days,IIf([Transhipment8Confirm],'Yes','No') AS Confirm" & _
" FROM ExpCarriers,(ExpRecords INNER JOIN ExpBooking ON ExpRecords.AEC = ExpBooking.AEC) INNER JOIN ExpDockRec ON ExpRecords.AEC = ExpDockRec.AEC "



Debug.Print WSQL1



''Combines Above SQL Queries with Union.
WholeSQL = SQL1 & WSQL1 & vbCrLf & " Union " & vbCrLf & SQL2 & WSQL2 & vbCrLf & " Union " & vbCrLf & SQL3 & WSQL3 & _
vbCrLf & " Union " & vbCrLf & SQL4 & WSQL4 & vbCrLf & " Union " & vbCrLf & SQL5 & WSQL5 & vbCrLf & " Union " & vbCrLf & SQL6 & WSQL6 & _
vbCrLf & " Union " & vbCrLf & SQL7 & WSQL7 & vbCrLf & " Union " & vbCrLf & SQL8 & WSQL8 & vbCrLf & " Union " & vbCrLf & SQL9 & WSQL9 & _
vbCrLf & " Union " & vbCrLf & SQL10 & WSQL10 & vbCrLf & " Union " & vbCrLf & SQL11 & WSQL11
'Debug.Print WholeSQL

''Assign's the data to the list & sets format
With ExpOBMlist
.RowSource = WholeSQL
.BoundColumn = 1
.ColumnWidths = ColAEC & ";" & 3400 & ";" & 600 & ";" & 2350 & ";" & 5000 & ";" & 1100 & ";" & 550 & ";" & 800
.ColumnCount = 8
.Requery
End With

Call UpdateOBMQueryLbl