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

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 (с дополнительными параметрами)
<pre class="prettyprint linenums">' функция заполнения элемента управления 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 </pre>
пример вызова:
ret = FillListViewFromRecordset("ПерекрестныйЗапрос1", ListView1, 10, "-", "5", True, vbRed, True)
заполнить ListView1 из ПерекрестныйЗапрос1заполнить только 10 последних строк из запросавместо Null значений отображать "-"выделить значения не равные "5" красным, полужирным


jsofthome1

функция заполнения элемента управления ListView (с дополнительными параметрами)+ номер выделяемой строки (например для суммы)+ для выделяемой строки цвет, полужирный
<pre class="prettyprint linenums">' функция заполнения элемента управления 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 </pre>