Сложение данных из символьных полей в запросе

Иногда бывает необходимо сложить в запросе символьные поля. Например: имеется база данных о строениях. Одним из параметров описания строений является описание материалов, из которых здания построены. Для одного здания может быть использовано несколько материалов: кирпич, газосиликатный кирпич, бетонные блоки, бетонные перекрытия, дерево, металлоконструкции и т.п. Количество материалов может быть произвольным.

Создаем в базе две таблицы - таблицу зданий tblOsnFond и таблицу материалов tblMaterial. Потом создаем третью таблицу tblOFmaterial, куда будем заносить связь между строениями и мотериалами, из которых оно построено. Всё довольно стандартно. Легко получить список материалов, из которых сделано строение, и легко получить список строений, в котором используется определенный материал. Но вот настала пора создавать отчет (или форму), в котором должен быть столбецы (поля) "Строение" и  "Материалы", В поле "Материалы" должны быть перечислены все материалы, которые были использованы при строительстве. Использовать в запросе функцию Sum() для сложения (слияния) символьных значений не представляется возможным потому, что функция Sum() применима только для скалярных(!!!) значений. А для символьных она не работает. И действительно:

3+2=2+3, но "3"+"2"<>"2"+"3" "32"<>"23"

Для того, чтобы сложить значения символьного поля надо самому написать функцию сложения символьных (string) значений и подставить её в запрос. Эта функция должна быть расположена в отдельном модуле (а не в модуле формы или отчета) и иметь свойство Public Такую функцию уже можно подставлять в запрос. Вот пример функции:

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

Обсудить на форуме...

Автор: Joss 30Kb Добавлен: 01.08.2008