Martin F's questions

Tenho uma tabela do Access chamada Engagementcom quatro campos:

Emp_id, Year, Week, Act_id

Ele registra quando um funcionário está/estava envolvido em uma atividade. Cada campo é Number e cada um faz parte da chave primária composta. A semântica do aplicativo era que cada entrada (cada Emp_id- Year- Week- Act_idcombinação) deveria ser única. Ou seja, embora um funcionário geralmente tenha diferentes semanas do ano e diferentes atividades, às vezes um funcionário pode se envolver na mesma atividade em diferentes semanas do ano ou até mesmo se envolver em atividades diferentes na mesma semana do ano. Nulos não eram permitidos. Tudo funcionou.

Agora preciso expandir/modificar a semântica para permitir valores ano-semana desconhecidos – ou mais apropriadamente, não divulgados – associados a qualquer atividade de funcionário. Obviamente, tentar inserir uma linha com um ano-semana vazio resulta em "Índice ou chave primária não pode conter um valor nulo". Então eu preciso de uma mudança no design da mesa.

Uma coisa que tentei foi converter o índice de chave primária em um índice não primário desativando Primário (e deixando Único ativado) na janela Índices. Isso evita corretamente registros duplicados onde os valores de Ano-Semana não estão vazios -- mas permite registros duplicados onde Ano-Semana estão vazios.

Por exemplo, usando o índice exclusivo não primário acima, os seguintes dados são permitidos:

Emp_id  Year  Week  Act_id
     7  2014    12      31  } Same activity,
     7  2015    22      31  } different dates.
     7  2015    33      32
     7  2015    40      33  } Same dates,
     7  2015    40      34  } different activities.
     7  2016     2      36
     7                  38  } Different activities,
     7                  39  } undisclosed dates.

E nenhuma das seguintes adições são permitidas posteriormente:

Emp_id  Year  Week  Act_id
     7  2014    12      31  } Both records are 
     7  2015    33      32  } duplicates of above.

Até aqui tudo bem (o comportamento corresponde aos requisitos). No entanto, ambas as adições a seguir são permitidas posteriormente, mas não devem ser :

Emp_id  Year  Week  Act_id
     7                  38  } Both records are
     7                  39  } duplicates of above.

Por que é que?

Qual é uma boa maneira de contornar esse problema de, por um lado, permitir algo equivalente a nulo para Ano e Semana e, por outro lado, restringir cada Emp_id- Year- Week- Act_idcombinação a ser única?

Posso pensar em duas outras soluções (não testadas):

1. Escolha um valor numérico equivalente a nulo, como zero ou -1 e de alguma forma explique isso aos usuários.
2. Converta os tipos de campo Ano-Semana de Número para Texto e simplesmente use "" (string vazia) para nulo.

Na sua experiência, qual é uma boa solução para esse tipo de situação?

Estou ciente de O que há de errado com colunas anuláveis ​​em chaves primárias compostas? e valor NULL na chave primária de várias colunas , que explicam certas coisas, mas não fornecem uma solução.

Suponha que eu tenha duas tabelas para junção externa

Tab1:
ID  Nam
1   Joe
2   Moe
3   Flo

Tab2:
ID  Val     y   w
1   stuff   66  33
2   duff    67  44

e uma das colunas resultantes é derivada -- ou seja, é uma função de duas outras colunas

SELECT Tab1.*, Tab2.Val, Tab2.y, Tab2.w, week(Tab2.y,Tab2.w) AS week
FROM Tab1 LEFT JOIN Tab2 ON Tab1.ID = Tab2.ID

Como esperado, onde não há junção, nulos aparecem nas colunas reais, mas #Erroraparecem na coluna derivada :

ID  Nam Val     y   w   week
1   Joe stuff   66  33  66w33
2   Moe duff    67  44  67w44
3   Flo                 #Error

Primeiro, pensei que poderia impedir #Errorpor meio da função útil para lidar com strings nulas, Nz()mas Nz(week(Tab2.y,Tab2.w))não fiz nada. Em seguida, pensei que era algo que eu tinha que lidar em minha week()função - ou seja, lidar com casos de IsNull(y)- mas descobri que nem mesmo é chamado para os "casos nulos".

Como evitar que os #Errorvalores apareçam?

Eu tenho uma tabela do Access 2010Assignment

key Empl    Number
key Year    Number
key Week    Number
key Proj    Number

Ele atribui aos funcionários zero ou mais projetos para determinadas semanas de trabalho, portanto, todas as quatro colunas compõem a chave composta. Tenho outra tabela, Import, com estrutura idêntica, contendo dados a serem utilizados para atualização Assignmentde acordo com diferentes opções.

Opção 1 Mantenha todas as atribuições existentes, adicione importações completamente novas e ignore quaisquer importações que já existam.

Posso gerenciar isso usando uma simples consulta de acréscimo:

INSERT INTO Assignment 
SELECT *
FROM Import 

Até agora tudo bem.

