ГлавнаяMS ACCESS Сложение данных из символьных полей в запросе с группировкой (аналог функции Sum())
Сложение данных из символьных полей в запросе с группировкой (аналог функции 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
' первоначальное присвоение значения 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" для каждого сооружения.Она не является универсальной (т.е на все случаи жизни), но её можно дорабатывать для своих нужд.
А вот пример запроса с использованием этой функции
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
Коментарии (9)