Help! Include In SQL statement if record doesnt exist.

[Shaunk23]

[FONT=&quot]I have a database with multiple tables...[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]ExpRecords is the main table.. There is another table called ExpDocsOut which is linked by a “AEC” reference number field.[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]When the user sends the final paperwork to customer he completes a “Docs Out” entry. [/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]Im doing a listbox query which shows all of the documents that NEED to be sent to customer .. I am using SQL statement & specifying if docs out fields for a specific record are empty then include the record.. If docs out has been done it should be omitted from the list. Ideally what I need to be able to write is IF a docs out entry DOESN’T exist for that shipment include the record... How can I write that in my sql statement? Write now im using the [/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]“DocsOUT = "(((ExpDocsOut.BillOfLading)=false OR Isnull(Expdocsout.SentTracking)=true OR Isnull(Expdocsout.sentvia)=true OR Isnull(Expdocsout.senttracking)=true))" which isn’t including all records.. It omits the records in which no docs out has even been done...[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]Private Function DocumentationQuery()[/FONT]
[FONT=&quot]Dim SQL As String[/FONT]
[FONT=&quot]Dim SqlGroup As String[/FONT]
[FONT=&quot]Dim SqlManager As String[/FONT]
[FONT=&quot]Dim SqlAge As String[/FONT]
[FONT=&quot]Dim DocsOUT As String[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]DocsOUT = "(((ExpDocsOut.BillOfLading)=false OR Isnull(Expdocsout.SentTracking)=true OR Isnull(Expdocsout.sentvia)=true OR Isnull(Expdocsout.senttracking)=true))"[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]'' Sets the criteria for SQL query for tracking group[/FONT]
[FONT=&quot] Select Case (Me.ExpOBMQueryGroupCmbo)[/FONT]
[FONT=&quot] Case "All Groups": SqlGroup = "(((ExpRecords.[Tracking Group]) <> [Forms]![ExpOutBoundManager]![ExpOBMQueryGroupCmbo]))"[/FONT]
[FONT=&quot] Case Else: SqlGroup = "(((ExpRecords.[Tracking Group]) = [Forms]![ExpOutBoundManager]![ExpOBMQueryGroupCmbo]))"[/FONT]
[FONT=&quot] End Select[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]'' Sets the criteria for SQL query for Job Manager[/FONT]
[FONT=&quot] Select Case (Me.ExpOBMQueryManagerCmbo)[/FONT]
[FONT=&quot] Case "All Users": SqlManager = "(((ExpRecords.[Job Manager]) <> [Forms]![ExpOutBoundManager]![ExpOBMQueryManagerCmbo]))"[/FONT]
[FONT=&quot] Case Else: SqlManager = "(((ExpRecords.[Job Manager]) = [Forms]![ExpOutBoundManager]![ExpOBMQueryManagerCmbo]))"[/FONT]
[FONT=&quot] End Select[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]'' Sets the criteria for SQL query for Shipment age[/FONT]
[FONT=&quot] Select Case (Me.ExpOBMQueryAgeCmbo)[/FONT]
[FONT=&quot] Case "Current 30 Days": SqlAge = "(((ExpBooking.[DateOfDeparture]) > Date() - 30))"[/FONT]
[FONT=&quot] Case "Current 6 Months": SqlAge = "(((ExpBooking.[DateOfDeparture]) > Date() - 183))"[/FONT]
[FONT=&quot] Case "Current Year": SqlAge = "(((ExpBooking.[DateOfDeparture]) > Date() - 365))"[/FONT]
[FONT=&quot] Case "All Shipments": SqlAge = "(((ExpBooking.[DateOfDeparture]) <> Date()-0))"[/FONT]
[FONT=&quot] End Select[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]'' Actual SQL query to display records for AES REQUIREMENTS using the users criteria from combo boxes.[/FONT]
[FONT=&quot]SQL = "SELECT ExpRecords.AEC, ExpRecords.Shipper, ExpBooking.UltimateCarrierRef AS Booking, ExpRecords.[Aarid Service Type]," & _[/FONT]
[FONT=&quot]" ExpBooking.PortOfLoading AS [Load Port], ExpBooking.PortOfUnloading AS [Unload Port],[ExpBooking].[Dateofarrival]-Date() as [Days Until Required], " & _[/FONT]
[FONT=&quot]" ExpDocsOut.SentDate AS [Date Out], ExpDocsOut.SentTracking AS [Tracking Number]" & _[/FONT]
[FONT=&quot]" FROM (ExpRecords INNER JOIN ExpBooking ON ExpRecords.AEC = ExpBooking.AEC) INNER JOIN ExpDocsOut ON ExpRecords.AEC = ExpDocsOut.aec " & _[/FONT]
[FONT=&quot]" WHERE (" & SqlGroup & "AND" & SqlManager & "AND" & SqlAge & "AND" & DocsOUT & ")"[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]'' Assign's the data to the list & sets format[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] With ExpOBMlist[/FONT]
[FONT=&quot] .RowSource = SQL[/FONT]
[FONT=&quot] .BoundColumn = 1[/FONT]
[FONT=&quot] .ColumnWidths = ColAEC[/FONT]
[FONT=&quot] .ColumnCount = 7[/FONT]
[FONT=&quot] .Requery[/FONT]
[FONT=&quot] End With[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] Call UpdateOBMQueryLbl[/FONT]

 

[sparks80]

Hi,

I need a little bit more information to answer your question fully. Would it be possible to post the full SQL code for your listbox?

For now you have two tables linked by the AEC reference number. By default the link will only display records if a valid record exists in both tables, and matches. Therefore if you have a record in ExpRecords, but not in ExpDocsOut, then this will not be displayed by default.

To include entries where there is no record in ExpDocsOut you will need to use a left join, for example:

SELECT * FROM ExpRecords LEFT JOIN ExpDocsOut ON ExpRecords.AECReferenceNumber = ExpDocsOut.AECReferenceNumber

What type of join are you using between the two tables?

 

[Shaunk23]

Perfect! Worked. I appreciate the assistance!!