How to call a VBA function to specify columns in a SQL TRANSFORM query?

Question

Here is my request:

PARAMETERS ... TRANSFORM ... SELECT ... ... PIVOT Mytable.type In ("Other","Info"); 

This is a cross-query, and I need to set all the column headers with this row: PIVOT Mytable.type In ("Other","Info") , and now I have hard-coded headers, Others and Information .

But I want to do it dynamically. So I want to call the vba function, which returns all the headers that I need.

Something like that:

 PIVOT Mytable.type In (myVbaFunction()); 

So my question is: how to call a vba function inside an sql query?

Answer 1

Yes it is possible.
However, I do not think this is possible with WHERE IN (...) .

Here is an example of a regular WHERE query:

 Public Function Test() As String Test = "Smith" End Function 

... and then:

 SELECT * FROM Users WHERE Name = Test(); 

It works while the function returns only one value.
But I think that you can not let your function return something like "Smith, Miller" and use it like:

 SELECT * FROM Users WHERE Name In (Test()); 

(at least I don't know how to do this)

Answer 2

If the IN list is excluded from the PIVOT clause, the TRANSFORM query automatically creates columns for each PIVOT value generated from the SELECT statement. Finite columns can be filtered by specifying an IN expression with "hard-coded" (i.e. literal) values. This, of course, is known from other answers.

Exactly the same effect can be achieved by restricting the data from the SELECT query to start with ... before TRANSFORM needs to filter it. Thus, one of them is not limited to predefined literal values ​​- rather, combinations of JOINS, subqueries, and / or VBA functions can also pre-filter data, effectively choosing which columns are displayed in the conversion table. Note that the HAVING clause is not allowed in the TRANSFORM query, but it can be used in another query that then selects TRANSFORM, so there is no restriction on how the data will be prepared before TRANSFORM.

All of the following results are equivalent. First use PIVOT ... IN:

 TRANSFORM Count(Services.ID) AS [Schedules] SELECT Agreement.City FROM Agreement INNER JOIN Services ON Agreement.Account = Services.Account WHERE ( Services.Code = "IS" ) GROUP BY Agreement.City ORDER BY Agreement.City PIVOT Month([ServiceDate]) In (1,4,12) 

Using the IN operator in a WHERE clause:

 TRANSFORM Count(Services.ID) AS [Schedules] SELECT Agreement.City FROM Agreement INNER JOIN Services ON Agreement.Account = Services.Account WHERE ( (Month([ServiceDate]) In (1,4,12)) AND Services.Code = "IS" ) GROUP BY Agreement.City ORDER BY Agreement.City PIVOT Month([ServiceDate]) 

but unlike the PIVOT ... IN clause, the list may also be another request:

 WHERE ((Month([ServiceDate]) In (SELECT Values FROM PivotValues)) AND Services.Code = "IS" ) 

Finally, using the VBA function (which answers the original question):

 TRANSFORM Count(Services.ID) AS [Schedules] SELECT Agreement.City FROM Agreement INNER JOIN Services ON Agreement.Account = Services.Account WHERE ( ReportMonth([ServiceDate]) AND Services.Code = "IS" ) GROUP BY Agreement.City ORDER BY Agreement.City PIVOT Month([ServiceDate]) 

With the VBA function defined in the standard module:

 Public Function ReportMonth(dt As Date) As Boolean Select Case Month(dt) Case 1, 4, 12: ReportMonth= True Case Else: ReportMonth= False End Select End Function 

(iDevlop already suggested this solution in a comment, but I don’t think it was understood, and good examples are needed.)

Answer 3

Guaranteed column inclusion in the transformation

Andre pointed out that my last answer was not able to solve one of the functions of the explicit list of PIVOT columns, namely that it stores the columns even if the data does not contain the corresponding values. In many cases, it is also possible to generate full SQL on the fly, as David W. Fenton commented. Here are some sample code for this:

 Public Function GenerateTransform(valueArray As Variant) As String Dim sIN As String Dim i As Integer, delimit As Boolean If (VarType(valueArray) And vbArray) = vbArray Then For i = LBound(valueArray) To UBound(valueArray) sIN = sIN & IIf(delimit, ",", "") & valueArray(i) delimit = True Next i If Len(sIN) > 0 Then sIN = "IN (" & sIN & ")" End If GenerateTransform = "TRANSFORM ... SELECT ... PIVOT ... " & sIN End Function Public Sub TestGenerateTransform() Dim values(0 To 2) As Integer values(0) = 1 values(1) = 4 values(2) = 12 Debug.Print GenerateTransform(values) Debug.Print GenerateTransform(vbEmpty) 'No column list End Sub 

---

Like my other answer, the following queries allow you to use various methods when selecting and filtering criteria. This method not only guarantees the availability of columns, but also allows more control over ^^ strings.

^ Despite the fact that VBA functions can still be used in their usual volume in SQL, Access does not allow dynamically adding new row data at runtime using SQL VBA ... rows should be based on actual table rows. (Technically, you can use UNION SELECT with literal values ​​to create rows, but this is forbidden for large amounts of data and does not facilitate any dynamic selection of columns.) Therefore, the following method requires the use of an auxiliary table to determine / select column values .

The first query applies the selection criteria and the initial grouping. If you compare with my other answer, this is essentially the same as the original TRANSFORM query - only without TRANSFORM and PIVOT. Save and name this query [1 Initial population] :

 SELECT Agreement.City, Month([ServiceDate]) AS [Month], Count(Services.ID) AS Schedules FROM Agreement INNER JOIN Services ON Agreement.Account = Services.Account WHERE (Services.Code = "IS") GROUP BY Agreement.City, Month([ServiceDate]) ORDER BY Agreement.City 

Then create a query that groups all the values ​​of the string. In this example, I prefer to include only the same values ​​from the original selection criteria. ^^ This set of values ​​can also be separated from previous selection criteria based on an unfiltered table or other query. Save and name this query [2 header lines] :

 SELECT RowSource.City AS City FROM [1 Initial Aggregate] AS RowSource GROUP BY RowSource.City ORDER BY RowSource.City 

Cross-link row headers and a sub table [PivotValues] containing column headers. A cross join creates rows from each combination of two tables — in Access SQL, this is done with the exception of all JOIN keywords. Save and name this query [3 Cross Join] :

 SELECT [2 Row Headings].City AS City, PivotValues.Values AS Months FROM [2 Row Headings], PivotValues ORDER BY [2 Row Headings].City, PivotValues.Values; 

Finally, the conversion: using the LEFT JOIN, this will include all the columns and rows that exist in the cross join request. For pairs of columns and rows that do not have data in the combined select query, the column will still be included (i.e. guaranteed) with a null value. Despite the fact that we have already grouped the original query, the conversion requires us to re-group anyway - perhaps a little redundant, but not very important to get the desired control over the final results of the crosstab.

 TRANSFORM Sum([1 Initial Aggregate].Schedules) AS SumOfSchedules SELECT [3 Cross Join].City AS City FROM [3 Cross Join] LEFT JOIN [1 Initial Aggregate] ON ([3 Cross Join].Months = [1 Initial Aggregate].Month) AND ([3 Cross Join].City = [1 Initial Aggregate].City) GROUP BY [3 Cross Join].City PIVOT [3 Cross Join].Months 

This may seem redundant to make the columns of the crosstab dynamic, but for complete control over the results, several additional queries may be required. VBA code can be used to (re) define values ​​in a helper table, which satisfies the original question of using VBA to dynamically specify columns.

Answer 4

Why not add these headers to the table and join this table in your xtab query?
It is probably easier to maintain that hard coding in function.

Answer 5

As you stated that you are using Access, then (from the head) yes, it is possible to use VBA functions in queries.

Check the documentation and MSDN.