Реализация сложной выборки из таблицы/запроса

jsofthome1

Уважаемые!Поделитесь мыслями(решениями) по такой задаче:Имеется исходная динамическая в обоих измерениях таблица(результат перекрестного запроса), где желтым - заголовки столбцов, зеленым - заголовки строкТребуется получить фиксированную по количеству строк (в данном случае 6) результирующую таблицу(запрос), где желтым - заголовки столбцов, зеленым - заголовки строк. Первая строка(cnt) - количество непустых значений, со второй по шестую(a,b,c,d,e) - последние пять непустых значений или 0.
15 ответов

jsofthome1

динамическая в обоих измерениях таблица(результат перекрестного запроса)
Чисто на SQL задача не решается.


jsofthome1

В исходной таблице (до перекрестного запроса) делаем группировку по желтому полю и условие на "не пусто". В том, что получилось, делаем самопальный счетчик (см. в факе), нумерующий записи от самых свежих назад. Из того, что получилось, делаем перекрестный запрос, где заголовками строк служат значения счетчика. Это мы получили 5 строк, кроме покрашенной коричневым. Коричневая строка - это аналогичный перекрестный запрос, но с одной строкой. Присоединяем ее через UNION.


jsofthome1

jsofthome1,а что дальше с результатом делать собираетесь--записать/дозаписать в некую таблицу--напечатать отчет и забытьстрочек 5 , столбиков ??столбики фиксированные или переменныеесли предположить, что это изделие ,то --появляются новые--старые аннулируютсядля людей --аналогично(поступают, увольняются)


jsofthome1

ПЕНСИОНЕРКА, возможно долго и нудно, но попытаюсь объяснить... :)(с 2009г. реализовано в excell и работает до сих пор, но есть причины перевода на access)это - учет заправки картриджейв исходной таблице:столбцы - усл.номера картриджейстроки - даты заправкицифры внутри - шифр произведенных работ (1-заправка, 2-замена барабана, 4-замена вала первичного заряда и т.д.)----------------------------------так-как все мы ленивые, при заправке естественно усл.номер картриджа на записываем в программу, заправили кучу, а в проге делаем авансовый отчет уже потом... дня через 2-3.НО! в бухгалтерии ведь тоже не дураки сидят, и может возникнуть вполне логичный вопрос - а че это катриж номер 12345 пять раз подряд менялся барабан?----------------------------------поэтому в результирующей таблице наглядно представлено, сколько раз конкретный картридж мы светили в авансовых и с какими целями, т.е., видим(снизу-вверх) 1,1,1,1,3 - значит смело можно заправить и заменить барабан... :)


jsofthome1

