Freelance Project Requests info@CodeDocu.de Software Development in C# WPF Asp.Net Core Vba Excel Word SQL-Server EF Linq, UWP Net
#

Download:

Datei 1: Demo_SubQuery.zip

MS Access: Subquery

 

A subquery is a query of data, whereby only the rows of a main query are accepted, in which an ID number of the main query also occurs in another table or query.

Sub-queries are quite complex and therefore have to be created partially in SQL as text directly.

 

Design:

Subqueries can not be created with the Design Designer, or even parts of it. One must write dynamic sub-queries as a rule with SQL.

The main query can be created in the design designer including the main filter, but since the subquery texts are in the criteria, you will only find SQL syntax as text.
 

 
The SQL text of the subquery can be viewed, copied and tested in the filter line with zoom, and can be designed, tested and checked in another query using SQL.
However, a direct drafting assistant for the subquery is not available.

 
Logic of the subquery:
The logic of a subquery is always to create a SELECTFields FROM Table_Main, and in the WHERE conditions, to filter an ID field of the main table with an ID field of the subquery,
Where the syntax is IDSub IN (SELECT IDSub in Table_Sub).
The subquery can refer to an occurrence of the ID number in the main results, whereby exactly this same ID number must also occur in the subquery.

 
Example:
The example here shows a product table, which is filtered by a filter field at the side.
The results, which are already filtered with "Radar" and "Currently", are reduced again with a query, whereby the ID number must also occur in a property table.
Show all products whose IDs also occur in a property query

 
 
The subquery is appended to the main query with the syntax IDProduct IN (SELECT IDProduct FROM tblx)


'--< Unterabfrage >--
sSQL_Funktionen = sSQL_Funktionen & " " & cbxOrAnd_Funktionen & " IDProdukt IN (SELECT IDProdukt"
 
sSQL_Funktionen = sSQL_Funktionen & " FROM tbl_Produkte_Funktionen INNER JOIN tblBase_Funktionen ON tbl_Produkte_Funktionen.IDBase_Funktion = tblBase_Funktionen.IDBase_Funktion"
 
sSQL_Funktionen = sSQL_Funktionen & " WHERE Funktion LIKE '*" & sWort & "*')"
'--</ Unterabfrage >--
 

 
The pre-created main query corresponds to the regular structure of an SQL query with SELECT Fields FROM Table

'--< SQL Basis >--
Dim sSQL As String
'fields
sSQL = "SELECT IDProdukt, Produktlinie, Komponenten, Pilotproject, MaturityLevel_00, MaturityLevel_10, MaturityLevel_20, MaturityLevel_30, Kommentar"
'Table
sSQL = sSQL & " FROM tbl_Produkte"
'--</ SQL Basis >--
 

 
 
Finally, the SQL text is written to the row source of a list control or a combo selection or to the recordsource of a form.
This will display the results automatically.

ctlListe_Produkte.RowSource = sSQL
 

 
 
Complete vba code to create a dynamic SQL query as text
Vba Visual Basic for Applications

Option Compare Database
Option Explicit On
 
 
'===================< Form >======================
Private Sub Form_Open(Cancel As Integer)
fxRefresh_List()
End Sub
'===================</ Form >======================
 
 
 
 
 
'===================< Controls >======================
Private Sub ctlListe_Produkte_AfterUpdate()
'-------------< ctlListe_Produkte_AfterUpdate() >-------------
Requery
ctlListe_Produkte.SetFocus
UF_Funktionen.Requery
'-------------</ ctlListe_Produkte_AfterUpdate() >-------------
End Sub
 
 
 
Private Sub tbxSuche_Produkte_KeyUp(KeyCode As Integer, Shift As Integer)
On Error Resume Next
fxRefresh_List tbxSuche_Produkte.Text, "Produkte"
End Sub
 
Private Sub tbxSuche_Funktionen_KeyUp(KeyCode As Integer, Shift As Integer)
On Error Resume Next
fxRefresh_List tbxSuche_Funktionen.Text, "Funktionen"
End Sub
 
Private Sub cbxOrAnd_Funktionen_AfterUpdate()
fxRefresh_List()
End Sub
'===================</ Controls >======================
 
 
 
 
'===================< Funktion >======================
Private Sub fxRefresh_List(Optional ByVal parSuchText, Optional ByVal parFeld)
'---------------< fxRefresh_List() >---------------
'*aktualisiere die Trefferliste oben
'< init >
Dim sSuchText_Produkt As String
Dim sSuchText_Funktionen As String
 
