Rambler's Top100
Российский фонд помощи
Навигация
Главная
MS ACCESS
VB
ASP
PHP
Наши друзья
Поиск
Форум
Лента новостей
Новый сайт

Online
Рассылки Subscribe.Ru
Работа с MS Access
Подписаться письмом
Реклама на сайте
 
Главная arrow MS ACCESS arrow Сложение данных из символьных полей в запросе с группировкой (аналог функции Sum())
Сложение данных из символьных полей в запросе с группировкой (аналог функции Sum()) Печать E-mail
Автор 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 LongAs 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.

Download now
Просмотров: 17803

  Коментарии (9)
 1 Написал(а) Александр, в 13:55 13.08.2008
ну, будем считать, что я все понял, хотя не мешало бы комментить не только самые элементарные строки типа: ...' первоначальное присвоение значения... 
;)
 2 Написал(а) Наталия, в 18:38 08.11.2008
Огромнейшее спасибо!!! 
Несколько дней мучалась и ведь задача-то типичная для БД, но нигде подсказки найти не могла. Супер!!! 
Код доступный, читается лекго и комментариев достаточно. 
СПАСИБО!
 3 Написал(а) irgendjemand, в 05:54 25.11.2008
Насчет fnSumString. Т.е. для каждой таблицы писать свою функцию? Как-то не по-программерски. Еще: испльзование IIF в VBA замедляет процедуру - каждый раз вычисляются обе ветки. 
 
admin: 
Хотелось бы научиться делать "по-программерски". Может приведете примерчик решения этой задачи? 
 4 Написал(а) Андрей Изранов, в 13:52 27.03.2009
Большое спасибо теперь я смогу до делать свою банк данных
 5 Написал(а) екеквк, в 13:44 15.10.2009
СПАСИБИЩЕ!
 6 Написал(а) ДФ, в 10:44 07.09.2010
Public Function DSUMSTR _ 
(EXPRESSION As String, DOMAIN As String, Optional WHERECONDITION As String = "", Optional POINT As String = ";") As String 
Dim rst As DAO.Recordset 
On Error GoTo Er_dsumstr 
DSUMSTR = "" 
If Len(WHERECONDITION) > 0 Then 
DOMAIN = DOMAIN & " WHERE " & WHERECONDITION 
End If 
Set rst = CurrentDb.OpenRecordset(DOMAIN & ";") 
With rst 
If rst.RecordCount > 0 Then 
.MoveFirst 
While Not .EOF 
DSUMSTR = DSUMSTR & nz(.Fields(EXPRESSION)) & POINT 
.MoveNext 
Wend 
End If 
End With 
Set rst = Nothing 
Exit Function 
Er_dsumstr: 
MsgBox "Ошибка DSUMSTR " & Err.Number & " (" & Err.Description & ")" 
End Function 
 
Public Sub tstdsum() 
Debug.Print DSUMSTR("NRaspFull", "select * from QvrShcPoz_RasporKol", "[IdPrice] = " & 1256) 
End Sub
 7 Написал(а) Татьяна, в 08:12 14.06.2011
Большое спасибо! Нужная функция. Логически знала как делать, но с языком слабо знакома, поэтому мне очень помогло.
 8 Написал(а) Ирина, в 18:25 23.04.2014
Выделяет RstX As ADODB.Recordset, какая-то ошибка... Что это может быть?
 9 Написал(а) osmor, в 06:38 24.04.2014
Ирина,  
1. вопрос лучше задавать в форуме hiprog.com/forum со ссылкой на статью 
2. Необходимо указывать текст ошибки. 
 
В вашем случае возможно не подключена библиотека adodb 
Без текста ошибки, более точно сказать не могу

Добавить коментарий
Имя:
E-mail
Коментарий:



Код:* Code

 
Реклама на сайте
HiProg.com - Технологии программирования
Rambler's Top100 TopList