Пример за изчисляване на общата сума в таблица от база данни Microsoft Accessбез използване на SQL заявка

Има моменти, когато в таблица на база данни трябва да изчислите сумата числови стойностинякаква колона. За това има различни методиизчисления, едно от които на база използвайки подходящата SQL заявка.

В тази задача, използвайки примера на таблица на Microsoft Access, общата сума се изчислява без използване на SQL заявка. Освен това се изчислява средноаритметичното на стойностите на клетките на посочената колона.

Използвайки даден пример, можете да създавате всякакви числени изчисления върху набора от записи на дадената колона.

Задачата

В резултат на това низът ConnectionString се формира в компонента ADOConnection1 (фиг. 2). Този ред указва типа доставчик на данни и пълния път до файла на базата данни.

Ориз. 2. ConnectionString на компонента ADOConnection1

1. Настройка на компонента ADOConnection1, ADOTable1, DataSource1, DBGrid1.

За да покажете таблица на база данни, трябва да зададете следните свойства на компонента:

– в компонента ADOConnection1 свойството LoginPrompt = “false” (фиг. 3) (анулиране на искане за потребителско име и парола за достъп до базата данни);

– в компонента ADOTable1 свойството Connection = “ADOConnection1” (фиг. 4);

– в компонента DataSource1 свойството DataSet = “ADOTable1” (фиг. 5);

– в компонента DBGrid1 свойството DataSource = “DataSource1” (фиг. 6);

– в компонента ADOTable1 свойството TableName = “Worker” (фиг. 7);

– в компонента DBGrid1 от свойство Options задайте опцията dgEditing = false (забрана за въвеждане на данни в клетките на таблицата директно от мрежата DBGrid1) (фиг. 8).

Ориз. 3. Свойството LoginPrompt на компонента ADOConnection1

Ориз. 4. Свойството Connection на компонента ADOTable1

Ориз. 5. Свойството DataSet на компонента DataSource1

Ориз. 6. Свойството DataSource на компонента DBGrid1

Ориз. 7. Свойството TableName на компонента ADOTable1

Ориз. 8. Опцията dgEditing на свойството Options на компонента DBGrid1

1. Активиране на маса.

За да покажете данни в таблица, направете следното (фиг. 9):

– изберете таблица ADOTable1;

– свойство Active = true.

След това данните от таблицата ще бъдат показани в DBGrid1.

Ориз. 9. Активно свойство на компонента ADOTable1

1. Регулиране на размерите на компоненти и форми.

Следващата стъпка е да коригирате размерите и позициите на компонентите във формуляра, както е показано на фигура 10.

Ориз. 10. Основен формуляр за кандидатстване

1. Скриване на полето ID_Worker в DBGrid1.

За да изглежда показаната таблица правилно, трябва да скриете полето ID_Worker, което е ключовото поле. В таблицата това поле е броячът. При добавяне на нов запис стойността в това поле се генерира автоматично (увеличава се с 1).

Първо трябва да извикате редактора на полета (“Fields Editor…”) на компонента ADOTable1 от контекстното меню (фиг. 11).

Ориз. 11. Извикване на редактора на полето на компонента ADOTable1

Отваря се прозорецът Form1.ADOTable1. В този прозорец, като използвате мишката, трябва да извикате контекстното меню. В контекстното меню изберете командата „Добавяне на всички полета“.

В резултат на това прозорецът на редактора ще изглежда както е показано на фигура 12.

Ориз. 12. Редактор на полета

Редакторът на поле Form1.ADOTable1 показва всички полета на таблицата Worker. За да изтриете полето ID_Worker, трябва да щракнете с десния бутон върху реда ID_Worker и да изберете командата Изтриване в контекстното меню. След това можете да затворите редактора.

В резултат на това в таблицата DBGrid1 ще бъдат показани само две полета (фиг. 13).

Ориз. 13. Работен табличен дисплей с две полета

1. Задаване на изходен филтър с два знака след десетичната запетая в поле Заплата.

За да може полето Заплата да покаже коректно стойността на сумата (2 знака след десетичната запетая), трябва да изпълните следните стъпки.

Изберете компонента ADOTable1. Извикайте редактора на полета „Редактор на полета…“, както е описано в параграф 6. В резултат на това ще се отвори списък с две полета Име и Заплата. Маркирайте реда, наречен Заплата. Обект с име ADOTable1Salary ще бъде активиран в инспектора на обекти.

