Ms access moving average query

Cálculo de totais em execução e de médias móveis no Microsoft Access com estatísticas de acesso total O Microsoft Access não oferece recursos internos para calcular totais em execução. Os totais em execução são resumos sobre um número definido de registros. Por exemplo, uma média móvel de 30 dias que você gostaria de calcular para cada registro com base em seu valor e seus 29 registros anteriores. Felizmente, o Total Access Statistics realiza uma ampla gama de cálculos de total de execução em seus registros: Execute resumos para um conjunto de registros em movimento para calcular somas, médias, mínimo, máximo, intervalo, mediana, desvio padrão, etc. Na sua fonte de dados Analisar os dados em suas tabelas e consultas do Access (incluindo dados vinculados) Agrupamento de suporte em valores de campo para que você possa executar várias análises em uma única passagem Totais atuais foram adicionados ao Total Access Statistics para Microsoft Access 2007 e X.7 Versões para o Access 2003 e versões anteriores. Visão geral dos totais em execução Os totais em execução são cálculos para um número definido de registros através de sua tabela ou consulta. Gerar facilmente resultados como somas em execução, médias, mínimo, máximo, mediana, etc. e colocá-los em campos em sua fonte de dados. Esses cálculos estão disponíveis: Média (média) Contagem Observações Sum Sum Squared Mínimo Máximo Gama Desvio padrão Variância Coeficiente de variância Erro padrão Modo mediano Modo Contagem Média geométrica Média harmônica Média raiz Quadratura Skewness Kurtosis Erro padrão de skewness Erro padrão de curtose Até cinco campos Em sua tabela podem ser atualizados ao mesmo tempo. Como calcular valores totais são calculados Por exemplo, você pode querer calcular a média dos últimos 10 registros. Essa média móvel é determinada com base na ordem de classificação de seus dados, calculando a média desses 10 registros e colocando-a em um campo designado pelo registro 10. Para o registro 11, o registro 1 é ignorado eo cálculo é executado para os registros 2 a 11, etc. Como os outros recursos, você pode especificar campos de grupo para que cada grupo tenha seus próprios totais em execução. Este recurso não cria uma tabela separada. Os resultados são colocados nos campos especificados na sua origem de dados. Executar Seleção de Campos de Totais A tela de seleção de campos para Executar Totais permite especificar os campos a ordenar e os campos para colocar os resultados: Os Campos de Grupo permitem que você gere um conjunto separado de análise para cada combinação de valores exclusivos entre os campos de grupo. Os Campos de classificação determinam a ordem de classificação de seus registros para calcular os valores em execução. Especifique pelo menos um campo de classificação. Isso pode incluir o campo que você está realizando os cálculos. Os campos são classificados em ordem crescente. Especifique até cinco Campos a Atualizar com seus totais. Verifique se os campos são de tipos de dados que podem conter os dados que você espera colocar neles. Executar as opções totais Depois de seleccionar os campos, são apresentadas as opções Totais em curso: Campo a analisar Especifique o campo em que os cálculos são baseados. Este campo pode ser um campo que foi especificado como um campo de classificação. Por exemplo, você pode querer calcular a média corrente baseada em vendas ordenadas em ordem crescente. Número de registros para calcular totais acima Especifica o número de registros no total móvel. Se você especificar 0, os totais são calculados para cada registro. Se você inserir um número específico, os cálculos são baseados nesse número de registros. O primeiro registro é descartado quando o próximo é adicionado, e os cálculos são baseados nesse conjunto de registros em movimento. Conjunto inicial de registros Se você especificar o número de registros para calcular os totais, há uma opção para determinar o que acontece antes de alcançar esse número de registros. Os totais devem ser ignorados ou calculados com base nos registros processados. Escolha Calcular para ver os totais em execução independentemente do número de registros em movimento ser atingido. Escolha deixar em branco para ter somente totais em execução quando o número especificado de registros é alcançado sem quaisquer cálculos para menor número de registros. Tipo de Cálculo Existem muitos tipos de cálculo disponíveis. Especifique o que você deseja para cada um dos campos de atualização selecionados. Resultados totais em execução Os resultados totais em execução são colocados nos campos de actualização especificados: Exemplo de totais actuais colocados nos quatro campos direitos desta tabela do Microsoft Access Neste exemplo, os dados foram ordenados por Data e ID da encomenda, com o cálculo no campo Vendas campo. Observe os valores no campo RunningCount aumentando de 1 a 10. Uma vez que atinge 10, permanece em 10 porque esse é o número máximo de registros no total móvel. O campo RunningTotal mostra a soma de vendas sobre os registros no conjunto de registros em movimento. Como a opção de calcular os valores para o conjunto inicial de registros foi selecionada (antes de atingir 10 registros), os valores são exibidos. Caso contrário, os primeiros 9 registros teriam valores nulos. Assistente Interativo e Interfaces Programáticas do VBA O Total Access Statistics permite gerar interativamente seus cálculos através de sua interface de assistente para selecionar facilmente a fonte de dados, campos e análise sem programação. Depois de especificar suas seleções, elas são salvas automaticamente como um cenário no banco de dados para que você possa executá-las novamente no futuro quando seus dados forem alterados. Uma função de estatísticas VBA também está disponível para que você possa gerar esses resultados de código. Você pode facilmente executar qualquer cenário salvo chamando um único procedimento na biblioteca Total Access Statistics VBA. A biblioteca inclui uma licença de tempo de execução livre de royalties para que você possa incluí-la em seu banco de dados do Access e distribuí-la para proprietários que não sejam Total Access Statistics. Chamar a função de um evento OnClick botões ou outro processo, e os resultados são gerados para você. Você pode até mesmo esconder a interface interna do usuário para que seus usuários nem sequer sabem que o Total Access Statistics está em execução. Eles serão surpreendidos com os recursos de análise estatística Detalhes adicionais de cálculos disponíveis nas estatísticas de acesso total Aqui estão alguns recursos e detalhes adicionais sobre alguns dos dados de análise que você pode executar em seus dados do Microsoft Access com estatísticas de acesso total: Fluxos e taxas de juros para gerar o Valor Presente Líquido (VPN), Valor Presente (PV), Valor Futuro (VF), Taxa Interna de Retorno (TIR), Taxa Interna Modificada de Retorno (MIRR) para pagamentos e recibos periódicos e dependentes da data. Cálculos de Percentilos Calcule diferentes tipos de percentis: mediana, quartis, quintis, octiles, deciles, percentis, cada percentil X.5 e coloque-os em uma tabela ou atualize um campo existente com o valor de percentil de registros. Totais Correntes e Médias Móveis Resumir um conjunto de registros em movimento para calcular somas correntes, médias móveis, mínimo, máximo, intervalo, mediana, desvio padrão, etc. Normalização de Dados (Transposição de Dados) Transponha dados não normalizados para que você possa facilmente analisar e manter isto. Regressões Regressões simples, múltiplas e polinomiais com cálculo dos coeficientes de equação, ANOVA e tabela residual Tabelas cruzadas e tabelas cruzadas Chi-Square com porcentagem de linha, coluna e totais exibidos como registros ou colunasValoração média na consulta Re: Média móvel na consulta Você Significa apenas os dados mais recentes de quatro semanas (por isso está mudando / movendo a cada semana) O que os dados em seu campo YearWeek parecem É formatado como Texto do Número DICAS PARA ENCONTRAR AS SOLUÇÕES EXCEL 1. Use a Ajuda embutida que vem Com Excel / Access 2. Use a funcionalidade de pesquisa nesta placa 3. Um monte de código VBA pode ser adquirido usando o gravador de macro. Dê a um homem um peixe, alimente-o por um dia. Ensinar um homem a pescar, alimentá-lo para a vida Gerrit. B disse: 06 de maio de 2017 16:31 Re: Movendo Média em Query Joe4, Não eu quero calcular a média móvel em 4 semanas. Campo YearWeek parece 201701 para a semana 1 de 2017, e é formatado como número. Re: Movendo Média em Query OK, independentemente de suas respostas às perguntas anteriores, acho que tenho uma solução que deve funcionar para qualquer instância. Primeiro, criei uma função definida pelo usuário que converte seu número de semana para um número total de semanas. Leva o ano e multiplica por 52, e depois adiciona os meses nas últimas duas posições de número. Esse UDF se parece com isto: Note, não é necessário usar um UDF, como você pode usar esta fórmula diretamente em suas consultas, mas desde que eu estou reutilizando, é muitas vezes mais fácil e torna suas fórmulas mais legível para fazê-lo deste jeito. Em seguida, criei uma consulta para calcular o máximo YearWeek para cada combinação de área / produto ID. Acabei de agrupar os campos Área e ID do produto e tomou o valor máximo de Anual (se você não estiver familiarizado com Consultas de Agregação, eu recomendo que eles sejam verificados nos arquivos de Ajuda embutidos do Access). Assumindo um nome de tabela de MyTable, aqui está o que o código SQL daquela consulta se parece com: Eu chamei essa consulta MyMaxQuery. Eu, em seguida, criei uma nova consulta em que juntamos a tabela original (MyTable) a esta consulta nos campos Área e ID do produto. Eu então criei um campo calculado chamado Check, onde verifico para ver se o campo YearMonth é maior ou igual ao valor máximo de YearWeek (a partir da consulta anterior) menos 3. Se for, eu retornar um 1. Eu, em seguida, adicionar critérios Para essa consulta para retornar apenas os valores iguais a 1. Assim, isso limita os dados para os últimos 4 meses. Agora, eu mudo esta consulta para uma consulta de agregação e pegue a média da quantidade para obter o que você deseja. O código SQL para essa consulta é semelhante a: Dicas para encontrar soluções Excel 1. Use a Ajuda embutida que vem com o Excel / Access 2. Use a funcionalidade de pesquisa nesta placa 3. Um monte de código VBA pode ser adquirido usando o Gravador De Macro. Dê a um homem um peixe, alimente-o por um dia. Ensinar um homem a pescar, alimentá-lo para a vida Eu tenho uma produção de tabela que contém a seguinte estrutura: Eu tenho dados para cada representante de 1/1/2017 a 8/1/2017. O que eu quero ser capaz de fazer é criar uma média móvel de 12 meses a partir de 1/1/2017 para cada representante, da seguinte forma: onde cada linha representa a média móvel de 12 meses para o referido representante no momento indicado. Eu encontrei alguns exemplos que estavam vagamente perto e eu tentei-los sem sucesso. Parece que a adição de um grupo por componente rep é a principal saída de outros exemplos. Isso é quase tão longe quanto eu tenho: Essa consulta parece puxar uma média geral ou soma, uma vez que não há agrupamento na subconsulta correlacionada. Quando tento agrupar por, recebo um erro que ele só pode retornar no máximo uma linha. Perguntou Oct 10 13 em 14: 47Moving Cálculo Médio Moving Average Cálculo Moving Average Cálculo Eu estou tentando calcular uma média móvel para uma série de dados. Eu quero gerar a média móvel para cada ponto dentro dos dados, para mostrar em um gráfico. Enfim, abaixo é um exemplo do MS Support. Tenho seguido à carta, mas a minha não dá uma média móvel. Ele repete o mesmo ponto de dados uma e outra vez (o primeiro ponto de dados). Então, eu não acredito que a função está encontrando o startdate na linha MyRST. Seek, portanto, apenas retornando o primeiro ponto de dados. Finalmente (talvez vai fazer isso realmente fácil) Estou confuso sobre como os índices de trabalho. Eu pensei que você poderia ter apenas uma chave primária, mas aparentemente você pode criar várias restrições de campo. Eu tentei fazer isso com a seguinte consulta de definição de dados: ALTER TABLE Tabela1 ADD CONSTRAINT NoDupes ÚNICO (CurrencyType, TransactionDate) Desculpe sobre o comprimento desta postagem. Eu aprecio sua ajuda. A função de exemplo a seguir calcula as médias móveis com base em uma tabela com uma chave primária de vários campos. Os valores semanais de moedas estrangeiras são usados ​​para este exemplo. Para criar a função de exemplo, execute estas etapas: Crie a tabela a seguir e salve-a como Table1: Table: Table1 --------------------------- -------------- Nome do Campo: CurrencyType Tipo de Dados da Chave Primária: Tamanho do Campo de Texto: 25 Nome do Campo: TransactionDate Chave Primária Tipo de Dados: Date / Time Formato: Short Date Nome do Campo: Rate Data Type : Currency Decimal Places: 4 Ver a tabela no modo folha de dados e introduza os seguintes valores: CurrencyType TransactionDate Rate ----------------------------- --------------- Yen 8/6/93 0,0079 Yen 8/13/93 0,0082 Yen 8/20/93 0,0085 Yen 8/27/93 0,0088 Yen 9/3/93 0,0091 Mark 8/6/93 0.5600 Mark 8/13/93 0.5700 Mark 8/20/93 0.5800 Mark 8/27/93 0.5900 Mark 9/3/93 0.6000 Abra um novo módulo e digite as seguintes funções: Function MAvgs (Periods As Inteiro, StartDate, TypeName) Dim MyDB como DATABASE, MyRST Como Recordset, MySum como Dim Duplo i, x Set MyDB CurrentDb () Set MyRB MyDB. OpenRecordset (Table1) Em erro Retomar Next MyRST. Index PrimaryKey x Períodos - 1 ReDim Store X) MySum 0 For i 0 To x MyRST. MoveFirst MyRST. Seek, TypeName, StartDate Essas duas variáveis ​​devem estar na mesma ordem que os campos de chave primária em sua tabela. Store (i) MyRSTRate Se eu lt x Em seguida, StartDate StartDate - 7 O 7 aqui assume dados semanais 1 para dados diários. Se StartDate lt 8/6/93 MAvgs Null: Exit Function 8/6/93 for substituído pela data mais antiga dos dados na tabela. MySum Store (i) MySum Próxima i MAvgs MySum / Períodos MyRST. Close End Function Crie a seguinte consulta com base na tabela Table1: Query: Query1 -------------------- ----------------------------------- Campo: CurrencyType Campo: TransactionDate Campo: Rate Campo: Expr1: MAvgs ( 3, TransactionDate, CurrencyType) NOTA: Esta consulta irá gerar uma média móvel de três semanas dos dados da taxa. Para calcular uma média móvel mais longa ou mais curta, altere o número 3 na coluna de consultas Expr1 para o valor que deseja calcular. Execute a consulta. Observe que você vê a seguinte média móvel de três semanas para cada moeda. Um valor Nulo indica que não havia valores anteriores suficientes para calcular essa média de semanas. CurrencyType TransactionDate Taxa Expr1 Marca 08/06/93 0,5600 Marca 08/13/93 0,5700 Marca 08/20/93 0,5800 0,57 Marca 08/27/93 0,5900 0,58 Marca 09/03/93 0,6000 0,59 Yen 08/06/93 0,0079 Yen 08/13/93 0,0082 Yen 08/20/93 0,0085 0,0082 Yen 08/27/93 0,0088 0,0085 Yen 09/03/93 0,0091 0,0088 RE: Moving Average Cálculo dhookom (Programmer) 28 Jun 10 21:15 Quantos anos tem esse código Ele não usa explicitamente DAO e não menciona que isso não funcionará com tabelas vinculadas. CODE Dim MyDB como DAO. Database, MyRST como DAO. Recordset. Eu usaria uma subconsulta em vez de um conjunto de registros. Ele pode parecer algo como: CODE SELECT CurrencyType, TransactionDate, Rate, (SELECT Avg (Taxa) FROM Tabela1 B WHERE A. CurrencyType B. CurrencyType E A. TransactionDate ENTRE B. TransactionDate - 14 AND B. TransactionDate) FROM Tabela1 A RE: Moving Average Cálculo Isso é realmente perfeito. Eu realmente aprecio sua ajuda. No entanto, o código que você deu é calcular a média móvel de 14 dias para a frente (colocando a média móvel no registro para o dia 1 da média, onde eu queria que ela fosse uma média para trás, colocada no registro 14). Eu mudei apenas ligeiramente para o seguinte e ele parece estar trabalhando SELECT A. CurrencyType, A. TransactionDate, A. Rate, (SELECT Avg (Rate) FROM Tabela1 B WHERE A. CurrencyType B. CurrencyType E B. TransactionDate ENTRE A. TransactionDate - 14 AND A. TransactionDate) AS Expr1 FROM Table1 AS A Você pode ver, tudo o que fiz foi trocar A por B na cláusula where. Esta é uma ajuda muito grande para mim e eu realmente aprecio isso. Eu não vi codificação como esta antes, e honestamente, eu realmente não entendo. Eu não sei como o SQL entende o que B e A são. Estou assumindo que eles estão criando algum tipo de referência alternativa para Table1. Se você pode dar qualquer orientação eu realmente aprecio isso. Além disso, talvez alguma referência ao material que eu poderia olhar Estou sempre muito animado para aprender algo novo sobre VBA / SQL, e eu realmente aprecio a sua ajuda RE: Moving Average Cálculo PHV (MIS) 29 Jun 10 12:22 compreende o que B E A são eles são alias es RE: Cálculo de Moving Average Obrigado, PHV. É fazer sentido melhor já RE: Moving Average Cálculo joshery420 (TechnicalUser) 6 Jul 10 15:06 Uau, nunca olhou SQL vista antes. Extremamente útil. Estou tentando obter este código para funcionar no meu próprio conjunto de dados e Im preso em um problema específico. Pd2004, não tenho certeza se o novo código de subconsulta funcionou o mesmo que o seu antigo código VBA ou não, mas com os meus dados ainda mostra a média de rolamento, mesmo se não houver dias suficientes para criar esse comprimento de uma média. por exemplo. Se Im executando uma média de rolamento de 7 dias, o dia 1 mostra os mesmos dados na coluna 7DayAvg como na coluna de dados diários. Dia 2 mostraria a média dos dias 1 e 2, etc. Qualquer um de vocês sabe como corrigir isso por acaso Também, obrigado pela grande dica de código PHV. RE: Moving Average Cálculo joshery420 (TechnicalUser) 6 Jul 10 15:08 Woops, significava agradecer dhookom para a dica de código, não PHV. Mas hey, obrigado a ambos. XD RE: Moving Average Calculation Vou deixar as melhores soluções para os profissionais aqui, mas você pode ver no meu post original como o exemplo da Ajuda da Microsoft tenta lidar com isso. Aqui está o código: Se StartDate lt 8/6/93 MAvgs Null: Exit Function 8/6/93 é substituído pela data mais antiga dos dados na tabela. Eles estão apenas saindo da função se a data não caber os critérios. Eu não sei se você poderia incorporar algo assim no código de aliases fornecido pelo dhookem. Eu não gosto da sua maneira de lidar com isso, e eu suspeito que dhookem irá fornecer uma solução muito mais elegante. Para meus propósitos a questão que você está descrevendo não é uma preocupação, mas eu estarei interessado em ver quaisquer soluções. RE: Moving Average Cálculo dhookom (Programmer) 6 Jul 10 17:05 Você pode tentar usar IIf () para testar uma contagem do número de registros. Cuidado: o código do bloco de notas não testado segue: CODE SELECT A. CurrencyType, A. TransactionDate, A. Rate, IIF ((SELECT Count (Taxa) FROM Tabela1 C WHERE A. CurrencyType C. CurrencyType E C. TransactionDate ENTRE A. TransactionDate - 14 AND A. TransactionDate) 7, (SELECT Avg (Taxa) FROM Table1 B WHERE A. CurrencyType B. CurrencyType E B. TransactionDate ENTRE A. TransactionDate - 14 AND A. TransactionDate), Null) AS Expr1 FROM Table1 como A RE: Moving Average CalculationAVG (Transact-SQL) ALL Aplica a função agregada a todos os valores. ALL é o padrão. DISTINCT Especifica que o AVG seja executado somente em cada instância exclusiva de um valor, independentemente do número de vezes que o valor ocorrer. Expressão É uma expressão da categoria de tipo de dados numérico exata ou aproximada, exceto para o tipo de dados bit. Funções agregadas e subconsultas não são permitidas. OVER (partitionbyclause orderbyclause) partitionbyclause divide o conjunto de resultados produzido pela cláusula FROM em partições às quais a função é aplicada. Se não for especificado, a função trata todas as linhas do conjunto de resultados da consulta como um único grupo. Orderbyclause determina a ordem lógica na qual a operação é executada. Pedido por cláusula é necessária. Para obter mais informações, consulte Cláusula OVER (Transact-SQL). O tipo de retorno é determinado pelo tipo de resultado avaliado da expressão. Decimal category (p, s) Se o tipo de dados de expressão é um tipo de dados de alias, o tipo de retorno também é do tipo de dados de alias. No entanto, se o tipo de dados base do tipo de dados de alias é promovido, por exemplo de tinyint para int. O valor de retorno é do tipo de dados promovido e não o tipo de dados alias. AVG () calcula a média de um conjunto de valores dividindo a soma desses valores pela contagem de valores nonnull. Se a soma exceder o valor máximo para o tipo de dados do valor de retorno um erro será retornado. O AVG é uma função determinística quando usado sem as cláusulas OVER e ORDER BY. Ele é não-determinístico quando especificado com as cláusulas OVER e ORDER BY. Para obter mais informações, consulte Funções determinísticas e não determinísticas. A. Usando as funções SUM e AVG para cálculos O exemplo a seguir calcula as horas de férias médias e a soma das horas de folga que os vice-presidentes de Ciclos de Trabalho Aventuras usaram. Cada uma dessas funções agregadas produz um único valor de resumo para todas as linhas recuperadas. O exemplo usa o banco de dados AdventureWorks2017.