Opção 2 Como acima, exceto onde há uma correspondência empregado-ano-semana entre a atribuição e a importação, substitua a atribuição existente pela importação.

Uma abordagem para conseguir isso é primeiro excluir todos os registros com uma correspondência funcionário-ano-semana e, em seguida, acrescentar as importações (como acima). O resultado deve ser substituir correspondências exatas como estão, substituir correspondências parciais (funcionário-ano-semana) por novos projetos e adicionar atribuições completamente novas.

Posso isolar as correspondências por meio desta consulta, chamada Existing:

SELECT a.*
FROM Import AS i INNER JOIN Assignment AS a 
ON a.Empl = i.Empl AND a.Year = i.Year AND a.Week = i.Week

Como posso excluir registros Assignmentque também estão em Existing?

Eu tentei variações sobre isso

DELETE 
FROM Assignment
WHERE IN (Existing)

tudo resultando em mensagens de erro.

A propósito, parece que, se existisse tal animal , eu poderia usar uma MINUS(ou DIFFERENCE) consulta:

Assignment
MINUS
Existing

Uma pequena variação na abordagem seria primeiro excluir os registros com uma correspondência funcionário-ano-semana e uma incompatibilidade de projeto e , em seguida, anexar as importações, como na Opção 1. (Menos exclusões e adições, mas mesmo resultado.)

Posso isolar essas correspondências por meio desta consulta, chamada Existing_exp:

SELECT a.*
FROM Import AS i INNER JOIN Assignment AS a 
ON a.Empl = i.Empl AND a.Year = i.Year AND a.Week = i.Week AND a.Proj <> i.Proj

mas ainda me resta a tarefa de como deletar os registros Assignmentque estão em Existing_exp!

Em SQL Aggregate Functions (Office 2007) existem apenas estas GROUP BYfunções agregadas no Access SQL:

Count(), Sum(), Avg(), 
First(), Last(), Min(), Max(), 
StDev(), StDevP(), Var(), VarP()

Das funções agregadas do PostgreSQL existem (entre outras) também estas funções agregadas no PostgreSQL:

array_agg (expression)
  -- input values, including nulls, concatenated into an array

string_agg (expression, delimiter)
  -- input values concatenated into a string, separated by delimiter

Como obter o equivalente GROUP BYa funções agregadas de array ou string no Access? É possível construir funções agregadas do Access SQL? Caso não esteja claro, se eu esses dados

ID col
-----
1  A
1  B
1  C
2  A
3  A
3  B

como posso obter a seguinte agregação?

ID cols
----------
1  A, B, C
2  A
3  A, B

Tenho que recorrer ao VBA? Alguma outra ideia?

Estou usando o Access 2007 - 2010, mas se as coisas forem diferentes em uma versão mais recente, informe-me.

Como posso salvar todo o design ou especificações de um banco de dados do Access 2007 - não o conteúdo dos dados - como texto?

Eu caí em conflito com o bug "Inserir valor do parâmetro", em que um pop-up de consulta mostra um nome de consulta desatualizado - apesar de ter as opções de autocorreção de nomes ativadas e de ter executado o banco de dados de compactação e reparo.

Eu li que a AutoCorreção de Nomes tem erros. Também li que era possível salvar um objeto como texto (em uma versão anterior do Access) e acho que esse seria um meio valioso de fazer alterações de nome em qualquer lugar - consultas e formulários, bem como tabelas.

_{Por favor, não tente me convencer a não mudar de nome; isso só pode me deixar mais determinado a fazê-lo ;-)}

Eu tenho o que estou chamando de "matriz de interação" esparsa de dados no MS Excel que desejo converter em uma "relação ternária" (tabela) no Access.

Os dados da planilha estão neste padrão:

Name   Act-1  Act-2  Act-3  ... Act-n
name-1         week
name-2                week
name-3  week                     week
  :                         week
name-m         week   week

Existem natividades como cabeçalhos do Excel, mnomes na primeira coluna de cada linha de dados e zero ou mais semanas específicas inseridas sempre que uma pessoa (nome) realizou uma atividade. Se nfossem pequenos e fixos, eu poderia manter a relação como uma matriz, mas npode ficar muito grande e a matriz ficará muito esparsa.

Assim, a estrutura da tabela relacional desejada (vou chamá-la de Atribuição) é assim:

Assignment: Name, Activity, Week

Existe uma ferramenta fácil no Excel ou no Access para facilitar essa conversão de dados?

Um algoritmo de pseudocódigo seria

for each Name (row)
    for each Activity (column)
        if Week, add Assignment: Name, Activity, Week

Embora eu tenha programado em VBA (anos atrás), nunca o usei em conjunto com o Excel. Estou inclinado a usar arquivos Python e CSV. Talvez haja uma boa técnica de SQL (mesmo que não seja específica do Access)? Ou é algo a ver com "pivotar" (nunca entendi do que se tratava)?

Você tem alguma experiência que gostaria de compartilhar com esse tipo de conversão?