Следващата стъпка е да зададете стойността "0.00" в Инспектора на обекти в полето " Формат на дисплея" (фиг. 14).

Ориз. 14. Задаване на изходен формат в полето Заплата на обекта ADOTable1

След извършените действия полето Заплата в таблицата ще се покаже с точност до 2 знака след десетичната запетая.

1. Въвеждане на вътрешни променливи.

За да запазите сумата и средноаритметичните данни, трябва да въведете вътрешни променливи с имената sum и avg в текста на класа на формата TForm1.

Променливите се въвеждат в частния раздел. Кодовият фрагмент на формуляра TForm1 клас изглежда така:

... Тип TForm1 = клас(TForm) ADOConnection1: TADOConnection; DataSource1:TDataSource; ADOTable1: TADOTable; DBGrid1: TDBGrid; Статичен текст1: Tстатичен текст; Статичен текст2: Tстатичен текст; Статичен текст3: Tстатичен текст; ADOTable1Name: TWideStringField; ADOTable1Заплата: TFloatField; процедура FormActivate(Подател: TObject); процедура FormClose(Подател: TObject; var Action: TCloseAction); частен( Частни декларации ) sum:real; // сума ср.:реално; // средно аритметично публичен(Публични декларации) край; ...

1. Програмиране на събитието за активиране на формата.

Веднага след като потребителят изтегли програмата, трябва незабавно да изчислите стойностите на сумата и средната аритметична стойност в таблиците.

Следователно трябва да програмирате събитието OnActivate на Form1. Събитието OnActivate ще бъде извикано, когато формулярът е активиран, след като програмата бъде стартирана за изпълнение.

Подробно е описан пример за програмиране на събития в Delphi.

В нашия случай манипулаторът на събития изглежда така:

процедура TForm1.FormActivate(Подател: TObject); вар f:TFeld; // допълнителна променлива от тип "Поле" започвам // 1. Проверете дали има записи в таблицата ако ADOTable1.RecordCount = 0 тогава изход; // 2. Деактивирайте визуализацията в DBGrid1 ADOTable1.DisableControls; // 3. Обхождане на всички записи на таблицата Worker // 3.1. Отидете на първия пост ADOTable1.First; // 3.2. Нулиране на суматасума:= 0; // 3.3. Вземете стойността на заплатата от първия запис f:= ADOTable1.FieldByName("Заплата"); // 3.4. Цикъл през записите в таблица // - проверка за достигане на края на таблицата докато ADOTable1.Eof<>вярнонаправи започвам // 3.4.1. Увеличете сумата sum:= sum + f.Value; // 3.4.2. Отидете на следващата публикация ADOTable1.Next; край; // 4. Изчислете средноаритметичното avg:= сума / ADOTable1.RecordCount; // 5. Попълване на низове от тип TStaticText StaticText1.Caption:= "Сума: " + FloatToStr(sum, ffFixed, 8, 2); StaticText2.Caption:= "Средна заплата: " + FloatToStr(avg, ffFixed, 8, 2); // 6. Разрешаване на визуализация в DBGrid ADOTable1.EnableControls; край;

Нека обясним някои кодови фрагменти.

Методите DisableControls и EnableControls са отговорни за деактивирането и активирането на визуални контроли, свързани с набора от данни (база данни). Извикването на метода DisableControls може значително да ускори процеса на преминаване през записите, тъй като ще принуди приложението да не прекарва време в преначертаване на съдържанието на контролата DBGrid1 с всяка промяна в записа.

Имот

ADOTable1.RecordCount

определя броя на записите в таблицата.

ADOTable1.Първо

задава първия запис на таблицата като активен.

ADOTable1.FieldByName()

ви позволява да получите обект от тип TField за даденото поле. След това в този обект можете да получите информация от текущия запис. В програмата за достъп до стойностите на записите на полето Заплата се използва допълнителна променлива f от типа TField. За да прочетете стойността в текущия запис на полето Заплата, просто се обадете на оператора

f.Стойност

Имот

ADOTable1.Eof

става истина, ако бъде достигнат краят на таблицата.

ADOTable1.Напред

осъществява прехода към следващия запис в таблицата.

Резултатът от сумата се показва в свойството Caption на компонента StaticText1.

Средната аритметична стойност се показва в свойството Caption на компонента StaticText2.

1. Стартиране на приложението за изпълнение.

Сега можете да стартирате приложението за изпълнение.

