Przenoszenie średnio zapytanie dostęp

Mam produkcję tabel, która zawiera następującą strukturę: mam dane dla każdego przedstawiciela z 112017 do 812017. Co chcę zrobić to utworzyć 12-miesięczną średnią ruchową, która zaczyna się od 112017 dla każdego repa, w następujący sposób: gdzie każdy wiersz reprezentuje 12 miesięczna średnia ruchoma dla wspomnianego przedstawiciela w określonym czasie. Znalazłem kilka przykładów, które były niewiele blisko i próbowałem ich bez skutku. Wydaje się, że dodanie grupy przez komponent rep jest głównym odejściem od innych przykładów. Chodzi o to, ile mi się udało: ta kwerenda wydaje się generować ogólną średnią lub sumę, ponieważ nie ma zgrupowania w skorelowanym podukwerem. Kiedy próbuję grupować się, pojawia się błąd, który może zwrócić tylko jeden wiersz. zapytał 10 października 13 o 14: 47Kalkulowanie łącznych sum i średnich kroczących w programie Microsoft Access z dostępem do statystyk całkowitych Firma Microsoft Access nie oferuje wbudowanych funkcji obliczania bieżących sum. Całkowite sumy są podsumowaniami dla pewnej liczby rekordów. Na przykład 30-dniowa średnia ruchoma, którą chcesz obliczyć dla każdego rekordu w oparciu o jej wartość i poprzednie 29 rekordów. Na szczęście Total Access Statistics wykonuje szeroki wachlarz ogólnych obliczeń w swoich rekordach: wykonaj podsumowania dla ruchomych zestawów rekordów do obliczania sum, średnich, minimalnych, maksymalnych, zakresu, mediany, odchylenia standardowego itd. Umieść wartość podsumowującą w pole w źródle danych Analiza danych w tabelach i zapytaniach Access (łącznie z połączonymi danymi) Wsparcie grupowania na wartości pól, dzięki czemu można przeprowadzić wiele analiz w jednym przejściu Sumy bieżące zostały dodane do Statystyka całkowitego dostępu dla programu Microsoft Access 2007 i X.7 wersje programu Access 2003 i starsze. Uruchamianie sumy całkowitej Omówienie Całkowite sumy są obliczeniami dla określonej liczby rekordów w tabeli lub zapytaniu. Łatwe generowanie wyników, takich jak sumy robocze, średnie, minimalne, maksymalne, mediana itd. I umieszczanie ich w polach w źródle danych. Obliczenia te są dostępne: Średnia (Średnia) Liczba obserwacji Sum Sum Kwad Minimalny Maksymalny Zakres Odchylenie standardowe Współczynnik wariancji wariancji Błąd standardowy Mediana Tryb Tryb Liczba Geometryczny Średni Harmoniczny Średnia Root Średnia kwadratowość Skurwa Kurtosis Standard Błąd błędu Standardowa Błąd Kurtosis Do pięciu pól w tabeli mogą być aktualizowane jednocześnie. Jak obliczane są łącznie wartości całkowite Na przykład możesz obliczyć średnią z ostatnich 10 rekordów. Ta średnia ruchoma jest określana na podstawie kolejności sortowania danych, obliczając średnią dla tych 10 rekordów i umieszczając ją w polu wyznaczonym przez rekord 10. Dla rekordu 11 rekord 1 jest ignorowany i obliczenia są wykonywane dla rekordów od 2 do 11 itd. Podobnie jak inne funkcje, możesz określić pola grup, tak aby każda grupa miała własne sumy bieżące. Ta funkcja nie tworzy osobnej tabeli. Wyniki są umieszczane w polach podanych w źródle danych. Uruchamianie wyboru sumy końcowej Ekran wyboru pola dla sumy całkowitej umożliwia określenie pól do posortowania, a pola do umieszczenia wyników: Pola grup umożliwiają wygenerowanie osobnego zestawu analiz dla każdej kombinacji unikalnych wartości wśród pól grupowych. Pola sortowania określają porządek sortowania rekordów w celu obliczenia bieżących wartości. Określ co najmniej jedno pole sortowania. Może to obejmować pole, na które wykonujesz obliczenia. Pola posortowane są w porządku rosnącym. Określ maksymalnie pięć pól do aktualizacji z sumami. Upewnij się, że pola są typami danych, które mogą przechowywać dane, które chcesz wprowadzić do nich. Na przykład powinny być podwajane, jeśli spodziewasz się zapisać takie wartości, jak średnie (średnie), wariancja itp. Uruchamianie Total Options Po wybraniu pól przedstawione są opcje Running Totals: Field to Analyze Określić pole, na którym opierają się obliczenia. To pole może stanowić pole, które zostało określone jako pole sortowania. Na przykład możesz obliczyć średnią ruchu na podstawie sprzedaży posortowanej w kolejności rosnącej. Liczba rekordów do obliczania sumy całkowitej Określ liczbę rekordów w ruchu. Jeśli podasz 0, sumy są obliczane dla każdego rekordu. Jeśli wpiszesz określony numer, obliczenia opierają się na tej liczbie rekordów. Pierwszy rekord jest pomijany, gdy następna jest dodawana, a obliczenia są oparte na tym ruchomym zestawie rekordów. Początkowe zestawy rekordów Jeśli określisz liczbę rekordów do obliczania sumy, istnieje możliwość określenia, co dzieje się przed osiągnięciem tej liczby rekordów. Należy pominąć lub obliczyć sumy w oparciu o przetwarzane rekordy. Wybierz Oblicz, aby wyświetlić bieżące sumy niezależnie od tego, czy liczba przenoszonych rekordów jest osiągnięta. Wybierz opcję Pozostaw puste, aby mieć tylko bieżące sumy, gdy osiągnięta została określona liczba rekordów bez żadnych obliczeń dla mniejszej liczby rekordów. Typ obliczania Dostępnych jest wiele typów obliczeń. Określ jedno, które chcesz dla każdego wybranego pola aktualizacji. Uruchamianie wyników sumy wyników Wyniki sumy roboczej są umieszczane w określonych polach aktualizacji: Przykład sumy całkowitej w czterech polach tej tabeli programu Microsoft Access W tym przykładzie dane zostały posortowane według daty i numeru zamówienia wraz z obliczeniami dotyczącymi sprzedaży pole. Zwróć uwagę na wartości w polu RunningCount wzrastając od 1 do 10. Kiedy osiągnie 10, pozostaje 10, ponieważ jest to maksymalna liczba rekordów w ruchu. Pole RunningTotal pokazuje sumę sprzedaży nad rekordami w ruchomym zestawie rekordów. Ponieważ została wybrana opcja obliczania wartości początkowego zestawu rekordów (przed osiągnięciem 10 rekordów) wyświetlane są wartości. W przeciwnym razie pierwsze 9 rekordów miałoby wartości null. Kreator interaktywny i interfejsy programistyczne programu VBA Statystyka całkowitego dostępu umożliwia interaktywne generowanie obliczeń za pośrednictwem interfejsu kreatora, aby łatwo wybrać źródło danych, pola i analizy bez programowania. Po określeniu wyboru są automatycznie zapisywane jako scenariusz w bazie danych, dzięki czemu można je ponownie uruchomić w przyszłości, gdy dane ulegną zmianie. Dostępna jest również funkcja statystyki VBA, dzięki czemu można generować te wyniki z kodu. Możesz łatwo uruchomić dowolny zapisany scenariusz, wywołując pojedynczą procedurę w bibliotece VBA programu Total Access Statistics. Biblioteka zawiera bezpłatną licencję czasu wolnego od licencji, dzięki czemu można ją umieścić w bazie danych programu Access i dystrybuować ją do właścicieli innych firm. Wywołaj funkcję z przycisku zdarzenia OnClick lub innego procesu, a wyniki są generowane dla Ciebie. Możesz nawet ukryć wbudowany interfejs użytkownika, dzięki czemu użytkownicy nawet nie wiedzą, że działa Total Access Statistics. Theyll jest zdumiony swoimi umiejętnościami analizy statystycznej Dodatkowe szczegóły obliczeń dostępne w Statystyka całkowitej dostępności Poniżej znajduje się kilka dodatkowych zasobów i szczegółów dotyczących niektórych analiz danych, które można wykonać w danych programu Microsoft Access za pomocą statystyk dostępu całkowitego: Kalkulatory przepływu finansowego Wykorzystaj dyskontowane środki pieniężne przepływy i stopy procentowe do generowania wartości bieżącej netto, wartości bieżącej (PV), wartości przyszłej (FV), wewnętrznej stopy zwrotu (IRR), zmodyfikowanej wewnętrznej stopy zwrotu (MIRR) dla okresowych i zależnych od daty płatności i wpływów. Obliczenia procentowe Oblicz różne typy percentyli: mediana, kwarty, kwintile, octyle, decile, percentyle, każdy X.5 percentyla i umieść je w tabeli lub zaktualizuj istniejące pole z wartością percentile zapisu. Uruchamianie sumy całkowitych i średnich kroczących Podsumuj ruchomy zestaw rekordów do obliczania bieżących sum, średnich kroczących, minimalnych, maksymalnych, zakresu, median, odchylenia standardowego itp. Normalizacja danych (Transponowanie danych) Przenoszenie nie znormalizowanych danych, dzięki czemu można łatwo analizować i utrzymywać to. Regresje Proste, wielokrotne i wielokrotne regresje z obliczaniem współczynników równań, ANOVA i tabelami crosstabów resztowych i chi-kwadratem Zaawansowane zestawienia krzyżów z odsetkiem wierszy, kolumny i sumy wyświetlane jako rekordy lub kolumnyMyślenie Obliczanie średnie Przeprowadzenie Średnia kwota Obliczanie średniej ruchome Próbuję obliczyć do obliczania średniej ruchomej dla szeregu danych. Chcę wygenerować średnią ruchoma dla każdego punktu w danych, aby pokazać na wykresie. Poniżej znajduje się przykład wsparcia MS. Poszedłem do listu, ale moje nie robi średniej ruchomej. Powtarza ten sam punkt danych w kółko (pierwszy punkt danych). Więc nie wierzę, funkcja jest znalezienie startdate w MyRST. Seek linii, a więc po prostu zwracając pierwszy punkt danych. Wreszcie (może sprawi, że będzie to naprawdę łatwe) Jestem zdezorientowany, jak działają indeksy. Myślałem, że możesz mieć tylko jeden klucz podstawowy, ale najwyraźniej możesz utworzyć wiele poleceń. Próbowałem to zrobić z następującą kwerendą definicji danych: ALTER TABLE Tabela 1 DODAJ KONIECZNIE NIEZBADZIE UNIQUE (CurrencyType, TransactionDate) Przepraszam za długość tego postu. Doceniam twoją pomoc. Poniższa funkcja próbki oblicza średnie ruchome w oparciu o tabelę z wieloma polami. W tym przykładzie użyto tygodniowych wartości walut obcych. Aby utworzyć przykładową funkcję, wykonaj następujące kroki: Utwórz poniższą tabelę i zapisz ją jako Tabela 1: Tabela: Tabela1 --------------------------------------- -------------- Nazwa pola: CurrencyType Primary Key Typ danych: Rozmiar pola tekstowego: 25 Nazwa pola: TransactionDate Primary Key Data Type: DateTime Format: Short Data Nazwa pola: Rate Type danych: Currency Dziesiętne miejsca: 4 Wyświetlanie tabeli w widoku arkusza danych i wprowadź następujące wartości: CurrencyType TransactionDate Rate ------------------------------- ------------- Yen 8693 0.0079 jen 81393 0.0082 jen 82093 0.0085 jen 82793 0.0088 jen 9393 0.0091 Marka 8693 0.5600 znak 81393 0.5700 znak 82093 0.5800 znak 82793 0.5900 znak 9393 0.6000 Otwórz nowy moduł i wpisz następujące funkcje: Function MAvgs (Okresy jako Integer, StartDate, TypeName) Dim MyDB Jako bazy danych, MyRST As Recordset, MySum jako podwójne wymiary i, x Ustawianie MyDB CurrentDb () Ustaw MyRST MyDB. OpenRecordset (Table1) On Error Powrót do następnego MyRST. Index Okresy podstawoweKey x - 1 Sklep ReDim (x) MySum 0 Dla i 0 Do x MyRST. MoveFirst MyRST. Seek, TypeName, StartDate Te dwie zmienne powinny być w takiej samej kolejności, co pola głównych kluczy w tabeli. Store (i) MyRSTRate Jeśli i lt x Następnie StartDate StartDate - 7 W tym przypadku 7 przyjmuje dane tygodniowe 1 dla danych dziennych. Jeśli StartDate lt 8693 Then MAvgs Null: Wyjście z funkcji 8693 zastępuje najwcześniejszą datą danych w tabeli. MySum Store (i) MySum Next i MAvgs Okresy MySum MyRST. Close End Function Utwórz następujące zapytanie w oparciu o tabelę Table1: Query: Query1 --------------------- ---------------------------------- Pole: pole CurrencyType: Pole TransactionDate: Pole Rate: Expr1: MAvgs (3 , TransactionDate, CurrencyType) UWAGA: Ta kwerenda generuje trzytygodniową średnią ruchliwą danych Rate. Aby obliczyć dłużej lub krótszej średniej ruchomej, zmień numer 3 w kolumnie Expr1 kwerendy na wartość, którą chcesz obliczyć. Uruchom zapytanie. Warto zauważyć, że dla każdej waluty widzisz następującą średnią z trzech tygodni. Wartość Null wskazuje, że nie było wystarczająco dużo wcześniejszych wartości, aby obliczyć średnie tygodnie. CurrencyType TransactionDate Rate Expr1 Mark 080693 0.5600 Mark 081393 0.5700 Mark 082093 0.5800 0.57 Mark 082793 0.5900 0.58 Mark 090393 0.6000 0.59 Yen 080693 0.0079 jen 081393 0.0082 jen 082093 0.0085 0.0082 jen 082793 0.0088 0.0085 jen 090393 0.0091 0.0088 RE: Przesuwanie średniej kalkulacji dhookom (programator) 28 Jun 10 21:15 Jak stary jest ten kod To doesnt wyraźnie używać DAO i nie wspomina, że ​​to przyzwyczajenie pracy z połączonych tabel. Dim MyDB jako DAO. Database, MyRST jako DAO. Recordset. Chciałbym użyć podkwerendy, a nie zestawu rekordów. Może wyglądać mniej więcej tak: SELECT CurrencyType, TransactionDate, Rate, (SELECT Avg (Rate) FROM Table1 B WHERE A. CurrentType B. CurrencyType AND A. TransactionDate MIĘDZY B. TransactionDate - 14 AND B. TransactionDate) FROM Table1 A RE: Moving Średnie obliczenia To jest naprawdę doskonałe. Doceniam twoją pomoc. Jednak dany kod oblicza 14-dniową średnią ruchową (średnia średniej ruchomej w rekordzie dla pierwszego dnia przeciętnego, gdzie chciałem, aby średnia była wsteczna, zapisana w rekordzie 14). Zmieniłem się nieco nieco na następujące i wydaje się, że działają SELECT A. CurrencyType, A. TransactionDate, A. Rate, (SELECT Avg (Rate) z tabeli 1 B WHERE A. CurrencyType B. CurrencyType I B. TransactionDate między A. TransactionDate - 14 AND A. TransactionDate) AS Expr1 FROM Table1 AS A Widać, wszystko, co zrobiłem, to wymiana A dla B w klauzuli where. To dla mnie naprawdę duża pomoc i naprawdę doceniam to. Nie widziałem kodowania tak przedtem i szczerze mówiąc, naprawdę nie rozumiem tego. Nie wiem, jak SQL rozumie, co B i A są. Zakładam, że tworzą jakieś alternatywne odniesienia do tabeli 1. Jeśli możesz dać wskazówki, na pewno bym to docenił. Jestem wdzięczny za naukę czegoś nowego o VBASQL i naprawdę doceniam Twoją pomoc RE: Przenoszenie średniej kalkulacji PHV (MIS) 29 Jun 10 12:22 rozumie, co B i A czy są to aliasy RE: Przenoszenie średniej kalkulacji Dziękujemy, PHV. To jest sensowne już RE: Przenoszenie średniej kalkulacji joshery420 (TechnicalUser) 6 Jul 10 15:06 Wow, nigdy nie spojrzał na widok SQL przed. Bardzo pomocny. Próbuję uzyskać ten kod do działania w moim własnym zestawie danych i utknęło w jeden szczególny problem. pd2004, nie wiesz, czy nowy kod podkwerendy działał tak samo jak stary kod VBA, czy nie, ale z moimi danymi nadal pokazuje się średnią kroczącą, nawet jeśli nie ma wystarczająco dużo dni, aby utworzyć tę średnią. na przykład Jeśli Im wykona 7 dniową średnią kroczącą, dzień 1 pokazuje te same dane w kolumnie 7DayAvg, co w kolumnie danych dziennych. Dzień 2 pokazywałby średnią z dni 1 i 2 itd. Czy każda z was wie, jak to naprawić przez przypadek Również dzięki wielkiemu kodeksowi PHV. RE: Przeniesienie średniej kalkulacji Joshery420 (TechnicalUser) 6 Jul 10 15:08 Woops, oznaczało dzięki dhookom za wskazówkę, a nie PHV. Ale hej, dzięki tobie. XD RE: Przenoszenie średniej kalkulacji Opuszczam tutaj najlepsze rozwiązania dla profesjonalistów, ale w moim oryginalnym artykule można zobaczyć, jak przykład pomocy firmy Microsoft próbuje to obsłużyć. Oto kod: Jeśli StartDate lt 8693 Następnie MAvgs Null: Wyjście z funkcji 8693 jest zastępowane najwcześniejszą datą danych w tabeli. Właśnie wychodzą z funkcji, jeśli data nie odpowiada kryteriom. Nie wiem, czy możesz wstawić coś takiego do kodu aliases dostarczonego przez dhookem. Nie podoba mi się, jak sobie z tym poradzić, i podejrzewam, że dhookem zapewni znacznie bardziej eleganckie rozwiązanie. Dla moich celów problem, który opisujesz, nie jest problemem, ale będę zainteresowany widzeniem jakichkolwiek rozwiązań. RE: Przeniesienie średniej kalkulacji dhookom (programista) 6 Jul 10 17:05 Możesz spróbować użyć IIf (), aby przetestować licznik liczby rekordów. Uwaga: testowany kod notatnika jest następujący: SELECT A. CurrencyType, A. TransactionDate, A. Rate, IIF ((SELECT Count (Rate) FROM Table1 C WHERE A. CurrentType C. CurrencyType I C. TransactionDate between A. TransactionDate - 14 AND A. TransactionDate) 7, (SELECT Avg (Rate) z tabeli 1 B WHERE A. CurrencyType B. CurrencyType AND B. TransactionDate MIĘDZY A. TransactionDate - 14 AND A. TransactionDate), Null) AS Expr1 FROM Table1 AS A RE: Przenoszenie średniej obliczeńMoving Średnia w Query Re: Przenoszenie średniej w kwerendzie Czy chodziło Ci o ostatnie cztery tygodnie danych (więc zmienia się co tydzień) Jakie dane w polu YearWeek wyglądają tak, jak jest sformatowane jako Tekst liczby WSKAZÓWKI DOTYCZĄCE ROZWIĄZANIA EXCEL 1 Użyj wbudowanej Pomocy dołączonej do programu ExcelAccess 2. Użyj funkcji wyszukiwania na tej płycie 3. Wiele kodów VBA można nabyć za pomocą programu Macro Recorder. Daj człowiekowi rybę, karmi go na jeden dzień. Naucz człowieka do połowu, karmi go na całe życie Gerrit. B powiedział: 6 maja 2017 05:31 Re: Przeprowadzka Średnia w Query Joe4, Nie chcę obliczyć średnią ruchoma w ciągu 4 tygodni. Pole YearWeek wygląda na rok 201701 na tydzień 1/2017 i jest sformatowane jako liczba. Re: Przenoszenie średniej w kwerendzie OK, niezależnie od odpowiedzi na poprzednie pytania, myślę, że mam rozwiązanie, które powinno działać w każdym przypadku. Najpierw utworzyłem definiowaną przez użytkownika funkcję, która konwertuje Twój tydzień na całkowitą liczbę tygodni. To trwa rok i mnoży się o 52, a następnie dodaje miesiące w ostatnich dwóch pozycjach liczbowych. Ten UDF wygląda następująco: Uwaga, nie ma potrzeby używania UDF, ponieważ możesz używać tej formuły bezpośrednio w swoich zapytaniach, ale ponieważ ją ponownie używam, często jest to łatwiejsze i sprawia, że ​​twoje formuły są czytelniejsze tą drogą. Następnie utworzyłem kwerendę, aby obliczyć maksymalną liczbę YearWeek dla każdej kombinacji identyfikatora produktu AreaProduct. Po prostu pogrupowałam pola Field i Product ID i wziąłem maksymalną wartość YearWeek (jeśli nie znasz Aggregate Queries, polecam sprawdzenie ich w plikach pomocy wbudowanych w Access). Zakładając, że jest to nazwa tabeli MyTable, oto jaki ma kod SQL tego zapytania: Nazwałem to zapytanie MyMaxQuery. Następnie utworzyłem nową kwerendę, do której dołączamy do tabeli oryginalnej (MyTable) do tej kwerendy w polach Obszar i ID produktu. Następnie utworzyłem obliczone pole o nazwie Check, gdzie sprawdzam, czy pole YearMonth jest większe lub równe wartości Max YearWeek (z poprzedniego zapytania) minus 3. Jeśli tak, zwracam wartość 1. I następnie dodaj kryteria do tego zapytania, aby zwrócić tylko wartości równe 1. W ten sposób ogranicza się dane do ostatnich 4 miesięcy. Teraz zmieniam tę kwerendę na Aggregate Query i weź średnią ilości, aby uzyskać to, czego chcesz. Kod SQL dla tego zapytania wygląda następująco: WSKAZÓWKI DOTYCZĄCE ROZWIĄZANIA EXCEL 1. Użyj wbudowanej Pomocy dołączonej do programu ExcelAccess 2. Użyj funkcji wyszukiwania na tej płycie 3. Wiele kodów VBA można uzyskać przy użyciu nagrywarki Macro . Daj człowiekowi rybę, karmi go na jeden dzień. Naucz człowieka, aby łowić, karmić go na całe życie