... Из того, что получилось, делаем перекрестный запрос...
что-то у меня фигня какая-то получается... :(


jsofthome1

что-то у меня фигня какая-то получается... :(
Ну извините, больше не буду.


jsofthome1

Ну извините, больше не буду.
у меня фигня - это не значит, что совет был неправильныйчто за обиды?


jsofthome1

неужели нет мыслей ни у кого??? Владимир Саныч! маленький примерчик своей идеи можно?


jsofthome1

Чисто на SQL задача не решается.
пусть не --Чисто на SQL-- , на VBA например или как ... неужели никто не поможет ?


jsofthome1

пусть не --Чисто на SQL-- , на VBA например или как ... неужели никто не поможет ?
"Чисто на SQL" - закончилось (не начинаясь) на этой фразе
Имеется исходная динамическая в обоих измерениях таблица(результат перекрестного запроса),
если говорить про "Чисто на SQL" - нужно начинать "с начала", - с ДО формирования этой "динамическая в обоих измерениях таблица" ---а на ВБА, не сказать, что бы "просто", но достаточно "прозрачно" и тупо1открываеш на этой "таблице" рекордсет2создаеш 2-х мерный массив с размерностью rs.fields.count X "фиксированную по количеству строк (в данном случае 6)" 3сортируеш рекордсет по Дата DESC4и в цикле по рекордсету, заполняеш массив по своему алгоритму5всё


jsofthome1

у меня фигня - это не значит, что совет был неправильныйчто за обиды?
Никаких обид. Если бы Вы сообщили что-то конкретное, я бы знал, чем могу помочь.
Владимир Саныч! маленький примерчик своей идеи можно?
Не могу примерчик, там много кода. Начинайте по порядку. Если будут конкретные вопросы, задавайте, попробую помочь.


jsofthome1

... на ВБА, не сказать, что бы "просто", но достаточно "прозрачно" и тупо ...
угу... это я уже давно сделал...хотелось-бы как-то более эффективно и оригинально...:-(


jsofthome1

если кому интересно посмотреть - вот работающий пример cкачать


jsofthome1

функция заполнения элемента управления ListView (с дополнительными параметрами)
' функция заполнения элемента управления ListView (с дополнительными параметрами)
' возвращаемые значения
' 0 при успехе
' 1, если свойство View у контрола ListView не 'Report'
' 2, если набор записей пустой
' 3, если в наборе записей полей меньше 2
' Err.Number при ошибке
'
' обязательные аргументы
' strSQL$ - имя таблицы/запроса или строка SQL для рекордсета
' ListViewObject - элемент управления ListView
'
' необязательные аргументы
' LastValue - сколько последних записей выводить из рекордсета (0 - все)
' NzColValue - значение для Null
' SelValue - выделяемые(цветом и/или полужирным) значения в ListView
' Inverse - если True, выделяются все, кроме SelValue
' SelFontColor - для выделяемых цвет RGB() или константы цвета (vbRed ...)
' SelFontBold - если True, выделяемые полужирным
Function FillListViewFromRecordset&(strSQL$, ListViewObject As Object, Optional ByVal LastValue& = 0, Optional NzColValue = "", Optional SelValue = "", Optional Inverse As Boolean = False, Optional SelFontColor&, Optional SelFontBold As Boolean = False)
 Dim rs As Recordset
 Dim nRow&, nCol&, nStartRow&
 Dim NewCol As ColumnHeader, NewRow As ListItem
 Dim rsVal, ReturnValue&
 On Error GoTo Err_Fn
 With ListViewObject
 ReturnValue = 1
 If .View = lvwReport Then ' если свойство View = 'Report'
 ' очистить ListView
 .ListItems.Clear
 .ColumnHeaders.Clear
 ' открыть рекордсет
 Set rs = CurrentDb.OpenRecordset(strSQL$)
 ' установить заголовки столбцов
 For nRow = 0 To rs.Fields.Count - 1
 Set NewCol = .ColumnHeaders.Add(, , rs(nRow).Name)
 Next
 ReturnValue = 2
 If rs.RecordCount Then
 ' заполнение рекордсета
 With rs
 .MoveLast
 .MoveFirst
 If .Fields.Count > 1 Then ReturnValue = 0 Else ReturnValue = 3
 End With
 ' проверка LastValue
 If LastValue Then
 LastValue = Abs(LastValue)
 ' если пользователь указал меньше строк, чем есть в рекордсете
 If LastValue < rs.RecordCount Then
 nStartRow = rs.RecordCount - LastValue
 ' смещаемся на нужную строку
 rs.Move nStartRow
 End If
 End If
 ' Loop по записям и добавление Items в контрол
 For nRow = nStartRow + 1 To rs.RecordCount ' строки
 rsVal = Nz(rs(0).Value, NzColValue)
 If IsNumeric(rsVal) Then
 Set NewRow = .ListItems.Add(, , str(rsVal))
 Else
 Set NewRow = .ListItems.Add(, , rsVal)
 End If
 For nCol = 1 To rs.Fields.Count - 1 ' столбцы
 With NewRow
 rsVal = Nz(rs(nCol).Value, NzColValue)
 .SubItems(nCol) = rsVal
 ' если значение столбца равно(или не равно при Inverse=True) опционально указанному параметру
 ' установить опционально указанный цвет и насыщенность шрифта
 If Inverse Xor (rsVal = SelValue) Then
 With .ListSubItems(nCol)
 If SelFontColor Then .ForeColor = SelFontColor
 .Bold = SelFontBold
 End With
 End If
 End With
 Next
 rs.MoveNext
 Next
 End If
 rs.Close
 Set rs = Nothing
 End If
 End With
Err_Exit:
 FillListViewFromRecordset = ReturnValue
 Exit Function
Err_Fn:
 ' Отобразить ошибку
 ReturnValue = Err.Number
 MsgBox "(FillListViewFromRecordset) Error: " & ReturnValue & vbCrLf & Err.Description
 Resume Err_Exit
End Function
пример вызова:
ret = FillListViewFromRecordset("ПерекрестныйЗапрос1", ListView1, 10, "-", "5", True, vbRed, True)
заполнить ListView1 из ПерекрестныйЗапрос1заполнить только 10 последних строк из запросавместо Null значений отображать "-"выделить значения не равные "5" красным, полужирным


jsofthome1

функция заполнения элемента управления ListView (с дополнительными параметрами)+ номер выделяемой строки (например для суммы)+ для выделяемой строки цвет, полужирный
' функция заполнения элемента управления ListView (с дополнительными параметрами)
' возвращаемые значения
' 0 при успехе
' 1, если свойство View у контрола ListView не 'Report'
' 2, если набор записей пустой
' 3, если в наборе записей полей меньше 2
' Err.Number при ошибке
'
' обязательные аргументы
' strSQL$ - имя таблицы/запроса или строка SQL для рекордсета
' ListViewObject - элемент управления ListView
'
' необязательные аргументы
' LastValue - сколько последних записей выводить из рекордсета (0 - все)
' NzColValue - значение для Null
' SelValue - выделяемые(цветом и/или полужирным) значения в ListView
' Inverse - если True, выделяются все, кроме SelValue
' SelFontColor - для выделяемых цвет RGB() или константы цвета (vbRed ...)
' SelFontBold - если True, выделяемые полужирным
' NumSelRow - номер выделяемой строки (например для суммы)
' SelRowColor - для выделяемой строки цвет RGB() или константы цвета (vbRed ...)
' SelRowBold - если True, выделяемая строка полужирным
Function FillListViewFromRecordset&(strSQL$, ListViewObject As Object, Optional ByVal LastValue& = 0, Optional NzColValue = "", Optional SelValue = "", Optional Inverse As Boolean = False, Optional SelValColor& = 0, Optional SelValBold As Boolean = False, Optional ByVal NumSelRow& = 0, Optional SelRowColor& = 0, Optional SelRowBold As Boolean = False)
 Dim rs As Recordset
 Dim nRow&, nCol&, nStartRow&
 Dim NewCol As ColumnHeader, NewRow As ListItem
 Dim rsVal, ReturnValue&
 LastValue = Abs(LastValue)
 NumSelRow = Abs(NumSelRow)
 On Error GoTo Err_Fn
 With ListViewObject
 ReturnValue = 1
 If .View = lvwReport Then ' если свойство View = 'Report'
 ' очистить ListView
 .ListItems.Clear
 .ColumnHeaders.Clear
 ' открыть рекордсет
 Set rs = CurrentDb.OpenRecordset(strSQL$)
 ' установить заголовки столбцов
 For nRow = 0 To rs.Fields.Count - 1
 Set NewCol = .ColumnHeaders.Add(, , rs(nRow).Name)
 Next
 ReturnValue = 2
 If rs.RecordCount Then
 ' заполнение рекордсета
 With rs
 .MoveLast
 .MoveFirst
 If .Fields.Count > 1 Then ReturnValue = 0 Else ReturnValue = 3
 End With
 ' проверка LastValue
 If LastValue Then
 ' если пользователь указал меньше строк, чем есть в рекордсете
 If LastValue < rs.RecordCount Then
 nStartRow = rs.RecordCount - LastValue
 ' смещаемся на нужную строку
 rs.Move nStartRow
 End If
 End If
 ' Loop по записям и добавление Items в контрол
 For nRow = nStartRow + 1 To rs.RecordCount ' строки
 rsVal = Nz(rs(0).Value, NzColValue)
 If IsNumeric(rsVal) Then
 Set NewRow = .ListItems.Add(, , str(rsVal))
 Else
 Set NewRow = .ListItems.Add(, , rsVal)
 End If
 For nCol = 1 To rs.Fields.Count - 1 ' столбцы
 With NewRow
 rsVal = Nz(rs(nCol).Value, NzColValue)
 .SubItems(nCol) = rsVal
 With .ListSubItems(nCol)
 ' если значение столбца равно(или не равно при Inverse=True) опционально указанному параметру
 ' установить опционально указанный цвет и насыщенность шрифта
 If Inverse Xor (rsVal = SelValue) Then
 If SelValColor Then .ForeColor = SelValColor
 .Bold = SelValBold
 End If
 ' если опционально указан параметр NumSelRow - номер выделяемой строки (например для суммы)
 ' установить опционально указанный цвет и насыщенность шрифта
 If (nRow - nStartRow = NumSelRow) Then
 If SelRowColor Then .ForeColor = SelRowColor
 .Bold = SelRowBold
 End If
 End With
 End With
 Next
 rs.MoveNext
 Next
 End If
 rs.Close
 Set rs = Nothing
 End If
 End With
Err_Exit:
 FillListViewFromRecordset = ReturnValue
 Exit Function
Err_Fn:
 ' Отобразить ошибку
 ReturnValue = Err.Number
 MsgBox "(FillListViewFromRecordset) Error: " & ReturnValue & vbCrLf & Err.Description
 Resume Err_Exit
End Function