Тази статия описва как да преброите данните, върнати от заявка. Например във формуляр или отчет можете да преброите броя на елементите в едно или повече полета на таблица или контроли. Можете също да изчислявате средни стойности, да намирате най-големите и най-малките стойности, най-старата и най-късната дата. Освен това Access предоставя функция, наречена общ ред, която ви позволява да броите данни в таблица, без да променяте структурата на заявката.

Изберете желаното действие

Методи за преброяване на данни

Можете да преброите броя на елементите в поле (колона със стойности), като използвате функцията Номер. функция Номерпринадлежи към редица функции, наречени агрегатни. Агрегираните функции извършват изчисления върху колони с данни и връщат една стойност. Освен функция Номер, Access има следните агрегатни функции:

Сумада сумирате колони от числа;

Средно аритметичнода се изчисли средната стойност в колона от числа;

Максимумнамиране на най-голямата стойност в поле;

минимумнамиране на най-малката стойност в поле;

Стандартно отклонениеда се оцени разпространението на стойностите спрямо средната стойност;

дисперсияза изчисляване на статистическата дисперсия на всички стойности в колона.

Access предоставя два начина за добавяне на функция Брояи други агрегатни функции в заявка. Можеш:

Отворете заявката в изглед на лист с данни и добавете общ ред. Общият ред ви позволява да използвате агрегатни функции в една или повече колони в резултатите от заявка, без да се налага да преструктурирате заявката.

Създайте обобщена заявка. Обобщената заявка изчислява междинни суми за групи от записи. Например, ако искате да изпълните междинна сума на всички продажби по град или тримесечие, бихте използвали обобщена заявка, за да групирате записите по желаната категория и след това да сумирате всички продажби. От друга страна, като използвате общия ред, можете да изчислите общата сума за една или повече колони (полета) с данни.

Забележка:Следващите раздели на тази статия описват подробно използването на функцията Сума, обаче, имайте предвид, че можете да използвате други агрегатни функции в общите редове и заявки. Допълнителна информациявижте раздела по-долу за използване на други агрегатни функции.

За повече информация относно начините за използване на други агрегатни функции вижте.

Следващите раздели описват стъпките, които трябва да следвате, за да добавите общ ред и описват как да използвате обща заявка за преброяване на данни. Имайте предвид, че функцията Номерработи с Голям бройтипове данни в сравнение с други агрегатни функции. функция Номерможе да се използва за всякакъв тип поле, с изключение на тези, които съдържат сложни, повтарящи се скаларни данни, като например комбинирано поле с много стойности.

От друга страна, много агрегатни функции работят само с данни в полета, които имат определен типданни. Например функцията Сумаработи само с типове данни "Число", "Реално" и "Валута". За повече информация относно типовете данни, необходими за всяка функция, вижте раздела по-долу.

За обща информация относно типовете данни вижте статията Промяна на типа данни за поле.

Преброяване на данни с общ ред

За да добавите общ ред към заявка, отворете го в изглед на лист с данни, добавете ред и след това изберете функция Номерили друга агрегатна функция като Сума, минимум, Максимумили Средно аритметично. Този раздел обяснява как да създадете проста заявка за избор и да добавите общ ред.

Създайте проста заявка за избор

Добавяне на общ ред

Скриване на общия ред

В раздела У домав група ВписванияНатисни бутона Резултати.

За повече информация относно използването на общия ред вижте Показване на суми по колони в таблица.

Преброяване на данни с обща заявка

Когато искате да преброите някои или всички записи, върнати от заявка, можете да използвате обобщена заявка вместо общ ред. Например, можете да преброите общия брой транзакции или броя на транзакциите в определен град.

Обикновено общата заявка се използва вместо общ ред, когато искате да използвате резултатната стойност в друга част от базата данни, като например отчет.

Преброяване на всички записи в заявка

Преброяване на записи в група или категория

Справочна информация за агрегатни функции

Следната таблица изброява и обозначава обобщени функции на Access, които могат да се използват в общ ред и в заявки. Имайте предвид, че Access предоставя повече обобщени функции за заявки, отколкото за общ ред. Освен това, когато работите с проект на Access ( външна основаДостъп до данни, които се свързват с база данни на Microsoft SQL сървър) можете да използвате разширения набор от агрегатни функции, предоставен от SQL Server. За повече информация относно тях вижте Microsoft SQL Server Books Online.

