JeyCi написал: - Delete(AffectRecords)... если когда-нибудь придётся допиливать - обязательно выложу итог "творческой мысли" ...
всё-таки итог творческой мысли приводит к 1 выводу (бд создаётся не для того, чтобы её удалять, а для хранения важной инфо и её структурирования, для того бд и создают, чтобы хранить инфо и сильно не увлекаться удалением) и 2 возможностям: 1) добавить в базу поле Архив - где помечать архивные записи - и удалять по мере (не)надобности 2) скидывать в отдельную таблицу архивные записи (что получается немного более громоздко) и лишь избавляет от необходимости при обращении к базе в дальнейшем ставить проверку WHERE NOT archive... кому как удобно... вариант 1 - простой вариант 2 - вставила sql-запрос на обновление, запрос на удаление... после чего закрыла рекордсет и открыла его обновлённым... без пустого поля... что-то RS.Delete не очень проходил, как хочется, - пустота оставалась - поэтому использовала такой ход конём p.s. итог мысли выкладываю, как обещала давно (адаптировано на примере из #1 этой ветки) P.P.S. на кибере мысля #6в ветке: Как переместить запись из одной таблицы в другую нажатием кнопки
Цитата
boby104 написал: Перегонять данные в другую (архивную) таблицу имеет смысл, когда нужно фиксировать все изменения записи в разрезе дат и пользователей
хотя, насколько можно заметить на том же кибере1 (ветка: Перенести данные из одной таблицы в другую ), создание ещё одной таблицы - это своего рода может быть денормализация?.. и она должна быть обоснована!... но с другой стороны - это напоминает просто распределённую БД - главное неизбыточность и оптимально задать ключевые поля!... пример Проектирования БД (на том же кибере2) - ветка: Проектирование БД
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
по RS.Update в примере #1 в принципе реализовано... но есть ещё пример в ветке Access - Excell-Access - здесь #7 "основная часть кода" - смотреть есть ли такая запись в базе Access, и если нет, то добавлять, а если есть то обновлять - (для: из Excel'я передать в Access) - везде всё то же... только RS.Sort у меня затребовал .CursorLocation=3, aka adUserClient... что и выставила после удаления в новом рекордсете (для простоты попадания на последнюю запись в rs для её отображения в полях листа после удаления текущей)... - других нюансов для адаптации не обнаружено в работе кода из #1 ( #121) p.s. все основы, как всегда здесь ADO
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
и простенький код OLEDB для получения QueryTable - ExecuteSQL:
Код
Sub ExecuteSQL()
'http://analystcave.com/create-microsoft-query-excel-excel-query/
Attribute ExecuteSQL.VB_ProcData.VB_Invoke_Func = "S\n14"
'AnalystCave.com
On Error GoTo ErrorHandl
Dim SQL As String, sConn As String, qt As QueryTable
SQL = InputBox("Provide your SQL Query", "Run SQL Query")
If SQL = vbNullString Then Exit Sub
sConn = "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;;Password=;User ID=Admin;Data Source=" & _
ThisWorkbook.Path & "/" & ThisWorkbook.Name & ";" & _
"Mode=Share Deny Write;Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
Set qt = ActiveCell.Worksheet.QueryTables.Add(Connection:=sConn, Destination:=ActiveCell)
With qt
.CommandType = xlCmdSql
.CommandText = SQL
.Name = Int((1000000000 - 1 + 1) * Rnd + 1)
.RefreshStyle = xlOverwriteCells
.Refresh BackgroundQuery:=False
End With
Exit Sub
ErrorHandl: MsgBox "Error: " & Err.Description: Err.Clear
End Sub
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
Sub AddVeryNew()
'anvg 2 Июн 2014 15:15:32'ДОБАВИТЬ В БД КРОМЕ УЖЕ ИМЕЮЩИХСЯ............................
Const dbaccCon As String = "Provider=Microsoft.ACE.OLEDB.12.0;;Mode=Share Deny None;Data Source=d:\path\SI_Database.accdb"
Const TableFields As String = "[fid],[fname],[fdate]"
Const xlsCon As String = "[Excel 12.0;Database=$1;HDR=YES].[Sheet1$] As t1"
Dim AddSQL As String, pCon As Object, addRS As Object
'строка запроса на основе данных Excel
AddSQL = "Select t1.[fid],t1.[fname],t1.[fdate] " & _
"From " & Replace$(xlsCon, "$1", ThisWorkbook.FullName) & " Left Join AddingSQL As t2 On (" & _
"(t1.[fid]=t2.[fid]) And (t1.[fname]=t2.[fname]) And (t1.[fdate]=t2.[fdate]) And " & _
"Where (t2.[fid] Is Null) And (t2.[fname] Is Null)"
'подключение к Access
Set pCon = CreateObject("ADODB.Connection")
pCon.Open dbaccCon
'выполнение запроса к Access
Set addRS = CreateObject("ADODB.Recordset")
addRS.CursorLocation = 3
addRS.Open AddSQL, pCon
'добавление ненайденых в БД
If addRS.RecordCount > 0 Then
AddSQL = "Insert Into AddingSQL (" & TableFields & ") " & AddSQL
pConn.Execute AddSQL
MsgBox "Добавлено " & CStr(addRS.RecordCount) & " новых ваписи"
Else
MsgBox "Нет новых записей"
End If
addRS.Close
pCon.Close
End Sub
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
Удаление из таблицы Excel на основе выбранного в UserForm - пост от 14 апр 15, 03:29 от anvg ... с Удалением, конечно, очень сильно поколдовать приходится... удалять средствами ADO в самой access как-то полегче через Delete... а в excel как-то нельзя ... но по линку в #14 есть ещё один ход конём
но исключения лучше отбирать Join'ом (там в #14)
Код
Sub go()
Dim sCon$, cn As Object, rs As Object
Dim finalRow&, lCount&, sSQL$
Set cn = CreateObject("ADODB.Connection")
cn.Mode = adModeReadWrite
Select Case CLng(Split(Application.Version, ".")(0))
Case Is < 12
sCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ThisWorkbook.FullName _
& ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
Case Is >= 12
sCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName _
& ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"
End Select
sSQL = "SELECT * FROM [Лист1$A:A] AS t LEFT JOIN [Лист2$A:A] AS t2 ON t.test=t2.ttest " & _
"WHERE t2.ttest IS NULL"
cn.Open sCon
Set rs = cn.Execute(sSQL)
lCount = UBound(rs.GetRows, 2) + 3
With Лист1
.Cells(2, 1).CopyFromRecordset cn.Execute(sSQL)
finalRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
.Range(.Cells(lCount, 1), .Cells(finalRow, 1)).Clear
End With
End Sub
! именно выгрузить поверх, после чего удалить оставшийся хвост от прежнего диапазона (если будет)... поскольку если будем очищать сразу диапазон выгрузки - мы будем терять данные из источник запроса... и выгрузка будет нулевая
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
JeyCi написал: ... с Удалением, конечно, очень сильно поколдовать приходится...
кратенько 3 варианта возможностей ADO Delete в excel (т.е. обходные манёвры невозможности использовать команду DELETE напямую): 1) с учётом листа "исключения" - на основе макроса выше 2) с учётом столбца "check" - где выставить отметки о ненадобности строк - выгрузка в столбец K (но манёвром из (1) можно прикрутить и на прежнее место) 3) идея от от anvg - упрощена для использования на листе (по значениям контрольного столбца) по сути: 3 модуля
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
'Составляется запрос с нарастающим итогом,
'из которого вычитается введенное значение списываемого количества -
'и таким образом получаем убывающий объем, который анализируем на исчерпывание:
'Списание1:
SELECT P.IdПрихода, P.количество, P.цена, P.progress,
P.количество-(P.progress-[СПИСАТЬ]) AS regress,
IIf([СПИСАТЬ]>P.progress,P.количество,IIf(regress<0,0,regress)) AS списано,
P.количество - списано AS остаток
FROM (SELECT IdПрихода, Количество, Цена, (SELECT SUM(Количество)
FROM Остатки S WHERE S.IdПрихода<=O.IdПрихода) AS progress FROM Остатки O) AS P;
там же вопрос (29 авг 07, 15:05)
Цитата
Ведь запросы с накоплением ,по всей вилимости, для каждой записи выборки повторяют суммирование с начала, что приводит к 10 и более кратному увеличению затрат времени на их реализацию по сравнению с позаписным подходом.А нельзя как-то исхитриться хотя бы не считать для всей таблицы Остатки накопления, а только до т.п.пока накапливаемая сумма Progress не превысит заданное СПИСАНИЕ
и там же ответ (29 авг 07, 16:28) от Karfaqen
Цитата
в такой категории задач, решение с помощью VBA (скажем, через временную таблицу), будет иметь преимущество по скорости выполнения - именно в силу того, что там возможно более ухищренное исключение из обработки лишних массивов данных.
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
встретилась там и ветка "Создание запроса чтобы получился остаток - MS Access" - и решение с обращением к доп. запросам... удалила лишнее из того файла-примера (оставив лишь запросы для сверки) и выложу свой вариант Запроса для вывода Остатков - одним махом [только из таблиц!] - ACCOUNT_ОСТАТКИ
Код
// Справочник-Приход-Расход
// MY ACCOUNT_Остатки (количество в шт.)
SELECT TT.[Код товара], TT.Артикул,TT.[Наименование товара],
IIF(ISNULL(TT.СуммаПрихода),0,TT.СуммаПрихода) AS СуммаПрихода, IIF(ISNULL(TTT.СуммаРасхода),0,TTT.СуммаРасхода) AS СуммаРасхода
FROM
(SELECT SSP.* FROM
(
SELECT [Справочник товара].[Код товара], [Справочник товара].Артикул, [Справочник товара].[Наименование товара], СуммаПрихода
FROM [Справочник товара]
LEFT JOIN
(
SELECT ПРИХОД.[Код товара], ПРИХОД.[Наименование товара], ПРИХОД.[Артикул], SUM(IIF(ISNULL(ПРИХОД.[Кол-во]),0,ПРИХОД.[Кол-во])) AS СуммаПрихода
FROM ПРИХОД
GROUP BY ПРИХОД.[Код товара], ПРИХОД.Артикул, ПРИХОД.[Наименование товара]
) SP
ON [Справочник товара].[Код товара]=SP.[Код товара]
) SSP) TT
LEFT JOIN
(
SELECT РАСХОД.[Код товара], РАСХОД.[Наименование товара], РАСХОД.[Артикул], SUM(IIF(ISNULL(РАСХОД.[Кол-во]),0,РАСХОД.[Кол-во])) AS СуммаРасхода
FROM РАСХОД
GROUP BY РАСХОД.[Код товара], РАСХОД.Артикул, РАСХОД.[Наименование товара]
) TTT
ON TT.[Код товара]=TTT.[Код товара]
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
QueryManager - ещё один удобный Add-in для XL - для написания SQL-запросов в т.ч. параметрических
Цитата
It eases the editing of queries and Pivottable connections. One can: 1. Edit the SQL string and the connect string of queries and PivotCaches 2. Add parameters 3. Change the path to the data source
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
на всякий случай - Cummulative col id - primary key (unique) id2 - foreign key
Код
SELECT t.id, t.id2, sum(q.СУММ_прихода) AS Cummulative
FROM TBL AS t INNER JOIN TBL AS q ON (t.id2=q.id2) AND (t.id>=q.id)
GROUP BY t.id2, t.id
ORDER BY t.id, t.id2;
p.s. кстати на Server'e FIFO-счёт можно реализовать через SUM... OVER... - см. окна
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
(из XL бд не сделать, но воспользоваться преимуществами структурирования инфо по принципу бд можно) Ликбез для сравнения с построением полноценной БД - оставляли планетяне в своё время интересные линки именно по БД - познакомиться с теорией дизайна баз данных #6
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
способ грузить в Access из PowerQuery - здесь - но проверка на отсутствие дублей Join'om не проводилась, при update (с проверкой через Join) может возникнуть проблема о "невозможности изменений в Связанной таблице" - тоже не проверялось из PQ, но встречалась такая проблема из ADODB SQL-запроса к базе...
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
встретилась проблема отсутствия ОКРУГЛТ() в Access, в отличие от Excel (в котором есть эта функция)... как можно обойтись БЕЗ Excel ??? (файлы прилагаются) p.s. просто потом хочется брать из Access на выгрузку (в TXT) даже просто vbs-скриптом (с SQL-запрсом) - не хотелось бы подряжать для этого дела Excel... может у кого есть идея, как алгоритмически (или лучше кодом) реализовать такое Округление в макросе Access для выгрузки ИЛИ в коде vbs-скрипта для выгрузки ??? (раз уж нельзя ? в самом Access его функциями ?) заранее спасибо! p.s. и предупредите please кто знает - как с таким Округлением обходятся на MS SQL Server Express - может там нет такой проблемы - просто для инфо ... хотя интересует прежде всего Access 2010
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
БМВ спасибо за этот подарок мне к завтрашнему празднику (данные кстати вроде были на день, предыдущий к вАшему прошЕдшему празднику) p.s. вопрос такого же встроенными средствами MS SQL Server Express остаётся -- если кто в курсе??... иначе и там придётся искать альтернативу (если кому-то сервер покажется более удобным, чем Access) ... я пока не могу найти серьёзного повода для перехода с Access на сервер... (по своим объёмам)... p.p.s. Трудности округления в MS SQL Server
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
а в чем проблема вычисляемо поле сделать по вышеуказанному алгоритму IIF функция есть далее дело техники. Думаю можно выбросить проверку на знак, для вашего конкретного случая, для простоты. =INT(Number/Multiple)*Multiple+IF(Number/Multiple-INT(Number/Multiple)<0,5;0;Multiple) Ну только заменить IF на IIF ,ну и поля нужные
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)