Colunas com nome fixo e referência cruzada

Como fixar nomes de colunas em tabelas de referência cruzada.

Autor: Michel Walsh

Se alguém deseja fixar nomes de colunas produzidas por uma consulta de referência cruzada, pode usar a propriedade Cabeçalho da coluna da consulta (nas propriedades, no modo design). Apenas colocamos o nome das colunas que desejamos obter, como "JAN", "FEV", "MAC", "ABR", ... e, se digitarmos "MAC", não teremos uma coluna MAR, para Março, e teremos uma coluna MAC, provavelmente cheia de NULLs. Alterar os cabeçalhos das colunas força a consulta de referência cruzada a produzir as colunas com os nomes fornecidos, mesmo que a coluna esteja totalmente vazia. E vai produzir apenas as colunas nomeadas.

Algumas vezes não é possível usar nomes fixos em colunas com a tabela de referência cruzada porque os valores na tabela fonte, que produzem os nomes das colunas na referência cruzada são alterados continuamente. Infelizmente, alguns nomes "fixos" podem ser necessário para uso em um formulário, um relatório, um gráfico... ou para outra manipulação.

Uma solução possível, neste caso, é criar uma consulta entre o formulário (gráfico, relatório) que exige o nome fixado e a consulta de referência cruzada. Essa consulta vai apenas criar APELIDOS [ALIAS] (usando a palavra chave AS) para os nomes "variáveis" de campo, transformando-os em um esquema de nomes "constantes":

SELECT XTab1 As F1, XTab2 As F2, ... FROM XTab;

onde F1, F2, ... serão nomes constantes e XTab1 é um nome de campo produzido pela referência cruzada.

Se usarmos esta string SQL como origem dos registros para um formulário este pode usar os NOMES de campos CONSTANTES: F1, F2...

Fácil e simples.

O problema que resta é construir a string SQL. Não podemos "passear" pela coleção de campos da consulta de referência cruzada, já que pode ser muito "caro" executar a consulta apenas com esse propósito. Por sorte, os nomes de campos produzidos pela consulta de referência cruzada são, na verdade, VALORES (VALUES) de um campo "cruzado" da consulta. Podemos ver tanto a tabela quanto o campo após a palavra chave PIVOT, quase no final da consulta (em modo SQL, como:

.... PIVOT TableName.FieldName;

Fácil. Se este é o caso, podemos obter com facilidade a string para a consulta "intermediária":

SELECT XTab1 As F1, XTab2 As F2, ...

usando qualquer um dos módulos abaixo (a segunda função é usável apenas no Access 2000 ou superiores), que, basicamente, usam algum tipo de concatenação, como:

Debug.Print DAO_MakeSQLCoverQueryFor("FieldName", "TableName", "CrosstabName")

'   ******** Início do código ********
' Este código foi escrito originalmente por Michel Walsh. 
' Não deve ser alterado ou distribuído, 
' exceto como parte de um aplicativo. 
' Use-o livremente em qualquer aplicativo,  
' desde que esta nota de copyright permaneça inalterada.
'
' Código cortesia de
' Michel Walsh
'
Public Function DAO_MakeSQLCoverQueryFor(TableName As String, _
                            FieldName As String, _
                            XTableName As String) As String
'   XTableName = nome da consulta de referência cruzada
'   TableName = nome da tabela que fornece o cruzamento
'   FieldName = nome do campo usado como pivot
Dim W As String     ' a string SQL
Dim i As Long       ' mantém a seqüência de campos
Dim db As DAO.Database
Dim rst As DAO.Recordset

    Set db = CurrentDb()
    Set rst = db.OpenRecordset("SELECT DISTINCT " & FieldName _
                        & " FROM " & TableName & ";")
    rst.Move 0
    W = ""
    i = 1
    Do Until rst.EOF
        W = W & rst(FieldName) & " As F" & i & ", "
        i = i + 1
        rst.MoveNext
    Loop
    W = Left$(W, Len(W) - 2)
    ' retira o último ", "
    W = "SELECT " & W & " FROM " & XTableName & ";"
    DAO_MakeSQLCoverQueryFor = W
End Function
'
'-------------------------------------------------
'
Public Function MakeSQLCoverQueryFor(TableName As String, _
                            FieldName As String, _
                            XTableName As String) As String
Dim rst As ADODB.Recordset
    Set rst = CurrentProject.Connection.Execute( _
                        "SELECT DISTINCT " & FieldName & " FROM " _
                        & TableName, , adCmdText)
    ' o suficiente para este aplicativo
    rst.Move 0
    W = ""
    i = 1
    Do Until rst.EOF
        W = W & rst(FieldName) & " As F" & i & ", "
        i = i + 1
        rst.MoveNext
    Loop
    W = Left$(W, Len(W) - 2)
    ' retira o último ", "
    W = "SELECT " & W & " FROM " & XTableName & ";"
    rst.Close
    Set rst = Nothing
    MakeSQLCoverQueryFor = W
End Function
'   ******** Final do código ********

Agora o comando SQL da consulta de referência cruzada tem uma cláusula PIVOT diferente do comum

PIVOT TableName.PivotName

temos que fazer as alterações apropriadas na linha de código:

"SELECT DISTINCT " & FieldName .

Por exemplo, se for

...PIVOT TableName.Format(FieldName, "mmm")

no programa, use

:
"SELECT DISTINCT " & Format(FieldName, "mmm") & .