функция	Описание	Поддържани типове данни
Сума	Сумирайте елементите в колона. Подходящ само за цифрови и парични данни.
Средно аритметично	Изчислява средната стойност за колона. Колоната трябва да съдържа числови, парични стойности или стойности за дата или час. Функцията игнорира празни стойности.
Номер	Преброява броя на елементите в колона.	Всички типове данни, с изключение на сложни, повтарящи се скаларни данни, като например колона от списъци с много стойности. За повече информация относно списъци с много стойности вижте Най-добри практики за създаване и изтриванемногозначни полета в ръководстватапосветен на многозначни полета.
Максимум	Връща елемента с най-голяма стойност. За текстови данни най-голямата стойност ще бъде последната стойност по азбучен ред, а Access не е чувствителен към главни и малки букви. Функцията игнорира празни стойности.	"Число", "Реално", "Валута", "Дата/час"
минимум	Връща елемента с най-малка стойност. За текстови данни най-малката стойност ще бъде първата стойност по азбучен ред, а Access не е чувствителен към главни и малки букви. Функцията игнорира празни стойности.	"Число", "Реално", "Валута", "Дата/час"
Стандартно отклонение	Показва колко стойностите се отклоняват от средната стойност. За повече информация относно тази функция вижте статията Показване на суми по колони в таблица.	"Число", "Реално", "Валута"
дисперсия	Изчислява статистическата дисперсия за всички стойности в колона. Подходящ само за цифрови и парични данни. Ако таблицата съдържа по-малко от два реда, Access връща празна стойност. За повече информация относно функциите за изчисляване на дисперсията вижте Показване на суми по колони в таблица.	"Число", "Реално", "Валута"

Във всеки отчет на Access, който съдържа числа, можете да използвате общи суми, средни стойности, проценти или подредени суми, за да направите данните по-разбираеми. Тази статия обяснява как да добавите такива елементи към отчет.

В тази статия

Типове агрегати, които могат да се добавят към отчет

Следващата таблица изброява типовете агрегатни функции в Access, които можете да добавите към отчет.

изчисление	Описание	функция
	Сумирайте елементите в колона.
	Определя средната стойност на всички елементи в колона.
	Преброява броя на елементите в колона.
Максимална стойност	Връща елемента, който има най-голямата (числова или азбучна) стойност в колоната.
Минимална стойност	Връща елемента, който има най-малката (числова или азбучна) стойност в колоната.
Стандартно отклонение	Показва колко стойностите в колона се отклоняват от средната стойност.
дисперсия	Изчислява дисперсията за всички стойности в колона.

Добавяне на сума или друг агрегат в изглед на оформление

Режимът на оформление осигурява най-много бърз начиндобавяне на суми, средни стойности и други агрегати към отчет.

Данни(Източник на контрол) Израз, който извършва желаното изчисление. Ако отчетът има нива на групиране, Access също добавя текстово поле, което извършва същото изчисление във всеки раздел на груповата бележка.

За повече информация относно създаването на нива на групиране в отчети вижте Създаване на групиран или обобщен отчет.

Добавяне на сума или друг агрегат в изглед за проектиране

Конструкторът ви позволява да прецизирате разположението и външен видкрайни стойности. В групирани отчети можете да поставите общи суми и други агрегати в горния или долния колонтитул на всяка група. Агрегираните данни на ниво отчет могат да бъдат поставени в горния или долния колонтитул на отчета.

Access добавя текстово поле към заглавката на отчета и задава стойността му на неговите свойства Данни(Източник на контрол) Израз, който извършва желаното изчисление. Ако отчетът има нива на групиране, Access също добавя текстово поле, което извършва същото изчисление във всеки раздел на груповата бележка. Ако за имот Кумулативна сума(Текуща сума) е зададено на За всичко, тогава общата сума може да се повтори в заглавката на отчета. Създайте поле в него и задайте свойството му като стойност Данни(Control Source) името на полето, в което се изчислява натрупаната сума, например =[Сума на поръчката].

Редът Общо в Access ви позволява бързо да видите обобщена информация за данните в колоните на таблицата. Например, ако добавите ред „Общо“ към таблица с подробности за покупката, можете да покажете сумата от продажбите, общия брой единици или броя на закупените артикули.

Забележка:За да покажете сумата от стойностите на дадена колона, трябва да зададете типа данни на колоната на Числов, Дробен или Валутен. За нечислови колони можете да изберете само типа обобщение на броя на стойностите.

Добавяне на ред „Общо“.

Избор на вида на общата сума

