Сложение данных из символьных полей в запросе с группировкой (аналог функции Sum()) |
Автор Joss | |||||||||||||||
11.08.2008 г. | |||||||||||||||
Иногда бывает необходимо сложить в запросе символьные поля. Например: имеется база данных о строениях. Одним из параметров описания строений является описание материалов, из которых здания построены. Для одного здания может быть использовано несколько материалов: кирпич, газосиликатный кирпич, бетонные блоки, бетонные перекрытия, дерево, металлоконструкции и т.п. Количество материалов может быть произвольным. Создаем в базе две таблицы - таблицу зданий (tblOsnFond) и таблицу материалов (tblMaterial). Потом создаем третью таблицу (tblOFmaterial), куда будем заносить связь между строениями и мотериалами, из которых оно построено. Всё довольно стандартно. Легко получить список материалов, из которых сделано строение, и легко получить список строений, в котором используется определенный материал. Но вот настала пора создавать отчет (или форму), в котором должен быть столбецы (поля) "Строение" и "Материалы", В поле "Материалы" должны быть перечислены все материалы, которые были использованы при строительстве. Использовать в запросе функцию Sum() для сложения (слияния) символьных значений не представляется возможным потому, что функция Sum() применима только для скалярных(!!!) значений. А для символьных она не работает. И действительно 3+2=2+3, но "3"+"2"<>"2"+"3" ("32"<>"23"). Для того, чтобы сложить значения символьного поля надо самому написать функцию сложения символьных (string) значений и подставить её в запрос. Эта функция должна быть расположена в отдельном модуле (а не в модуле формы или отчета) и иметь свойство Public Такую функцию уже можно подставлять в запрос. Вот пример функции: '--------------------------------------------------------------------------------------- ' Procedure : fnSumString ' DateTime : 16.06.2008 14:18 ' Author : DSonnyh ' Purpose : суммирование символьных полей в запросе '--------------------------------------------------------------------------------------- ' Public Function fnSumString(LngOsnFond As Long) As String Dim sResult As String On Error GoTo fnSumString_Error Dim RstX As ADODB.Recordset Dim strSQL As String Set RstX = New ADODB.Recordset strSQL = "SELECT * FROM qryOFmaterial WHERE IdOsnFond=" & LngOsnFond RstX.Open strSQL, CurrentProject.Connection, adOpenKeyset ' первоначальное присвоение значения sResult = "" ' проверка на наличие записей в запросе If RstX.RecordCount > 0 Then Do sResult = sResult & (IIf(Len(sResult) > 0, ", ", "") & Nz(RstX.Fields("Material"), "")) RstX.MoveNext Loop Until RstX.EOF End If RstX.Close Set RstX = Nothing fnSumString = sResult On Error GoTo 0 Exit_fnSumString: Exit Function fnSumString_Error: MsgBox "Ошибка " & Err.Number & " (" & Err.Description & ") в процедуре fnSumString в Module Module1" Resume Exit_fnSumString End Function Эта функция проводит суммирование значений поля "Material" для каждого сооружения.Она не является универсальной (т.е на все случаи жизни), но её можно дорабатывать для своих нужд. А вот пример запроса с использованием этой функции SELECT qryOFmaterial.IdOsnFond, qryOFmaterial.OsnFond, fnSumString(qryOFmaterial.IdOsnFond) AS MaterialSum FROM qryOFmaterial GROUP BY qryOFmaterial.IdOsnFond, qryOFmaterial.OsnFond; Вот собственно и всё. Но поскольку сейчас многие начинают переходить на MS SQL Server, то следует рассказать как эта задача решается и там. Там тоже используется пользовательская функция, но не написанная в программе, а хранимая на сервере. Решает она те же самые задачи. Вот её текст ALTER FUNCTION [dbo].[fn_OFmaterialS] (@id INTEGER) RETURNS NVARCHAR(100) AS BEGIN DECLARE @Result NVARCHAR(4000) SET @Result = '' SELECT @Result = @Result + case when [Material] Is null then '' else case when @Result<>'' then ', '+[Material] else [Material] end end FROM vw_OsnFondMaterial WHERE [IdOsnFond] = @Id RETURN @Result END Используемые источники: Сайт HIPROG.COM ( Слияние полей из разных строк запроса ) Сайт SQL.RU ( Сложение символьных полей в запросе) Ну и справочники и учебники по Access и MS SQL Server. К статье приложен небольшой пример в формате Access 2000. Просмотров: 19969
|