If IsMissing(parFeld) Then parFeld = ""
 
If parFeld Like "Produkte" Then
sSuchText_Produkt = parSuchText
sSuchText_Funktionen = Nz(tbxSuche_Funktionen.Value, "")
ElseIf parFeld Like "Funktionen" Then
sSuchText_Produkt = Nz(tbxSuche_Produkte.Value, "")
sSuchText_Funktionen = parSuchText
Else
sSuchText_Produkt = Nz(tbxSuche_Produkte.Value, "")
sSuchText_Funktionen = Nz(tbxSuche_Funktionen.Value, "")
End If
'</ init >
 
 
'--< SQL Basis >--
Dim sSQL As String
'fields
sSQL = "SELECT IDProdukt, Produktlinie, Komponenten, Pilotproject, MaturityLevel_00, MaturityLevel_10, MaturityLevel_20, MaturityLevel_30, Kommentar"
'Table
sSQL = sSQL & " FROM tbl_Produkte"
'--</ SQL Basis >--
 
Dim arrWorte
Dim varWort
Dim sWort As String
 
'------< SQL Produkte >------
If Not sSuchText_Produkt = "" Then
Dim sSQL_Produkte As String
sSQL_Produkte = " ( "
 
'----< @Loop: Wortfilter >----
arrWorte = Split(sSuchText_Produkt, " ")
 
For Each varWort In arrWorte
If Not varWort = "" Then
sWort = varWort
'--< direkte Filter >--
sSQL_Produkte = sSQL_Produkte & " AND ( [Produktlinie] & [Komponenten] & Pilotproject & MaturityLevel_00 & MaturityLevel_10 & MaturityLevel_20 & MaturityLevel_30 & Kommentar LIKE '*" & sWort & "*' )"
'--</ direkte Filter >--
End If
Next
'----</ @Loop: Wortfilter >----
 
'< Korrektur >
sSQL_Produkte = Replace(sSQL_Produkte, " AND ", " ", 1, 1, vbTextCompare)
sSQL_Produkte = sSQL_Produkte & " ) "
'</ Korrektur >
 
End If
'------</ SQL Produkte >------
 
 
'------< SQL Funktionen >------
If Not sSuchText_Funktionen = "" Then
Dim sSQL_Funktionen As String
sSQL_Funktionen = " ( "
 
'----< @Loop: Wortfilter >----
arrWorte = Split(sSuchText_Funktionen, " ")
 
For Each varWort In arrWorte
If Not varWort = "" Then
sWort = varWort
'--< Unterabfrage >--
sSQL_Funktionen = sSQL_Funktionen & " " & cbxOrAnd_Funktionen & " IDProdukt IN (SELECT IDProdukt"
sSQL_Funktionen = sSQL_Funktionen & " FROM tbl_Produkte_Funktionen INNER JOIN tblBase_Funktionen ON tbl_Produkte_Funktionen.IDBase_Funktion = tblBase_Funktionen.IDBase_Funktion"
sSQL_Funktionen = sSQL_Funktionen & " WHERE Funktion LIKE '*" & sWort & "*')"
'--</ Unterabfrage >--
End If
Next
'----</ @Loop: Wortfilter >----
 
'< Korrektur >
sSQL_Funktionen = Replace(sSQL_Funktionen, " " & cbxOrAnd_Funktionen & " ", " ", 1, 1, vbTextCompare)
sSQL_Funktionen = sSQL_Funktionen & " ) "
'</ Korrektur >
 
End If
'------</ SQL Funktionen >------
Debug.Print sSQL
 
 
'----< combine >----
If Not sSQL_Produkte Like "" And Not sSQL_Funktionen Like "" Then
sSQL = sSQL & " WHERE " & sSQL_Produkte & " AND " & sSQL_Funktionen
ElseIf Not sSQL_Produkte Like "" Then
sSQL = sSQL & " WHERE " & sSQL_Produkte
ElseIf Not sSQL_Funktionen Like "" Then
sSQL = sSQL & " WHERE " & sSQL_Funktionen
End If
'----< combine >----
 
 
ctlListe_Produkte.RowSource = sSQL
'---------------</ fxRefresh_List() >---------------
End Sub
'===================</ Funktion >======================
 
 

 
Mobile

.

Contact for Jobs, Project Requests: raimund.popp@microsoft-programmierer.de