След добавяне на линията резултативие избирате типа обща сума, показвана за всяка колона. Например агрегати като суми могат да бъдат показани, ако типът данни е число, десетичен знак или валута. Преброяването на стойности е възможно, ако типът данни е текстова стойност.

Нека направим така, че в този пример таблицата да показва сумата от стойностите в колоните Покупна ценаИ Закупени стокии общата сума за колоната Позициякакто е показано на изображението по-долу.

Как работят "Sum" и други агрегатни функции

Агрегираните функции извършват изчисления върху колони с данни и връщат един резултат. Те могат да бъдат полезни, ако трябва да изчислите една стойност, като например сума или средна стойност. Имайте предвид, че агрегатните функции се прилагат към колони с данни. Това може да изглежда очевидно, но когато проектирате и използвате бази данни, обикновено обръщате внимание на редове от данни и отделни записи, така че потребителите да могат да въвеждат данни в поле, да преместват курсора надясно или наляво, за да попълнят следващото поле и т.н. Обратно, вие използвате агрегатни функции, като се фокусирате върху групи от записи в колони.

Да приемем, че използвате Access за съхраняване и проследяване на данни за продажбите. С помощта на агрегатни функции можете да изчислите броя на продадените артикули в една колона, общата сума на продажбите във втората и средните продажби за всеки артикул в третата.

Следващата таблица описва агрегатните функции на Access, които са налични в реда Общо. Имайте предвид, че в Access има и други агрегатни функции, но те се използват в заявки.

функция	Описание	Поддържани типове данни	Наличен в реда "Общо"?
	Изчислява средната стойност за колона. Колоната трябва да съдържа числови, парични стойности или стойности за дата или час. Функцията игнорира празни стойности.
Брой стойности	Преброява броя на елементите в колона.	Всички типове данни, с изключение на сложни, повтарящи се скаларни данни, като например колона от списъци с много стойности. За повече информация относно списъци с много стойности вижте Най-добри практики за създаване и изтриване на полета с много стойности в ръководствата за полета с много стойности.
Максимална стойност	Връща елемента с най-голяма стойност. За текстови данни най-голямата стойност ще бъде последната стойност по азбучен ред, а Access не е чувствителен към главни и малки букви. Функцията игнорира празни стойности.	, "Дата и час"
Минимална стойност	Връща елемента с най-малка стойност. За текстови данни най-малката стойност ще бъде първата стойност по азбучен ред, а Access не е чувствителен към главни и малки букви. Функцията игнорира празни стойности.	"Числен", "Реален", "Валута", "Дата и час"
Стандартно отклонение		"Числен", "Реален", "Валута"
	Сумирайте елементите в колона. Подходящ само за цифрови и парични данни.	"Число", "Реално", "Валута"
дисперсия	Изчислява статистическата дисперсия за всички стойности в колона. Подходящ само за цифрови и парични данни. Ако таблицата съдържа по-малко от два реда, Access връща празна стойност. Вижте следващия раздел за повече информация относно функцията "Дисперсия".	"Числен", "Реален", "Валута"

Научете повече за функциите за стандартно отклонение и дисперсия

Функции Стандартно отклонениеИ дисперсияизчисляване на статистически стойности. По-специално, те се прилагат за стойности, които се намират в близост до тяхната средна стойност и се подчиняват на нормалния закон за разпределение (са върху крива на Гаус).

Да предположим, че избирате на случаен принцип 10 инструмента, направени на една и съща машина, и измервате тяхната якост на счупване за проверка на машината и контрол на качеството. Ако изчислите средната якост на счупване, ще видите, че за повечето инструменти якостта на счупване е близка до средната, но има и инструменти с по-високи и по-ниски стойности. Въпреки това, ако изчислите само средната якост на счупване, тази цифра няма да ви даде никаква информация за ефективността на контрола на качеството, тъй като няколко необичайно здрави или крехки инструменти могат да увеличат или намалят средната стойност.

Функции променливостИ стандартни отклоненияпосочете този проблем, като посочите колко близки са стойностите до средната стойност. За критична якост по-ниските числа, върнати от една от функциите, показват, че вашите производствени процеси работят добре, тъй като някои от тях имат ограничена степен, като над или под средната.

Подробно описаниедисперсия и стандартно отклонение е извън обхвата на тази статия. Повече информация за двете функции можете да намерите на уебсайтовете за статистика. При използване на функции дисперсияИ Стандартно отклонениезапомнете следните правила.