Страницы: Пред. 1 2 3 4 5 6 7 8 9 След.
RSS
Excel и Access могут облегчить друг другу жизнь?, как можно совмещать их способности
 
- (по первым буквам ввод) -
ещё одна полезная ссылка на R Dmitry (#12):
Как сделать так чтобы в столбце В, путем ввода первых нескольких букв предлагались варианты выбора из базы
- Связать excel и базу данных
(-->далее, как обычно, Проверка Данных на полученный из БД список - итог: ввод первых букв уже предлагает варианты из списка)
Изменено: JeyCi - 03.10.2015 12:13:07
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
также в копилку с просторов планеты:
- ( для обработки больших текстовых файлов) -
пример импорта в аксесс из текстового файла с созданием спецификации импорта тхт файла (от B.Key #17)
- так как:
Цитата
Excel в качестве источника данных имеет свои внутренние ограничения книги и листов Excel. А именно, не более:
* Размер листа: 65536 строк и 256 столбцов
* Ячейка содержимое (текст): 32 767 символам
* Листов в книге: ограничено объемом доступной память
* Имена в книге: ограничено объемом доступной памяти  
- в случаях превышения допустимых размеров - стоит присмотреться к Access...  
P.S. кажись, дубль линка из моего #16 на этой ветке  :oops: ... но с указанием ограничений Excel в цитате (поэтому оставлю пост, как есть, - на заметку)
Изменено: JeyCi - 23.08.2015 08:41:18
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
не могу не выразить благодарность Андрею VG и поисковику:
1-му) за то, что помог задуматься... в теме Импорт очень большого текстового файла (№18)
Цитата
Андрей VG написал: загнал в Access, проиндексировал обе таблицы по полю UserName, далее следующим кодом получил результат за 65 секунд
2-му) за то, что... просветил что это и как с этим жить
Использование индексов в MS Access

Создание и использование индексов (в Access2002 - но суть всё та же)

P.S. остаётся вопрос, можно ли индексировать поля таблиц, импортированных из xl и связанных с xl (т.е., когда источник Excel) ??..
(чуть что - практика покажет, или знающие предупредят)... но быстродействие - вещь однозначно интересная... (БД могут быть быстрее словарей при больших объёмах и правильном подходе и кушать меньше ресурсов)
Изменено: JeyCi - 27.08.2015 20:01:28
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
JeyCi, добрый вечер.
Это легко устанавливается экспериментом. Связанную таблицу Excel нельзя индексировать. Если вы импортировали данные из Excel в Access, то это уже таблица базы данных и, следовательно, можно, зайдя в конструктор таблицы, установить индексацию полей. Это же можно сделать и через DDL команды Create Index.
 
Андрей VG спасибо, что предупредили!
буду тренироваться и разбираться при случае... не буду учиться на своих ошибках, буду помнить ваши слова  :)
... и справку  support.microsoft.com - операторы SQL DDL (Data Definition Language) для базы данных Microsoft Jet - тут же и Create Index
p.s. но сразу же возникает вопрос: можно ли индексацию включить в Определение запроса в MS Query в xl?? (для ускорения выполнения запроса).. маловероятно, т.к. возможности MS Query - очень недотягивают до функционала БД... имхо
Изменено: JeyCi - 27.08.2015 19:17:58
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
Цитата
JeyCi написал: как и всякое сильное средство, индексы в Access следует использовать с умом...
эту мысль под спойлером (кто туда заглянул) - завершу цитатой от Андрей VG с ветки (куда был линк)

Цитата
Андрей VG написал: Индексировать - тоже можно нарваться - не только же выборки есть, но и вставки, обновления, а это всё пересчёт индексов - может и притормозить.
ВЫВОД: индексировать осторожно... и не увлекаться, где не надо!
Изменено: JeyCi - 27.08.2015 19:33:43
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
Доброе время суток
Цитата
JeyCi написал:
p.s. но сразу же возникает вопрос: можно ли индексацию включить в Определение запроса в MS Query в xl?? (для ускорения выполнения запроса).. маловероятно, т.к. возможности MS Query - очень недотягивают до функционала БД... имхо
Давайте рассмотрим дело по порядку.
Простейший алгоритм создания индекса. Представим себе таблицу с данными как двумерный массив. Создадим одномерный массив, в который запишем номера строк таблицы (индексы). Затем упорядочим эти индексы (трудозатраты на сортировку N*Log(N), где N – число строк таблицы) в одномерном массиве по значениям столбов (столбца) таблицы с которыми связан индекс.
Для чего нужна такая конструкция. Во-первых – в упорядоченном массиве для поиска значения можно воспользоваться бинарным поиском. Трудозатраты на поиск буду C*Log(N). Как вы понимаете, это быстрее, чем простой просмотр, требующий в среднем N/2 . Во-вторых – создаётся массив упорядоченных индексов, а не сортируется сама таблица последующим причинам. а) Число полей в таблице может быть большим, а переупорядочивание, в этом случае, потребует значительных трудозатрат.  б) С таблицей может быть связано несколько индексов, как в этом случае упорядочивать таблицу?
Для поддержания актуальности индексов, а также их хранения требуется (неиндексированная база Access примера занимает 783Мб, а индексированная 1,17Гб) соответствующий код.
Немного отвлечёмся на то, на что вам в своеобразной манере намекал Johny. БД – это комплекс программ.  Минимальный  набор : а) «Движок» базы данных, который управляет данными, выбирает оптимальный алгоритм выполнения запроса, поддерживает индексирование, управляет доступом и т. д.. б) Драйвер баз данных, предоставляющий для клиентских программ стандартный интерфейс обращения к «движку» БД для передачи ему команд и получения результирующих данных (например, в стандарте ADO). Для серверных БД есть ещё один промежуточный слой – клиент БД.
Для Access «движок» и драйвер – это одна программа Access Runtime Engine, её достаточно для работы с базой данных Access. То же что мы запускаем как приложение – это надстройка для визуального управления базой данных и разработки клиентского приложения. Как вы понимаете, непосредственно для работы самостоятельно разработанной клиентской программы или программ, умеющих использовать интерфейс ADO, эта надстройка не нужна.
С этой точки зрения – MS Query точно такая же надстройка для визуального создания запросов к БД. Управлять же индексами может только «движок» БД.
Менять же файлы Excel могут и другие программы – нет монопольного управления файлами «движком», поэтому создание индексов для этого формата и не предусмотрено.
Но, не всё так плохо. Для неиндексированной базы Access время выполнения запроса 59 секунд, для индексированной – 55 секунд (с утра ноут работал быстрее – ещё не устал;)). Видимо разработчики MS, учитывая, что с их базами будут работать неопытные пользователи, предусмотрели создание временных индексов по результату анализа запроса и их использование во время его выполнения. Иначе такую небольшую  разницу во времени невозможно объяснить.
Для примера по базам по ссылке на приведённый вами пост трудозатраты на прямой просмотр значений одной таблицы в другой N*M – 3*10^13 операций, а по оптимальному плану при использовании индексированных таблиц C*N*Log(M) – C*3*10^7. Трудно предположить, что коэффициент затрат на использование временных индексов равен 10^6
Извиняюсь, за несколько сумбурное изложение. Но дело где-то как-то так;).
Успехов.
 
Ух ты. Блин, сколько всего интересного в последнем сообщении для не профессионалов типа меня :)
ОФФ: не хватает всё же на форуме кнопочки "добавить сообщение в избранное"
F1 творит чудеса
 
Скрытый текст
т.е.  для серверных БД клиент есть (кстати, Из CSV в SQL при помощи VBScript-  содержимое SQL-файла можно открыть клиентом базы данных) ...
но всё равно, думается, что разрабатывать его (Клиента) под каждого конкретного заказчика и его модель данных всё-таки надо - (наверно, как-то через Visual Studio) ... как и "Access служит для разработки клиентского приложения"... т. е. я не программист: мне просто кажется, что слово "клиент" и "клиентское приложение" - это одно и то же  :oops: ... и их надо разрабатывать под каждую конкретную задачу...
Цитата
Андрей VG написал:
MS Query (в xl)... -  создание индексов для этого формата и не предусмотрено.
(Access)... разработчики MS... предусмотрели создание временных индексов по результату анализа запроса...
поэтому, видимо, мне и кажется всё время, что запросы в Access работают быстрее, чем, созданные (такие же) с помощью MS Query в xl?? ... всё не могу понять:
а) спасёт ли меня сохранение файла подключения .odc - может если решу с облака взять свой файл и поюзать его на др компьютере?..
б) оправданно ли сохранение запроса .dqy - когда такое полезно?.. - не могу найти их пользу (а и б)
... видела только Параметрический веб-запрос для получения курса валют на заданный интервал дат ...
в) и можно ли как-то все запросы в xl прогнать через существующий Connection (файла к самому себе на один и тот же лист - просто запросы разные для вывода на другие листы нужных раскладок)?.. хотя, наверно, нельзя, - так как в каждом Подключении к одному и тому же xl прописан свой запрос...
просто файл xl уже много весит и работает чуть-чуть задумчиво - мне в принципе пока не очень мешает это - но мечты об оптимизации не покидают...   8)  хотя понятно, конечно, что и сами запросы можно пооптимизировать, я ведь ещё только первые шаги сделала на 7000кб... вот и задумалась: можно ли причесать Excel? или эммигрировать в Access?
P.S.
в любом случае спасибо за столь развёрнутый ответ! - про драйвер знала, про "движок" нет, про клиента лишь слышала... про индексы лишь читала, не думая о степени важности... наверно, теперь заметила их (временных) вклад в скорость моей (и их в access) работы  :) ... спасибо!
p.p.s.

Максим, у вас вверху странички (когда заходите под своим логином - звезда есть - это Избранное (не подписка)... и из вашего профиля к нему тоже доступ где-то сверху вкладка... как-то так (наверно, только если ветку, а не отдельный пост)
Изменено: JeyCi - 27.09.2015 22:07:34
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
всем, доброе утро. Вот и ответ на мой первый вопрос (из последних трёх)
Цитата
JeyCi написал: а) спасёт ли меня сохранение файла подключения .odc - ...
оказывается был тут неподалёку (#6)
Цитата
Андрей VG написал: можно сохранить подключение в файл и просто добавить потом в новую книгу.
- такое его назначение (.odc), оказывается ... а так же примеры файлов Подключений (#16) и способы их модификации под нужную ситуацию (#8 ) - в ветке "создать сводную таблицу из нескольких баз данных" уже были... для комфорта работы с Подключениями в xl-файлах
p.s.
только не забывать, что драйвер БД (ODBC или OLEDB) - всё равно нужен, для создания подключений в xl... устанавливается по умолчанию с Access... если офис устанавливается без Access - то драйвер (эксессный) надо доустановить ручками - для возможности работы с MS Query в Excel...
Изменено: JeyCi - 29.08.2015 10:07:32
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
Доброе время  суток
Цитата
JeyCi написал:
т.е.  для серверных БД клиент есть ... но всё равно, думается, что разрабатывать его под каждого конкретного заказчика и его модель данных всё-таки надо - (наверно, как-то через Visual Studio) ... как и "Access служит для разработки клиентского приложения"... т. е. я не программист: мне просто кажется, что слово "клиент" и "клиентское приложение" - это одно и то же   ... и их надо разрабатывать под каждую конкретную задачу...
Ещё раз извиняюсь за сумбурность изложения.
Вы не совсем правы. Клиент серверной БД и связанный с ним драйвер - это часть программного обеспечения (ПО) БД, поставляемого разработчиком БД. Клиент необходим для взаимодействия с серверным ПО базы данных. Его достаточно скачать и установить (просто наберите скачать клиент Oracle или MS SQL server). Хотя тут есть тоже свои особенности, например, для MySQL лучше искать драйвер MySQL. Поэтому вам не надо под каждого заказчика разрабатывать свой клиент.
Клиентское приложение - это уже тот код, который создаёт и управляет объектами базы данных DDL (таблицы, представления,индексы, последовательности, ограничения и т. д.), или взаимодействует с созданными объектами DML (вставка, замена, удаление данных в таблицах и собственно запросы SQL).
Ещё раз. Есть ПО базы данных, выполняющее работу с базой данных и предоставляющее услуги по работе с ней. И есть клиентский код, работающий с базой данных и реализующий бизнес-логику тех задач, для которых и создаётся база данных (включая и создание объектов БД).

Применительно к данной теме (всё же только про Access). Для работы с БД достаточно Access Runtime Engine. Вы можете в VBA, используя библиотеку Microsoft ADO Ext. for DDL and Secutiry, создать базу данных, создать таблицы, поля таблиц, наложить ограничения и т. д. Для существующей БД Access можно просто через ADODB.Connection или ADODB.Command, с заданным Connection, выполнять DDL. Можно это же сделать и из под VBScript или JSScript. Используя Recordset вставлять, обновлять или удалять записи в таблицах, а также выполнять SQL запросы. А можно это же выполнить через те же Connection и Command. Весь этот код и будет вашим клиентским приложением.
То есть всё то, что вы делаете, используя приложение Access, поэтому Access всего лишь клиентское приложение, для функционирования БД не нужный, но очень удобный инструмент  ;)
Цитата
поэтому, видимо, мне и кажется всё время, что запросы в Access работают быстрее, чем, созданные (такие же) с помощью MS Query в xl?? ... всё не могу понять:
Скорее всего кажется. Тут просто есть некоторая разница в восприятии. В Access вы имеете дело с уже инициализированным "движком" и открытым подключением к БД, плюс формат вывода запроса проще, чем лист Excel, поэтому выборка происходит быстрее. Из под Excel же необходимо выполнить открытие подключения к БД, передать требуемые данные и вставить на лист Excel (если запрос выполняется первый раз после запуска Excel, то ещё и инициализация "движка" - всё это требует времени. С этой точки зрения, да медленнее, если же с точки зрения выполнения самого запроса - то разницы скорее всего нет.

С Odc и dbq - это всего лишь текстовые файлы с соответствующей структурой - данными по строке подключения и текстом запроса. С их помощью легко повторно вставить данные из БД на лист новой книги, не проходя все шаги, например MS Query по получению данных, но и не более того. Можно сбросить его другому человеку, в случае использования серверной БД, чтобы он получил те же данные у себя, минимизируя в какой то степени объём передаваемых данных.

С облачными хранилищами, тут не всё так просто - это же всего лишь удобное средство синхронизации. Представьте себе, что offline в папке клиента одного и того же облака на разных компьютерах два человека работают с одним и тем же файлом. Потом подключаются к интернет - и что с чем будет синхронизировано? Я облачными хранилищами не пользуюсь, возможно что-то для такой коллизии и есть решения. Буду рад, если мои сомнения по этому поводу напрасны.

Всё же, мне кажется, если стоит задача доступа к общим данным в структуре БД, то лучше либо задействовать облачные базы данных (Google, Microsoft, Oracle может кто ещё), с оплатой по подписке. Либо, организовав серверную БД у себя, для сотрудников организовать доступ к ней через интернет, используя WPN.

Ответ на вопрос в) вы дали и сами. Подключение в рамках его определения в Excel это и само подключение и запрос одновременно, в отличии организации этого в ADODB, поэтому использовать один и тот же WorkbookConnection для выполнения разных запросов не получится.
Цитата
просто файл xl уже много весит и работает чуть-чуть задумчиво - мне в принципе пока не очень мешает это - но мечты об оптимизации не покидают...     хотя понятно, конечно, что и сами запросы можно пооптимизировать, я ведь ещё только первые шаги сделала на 7000кб... вот и задумалась: можно ли причесать Excel? или иммигрировать в Access?
Вы же читали книги по Aceess, во всех в них даются рекомендации - данные отдельно, управление отдельно. Применительно к файлу Excel, храните данные используемые для выборок в одном файле, а результаты этих выборок в других файлах.
И несколько общая рекомендация, если для реализации ваших задач удобен SQL и нужен доступ с разных компьютеров - то не перейти ли вам на серверную БД? Помимо единого хранилища, вы решите и такую проблему файловых БД, как зависимость всех ваших строк подключения для файловых БД от местоположения файла. В серверных это же (упрощённо) имя компьютера и имя сервиса, как вы понимаете в пределах доступности по сети - всегда одно и тоже.
Изменено: Андрей VG - 29.08.2015 10:07:07
 
Андрей VG спасибо огромное - вы развеяли мои страхи и переживания и в придачу ещё кучу полезной инфо привнесли в моё понимание и на ветку...
Цитата
Андрей VG написал: Поэтому вам не надо под каждого заказчика разрабатывать свой клиент.
разве что ещё какого линка не хватает о том, как его (клиента любого) юзать... [если кто захочет альтернативу Access'у и Excel'ю увидеть]
Цитата
Андрей VG написал: и из под VBScript ... Весь этот код и будет вашим клиентским приложением.
вот и пример .vbs нашёлся - Доступ к данным Excel через ADO - т к книгу xl, как говорил один планетянин, "можно назвать своего рода БД, а листы в этой книге - это таблицы этой БД"... - своего рода "файловая бд"... это меня в своё время и вдохновило не наворачивать Америку из всех данных, а компактно и в знакомой среде Excel - сконструировать какую-нибудь реляционную структуру без излишеств - используя MS Query и ADO в vba...
хотя, конечно ADO и для Access и для доступа к др БД - очень удобно..
==============
а) про облачные хранилища - не просто представила, но и прочувствовала неудобство, описанное вами ("что с чем синхронизируется") - даже на примере синхронизации закладок google Chrome (между компом и ноутом) - только время зря потратила... полагаю, OneDrive такой же... а вот за облачную БД - спасибо, даже не знала, что такое есть...
б) ну, а собственный сервер с VPN - это уже только для сверхсекретных данных есть смысл (которые однозначно хранить только у себя можно, например бухгалтерию) - я не Пентагон  :), да и сотрудников нет, чтобы бухгалтерА с разных машин на общий сервер ходили через VPN (Virtual Private Network - насколько поняла)...
-----------------------
Цитата
Андрей VG написал: - данные отдельно, управление отдельно. Применительно к файлу Excel, храните данные используемые для выборок в одном файле, а результаты этих выборок в других файлах.
- да, кстати, так и было удобно до тех пор пока не захотелось заглянуть во временной горизонт, а каждый день в отдельном файле (со своим названием) - бери и ковыряйся в именах файлов и их ячейках... тут я и задумалась - лучше бы все таблицы были в БД (хотя бы Access)... и поняла, что "да и в БД они были бы уже другие" - тупо не складируешь, связи продумать изначально, чтобы по ним потом легко тягать, что надо  8)
Цитата
Андрей VG написал: И несколько общая рекомендация, если для реализации ваших задач удобен SQL и нужен доступ с разных компьютеров - то не перейти ли вам на серверную БД?
наверно, ресурсов намного больше будет кушать...
Цитата
Андрей VG написал:  Помимо единого хранилища, вы решите и такую проблему файловых БД, как зависимость всех ваших строк подключения для файловых БД от местоположения файла. В серверных это же (упрощённо) имя компьютера и имя сервиса, как вы понимаете в пределах доступности по сети - всегда одно и тоже.  
пока скромненько справляюсь так (файл сам на себя) - как условно (при условии использования конкретного, по коду линка, провайдера) отпостила в теме Подключение БД к Excel - иначе, наверно, окошко придётся выкидывать для выбора файлов...
==============
Цитата
Андрей VG написал: Для работы с БД достаточно Access Runtime Engine
полагаю, его и надо устанавливать дополнительно, если не установлен Access, а MS Query и ADO поюзать надо бы в Excel... только эти пару линков встретились (как устанавливать его отдельно не знаю, так как стоИт Access)
Тема: Runtime Access
Access 2007: среда выполнения Access (и даже не 2010)
Изменено: JeyCi - 31.08.2015 09:04:22
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
Цитата
JeyCi написал:
Access 2007: среда выполнения Access (и даже не 2010)
Погодите, а как же
Среда выполнения Microsoft Access 2010
Среда выполнения Microsoft Access 2013
А если у вас Excel 32битный, так и старым добрым Jet (движок и драйвер от Access 2000) можно обойтись. Он, начиная с WinXP по Win7, по умолчанию стоит даже на "голой" установке ОС.
Да и если вы не создаёте клиентских решений для Access в самом Acess (DBE-файлы), то вам и Access Runtime 2007 для нового формата БД accdb хватит (он c 2007 не менялся). Тут только одна проблема, если Excel 64битный, тогда нужно будет Access Runtime 2010 64bit.
Ну, а для серверных баз, когда возникнет такая тема, тогда и ссылки будут. Думаю, пока у вас ещё нет таких объёмов данных или задач (например,потребность в использовании аналитических/оконных функций), чтобы ставить Express версии SQL Server или Oracle. Хотя ресурсов, не сказать что для современных ПК они потребляют много.
Изменено: Андрей VG - 30.08.2015 22:16:39
 
Цитата
Андрей VG написал:  Думаю, пока у вас ещё нет таких объёмов данных или задач (например,потребность в использовании аналитических/оконных функций), чтобы ставить Express версии SQL Server или Oracle.
а) насчёт объёмов данных, конечно, вы правы, - не стремлюсь к собирательству... лишь к получению важной инфо и возможностям автоматизации этого процесса, выраженным в минимальном количестве строк... - за это и неравнодушна стала к SQL - когда узнала, что много строк по коду vba - можно заменить SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY... чтобы избежать долгой писанины и получить нужные результаты... главное, правильно ими воспользоваться
Последовательность выполнения SQL запроса

б) но насчёт задач - думаю, согласна (пока верю на слово этой цитате), что
Цитата
практически все, что можно сделать с помощью аналитических функций, можно сделать и без них, но с их использованием требуемого эффекта можно добиться проще и, зачастую, оптимальнее...
- но Окна, вообще, не могу придумать, какими Join'ами заменить, например в теме TotalValue производного актива - добрые люди помогли UDFку соорудить для Excel (ещё только знакомилась с vba)... а вот возможно ли такое же соорудить без кода, а в Access (или MS Query) запросом - пока что сильно затрудняюсь сказать, помогут ли хоть какие Join'ы... а после ваших слов уже думаю, наверно, это было Окно... (в том случае - скользящий агрегат)
окно - интервал данных в пределах группы
... и функции на серверных БД для тех моих целей, верю вам, что найдутся... НО ещё себе не верю  :) , что ради одного моего незнания стоит заводить сервер... может, незнание это временно (и через несколько месяцев это пройдёт)... как, кстати, можно агрегировать на SQL на ветке Поиск последних 3 значений и возврат их среднего - сегодня я поняла (нашла), задумавшись о манящей простоте Оконных функций, отмеченных вами... (там, на ветке, и спасибо) - тоже не один месяц мне это снилось  8) - а оказалось - это возможно ... и пока рассматриваю альтернативы в простых Office'ных возможностях для аналитических моментов... хотя свои плюсы, конечно же, есть у Oracle
основное достоинство аналитических функций в том, что все выполняется в один проход по таблице
и для оптимизации запросов тоже есть поле непаханое, например в Oracle ...
... но всё же вы правы, объём данных для сервера у меня явно ещё не насобирался, а потребности в нём вообще отсутствуют... а вот "краткость - сестра..." того, что ещё и не снилось - у меня по всем кодам явно отсутствует... вот и стремлюсь, как-то более компактные строки выискивать и брать на вооружение...
p.s. получилась исповедь девушки, не нуждающейся в сервере   8)  ... вы правы, линки по серверам в мой ход мыслей пока рано помещать... ещё подумаю (поищу), как то окно join'ами сделать - по типу скользящего агрегата... если есть идеи для Access - то долго ещё будем справляться без сервера.....
p.p.s под спойлерами цитаты с др. ресурсов
Изменено: JeyCi - 31.08.2015 20:54:27
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
Доброе время суток
Цитата
а вот возможно ли такое же соорудить без кода, а в Access (или MS Query) запросом - пока что сильно затрудняюсь сказать
Можно и без оконных функций, только помнить о декартовом произведении;) Специально разбил на подзапросы, чтобы было проще анализировать (запрос для получения результата GetTotal). Естественно, всё это можно объединить в один запрос.
А о серверной БД, видимо вам стоит уже думать. Тут вопрос даже не в объёме данных, а в простоте решения. С аналитическими функциями можно искать и как локальные минимумы, так и глобальный минимум по всем данным.
Успехов!
 
JeyCi, если Вы не против, то дополню Вашу тему, возможно, интересной программой. (не использовал, случайно наткнулся при поиске другого)
Программа SQL2Excel

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
Цитата
JayBhagavan написал:  возможно, интересной программой
JayBhagavan, спасибо -  уверена, если сделать работу в Excel c SQL более юзер-фрэндли, то очень быстро очень многие оценят по достоинству эту превосходную возможность работать с данными в Excel, как с данными БД, особенно на больших объёмах... добавлю ещё пару линков:
-- PivotPlay PLUS Add-in - (в середине страницы название с PT0020 -) - даёт возможность править запрос к источнику сводной таблицы, построенной на основе подключения к источнику, как к внешним данным...
-- DbConsole - "Данная программа представляет собой практически обычную SQL консоль. В задачи подобных приложений входит выполнение SQL-запросов и отображение их результатов. Особенность DbConsole в том, что она рассчитана на работу с различными реляционными базами данных"...
-- АКТИВНЫЕ ТАБЛИЦЫ - надстройка для обработки данных в Excel...
P.S.
сама активно пользуюсь надстройкой с последнего линка (версии 1.01) - экспериментировать с запросами в xl - не очень удобно, т.к. окошко Определения (Данные-Подключеня-Свойства) - очень небольшое, чтобы видеть весь запрос и править его... !! а данная надстройка помогает мне 1) быстро взять нужные данные (если не планирую вечный обновляемый запрос) и 2) поковыряться в запросе в режиме "запрос - создать запрос выборки данных вручную" - когда строю запрос и тестирую его (не всегда же с первого раза могу написать правильный - приходится и поправки вносить и красоту наводить и причёсывать запрос - периодически проверяя, запуская, пока он не заработает)...  :idea: с этой надстройкой и глазам удобнее намного и рукам...
хотя иногда и просто в Notepad++  (это "редактор с подсветкой синтаксиса и неплохим функционалом") - сохраняя txt-файл в формате .sql - тогда и подсветка синтаксиса проявляется - чтобы глазами легче вылавливать структуру запроса, который строю...

P.P.S. Opal-Convert Excel/CSV to SQL
Изменено: JeyCi - 21.10.2015 07:38:44
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
Цитата
Андрей VG написал: Специально разбил на подзапросы, чтобы было проще анализировать
:idea: вот! наконец, теперь я знаю, как их проще писать... - Андрей VG, этот ваш совет просто неоценим! - когда "сразу и весь" запрос не получается - действительно, лучше делать по частям... respect!!
Цитата
Андрей VG написал: Естественно, всё это можно объединить в один запрос.
объединила  :)
GetTotal

... только Min итоговых записей сейчас пробую вывести (как-то правильно сгруппировать надо, похоже, - я пока в процессе)... но вчера на целый день задумалась о
Цитата
Андрей VG написал: Можно и без оконных функций, только помнить о декартовом произведении;)
Декартово произведение
- только чем оно может навредить/помочь пока не осознала...  :qstn:
p.s.
пример
Изменено: JeyCi - 28.09.2015 10:15:20
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
Доброе время суток
Представим выполнение запроса к двум таблицам, указанным в предложении From как декартово произведение, где для каждой записи "левой" таблицы выводятся все записи "правой" таблицы, а число полей равно числу полей "левой" таблицы плюс число полей "правой".
Теперь из получившегося декартовым произведением отношения (таблицы) выбираем только те записи, которые удовлетворяют условию Where и/или On предложения Join. А список выводимых полей определяется предложением Select.
В вашей же задаче, как раз и нужно декартово произведение. Где для каждого значения Strike (левая таблица) нужно сопоставить все остальные значения Strike (правая таблица) и ограничить для Call меньшими значениями, а для Put большими, собственно это и использовалось в подзапросах GelCall, GetPut, плюс к результирующему отношению применялась группировка по Strike "левой" таблицы. Вся остальная математика и так понятна.
 
Действительно, Доброе!!
Андрей VG, вы, как всегда, правы - оно (декартово произведение) не мешает, а помогает   8) ... его, главное, понять...
искомая строка нашлась без сервера -
а) или просто упорядочить по возрастанию ваш труд и взять TOP 1 (запрос MIN)
б) или ввести доп. столбец для группировки и отбор совпадения с минимальным значанием (запрос test)
(в) могут быть вариации на тему EXISTS в п.(б) вместо равенства --> тогда запрос даже без "order by" выдаёт выборку по возрастанию, из которой можно тоже взять TOP 1 в качестве искомого минимуму -- причина уже понятна по предыдущим постам
Цитата
Андрей VG написал: создание временных индексов по результату анализа запроса и их использование во время его выполнения.
... ВЫВОД: язык непростой, но интересный... Понимание SQL (Understanding SQL) - (Мартин Грабер)
Андрей VG, спасибо за очень показательный пример...
P.S.
файл дополненный (а) и (б) прилагаю
... навыки находить оптимальные связи для запросов - надеюсь, выработаются со временем... - ссылки на ресурсы оптимизационного рода, конечно же, тоже помогут сделать ветку более информативной,  поскольку верно составленный запрос - залог скорости его исполнения, - сама была свидетелем своих мыслей: когда от перестановки последовательности действий по отбору, скорость исполнения запроса качественно возрастала...
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
Цитата
JeyCi написал:
когда "сразу и весь" запрос не получается - действительно, лучше делать по частям
С вами не согласны.
There is no knowledge that is not power
 
Johny, и я с вами не согласна - что ваш линк касается используемой вами цитаты... я не писала про Top Down подход, да и пример Андрея явно о другом
Изменено: JeyCi - 03.09.2015 18:27:12
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
Доброе время суток
JeyCi, в предложении Order By не обязательно использовать вычисляемое выражение, достаточно указать номер поля по порядку в Select, начиная с 1.
Для запроса MIN
Код
Order By 3

Большинство баз поддерживают в этом предложении и указание псевдонима поля, указанного в Select, но не Access, увы.
P. S. Offtop JeyCi, да не обращайте так много внимания на Johny. Судя по последней сотне постов, человек скатывается к мелкому, хотя местами и не без информации, подкалыванию.
Изменено: Андрей VG - 03.09.2015 19:48:25
 
нашлись и такие Оконные функции LAG и LEAD - появились в SQL Server, начиная с версии 2012
-  :idea: !!  и им имеются "классические" решения ЗДЕСЬ
Изменено: JeyCi - 06.09.2015 20:20:41
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
Цитата
Андрей VG написал: Order By 3... достаточно указать номер поля по порядку
Андрей VG, ваши рекомендации всегда качественно упрощают мой ход мыслей... до моих талантов ещё далеко, но на бОльшую краткость я уже точно могу надеяться благодаря вам  :) thanks a lot!...
приложу:
- ваш ODBC-пример по программному выводу параметрического запроса в Excel (#22)
- мои ODBC-тренировки запроса из нескольких файлов Excel (#12, #13)

==============
p.s. впечатления (в общем и целом):
если данных немного - то, думаю, всё-таки сам Excel уже в состоянии дать приемлемые и достаточные средства для оперирования инфо по принципу БД...
Access может дать более юзер-френдли среду для знакомства с запросами...
а если данных много и хранятся на сервере, то и Excel, и Access могут их импортировать...
и сами по себе, и посредством ADO
(только один нюанс ещё не совсем понятен без опыта - CursorLocation - adUseServer(2) по умолчанию или  adUseClient(3) - курсор на стороне сервера или клиента.
- ??влияет ли это на диалект SQL, который надо использовать, для импорта данных в Excel или Access через ADO? -
-------
p.p.s
справедливости ради, думаю, всё-таки, не стоит забывать и о PowerPivot в excel
Цитата
R Dmitry писал: access???
excel & ms server прекрасно уживаются вместе.  
...  если таблицы большие то лучшe использовать связку msserver-powerpivot-excel  
Данные будут в кэш и все будет летать при правильном подходе :))  
но есть один недостаток у PP :(( данные необходимо сначала обновлять в PP а затем в excel.
Изменено: JeyCi - 07.09.2015 22:29:43
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
Цитата
(только один нюанс ещё несовсем понятен без опыта - CursorLocation - adUseServer(2) по умолчанию или  adUseClient(3) - курсор на стороне сервера или клиента
Давайте разберёмся. CursorLocation используется для объекта Recordset (в основном использовании, таблица результат запроса к базе данных Recorset.Open SQLtext,objConn). Курсор определяет текущую запись, с полями которой мы можем работать Recordset(FieldName).Value или Recordset(FieldIndex).Value. Свойство же CursorLocation задаёт с какой стороны будет определятся положение этого курсора, указывающего на текущую запись.
Если adUseClient, то со стороны клиентского кода (Recordset.MoveFirst, MoveLast, Find, Seek). Правда, на это управление влияет ещё свойство CursorType, если, например, adOpenForwardOnly, то можно использовать только последовательный просмотр к концу таблицы, используя MoveNext.
Если adUseServer, то положение курсора задаётся со стороны сервера, путём соответствующих команд. Как, не знаю - ни разу не использовал :( .
Если вы используете Recordset, для примера: для вывода его данных на лист Range(Address).CopyFromRecordset Recordset или список ListBox, Combobox - ComboBox.Column = Recordset.GetRows, то в этом случае без разницы, кто задаёт текущее положение курсора. Recordset.MoveNext - доступен в обоих случаях.
Если же вы будете использовать Recordset, например, для поиска данных, то adUseClient. Свойство CursorLocation можно установить только перед использованием метода Open.

Recorset сам по себе полезный объект. Если определить методом Sort столбец или список столбцов, то неявно будет создан временный индекс. Тогда Recordset можно использовать как аналог Dictionary (всего раза в 1,5-2 медленнее работа, чем с Dictionary), но возможности метода Find заметно шире, в частности можно использовать для текстовых полей Like и получать запись первого или последнего вхождения в зависимости от направления поиска (хотя Like, увы, не может использовать индекс - медленно).  Для задач, где требуются использование поиска, сортировки и фильтрации по данным таблиц что Excel, что Access непосредственно в коде - это очень удобный инструмент.
 
Цитата
Андрей VG написал:   Для задач, где требуются использование поиска, сортировки и фильтрации по данным таблиц что Excel, что Access непосредственно в коде
точно! это, наверно, у меня уже что-то в рассудке помутнилось... наверно, пока от нехватки опыта... действительно, ведь для Recordset есть свои методы Sort, Filter, Find - эквивалентные ORDER BY, WHERE, SELECT в sql-запросах... и в коде по Recordset'у можно ходить как угодно даже без запросов - синтакисом ADO... значит, только не забывать указывать CursorLocation (3)  для этих методов, потому что
Цитата

[*]adUseServer(2) - курсор на стороне провайдера (по умолчанию).
[*]adUseClient(3) - курсор на стороне пользователя. Может предоставлять дополнительные возможности, которые отсутствуют на стороне провайдера.
с ваших объяснений теперь точно запомню ...
кстати начало ветки с примера на ADO и начиналось - мало ли проверку на уникальность, вносимых данных, придётся вставлять  ;)
p.s.
только, наверно, прототипа JOIN нет в методах для Recordset'a(ов)?? не нашла
Изменено: JeyCi - 31.03.2017 15:56:49
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
Цитата
только, наверно, прототипа JOIN нет в методах для Recordset'a(ов)?? не нашла
Их и нет. Это же объект одной таблицы. И так не мало, в общем то. Ronin751 тут тему создал, можно как раз посмотреть код для работы с фильтром в Recordset - есть некоторые отличия, части столь привычных в базах данных возможностей нет (Between And , In (list)), но в общем решаемо.
Можете ещё решение посмотреть. и этот (здесь вообще нет SQL запроса для создания Recordset).
P. S. Прошу не воспринимать ссылки за личную рекламу  ;)
Изменено: Андрей VG - 07.09.2015 23:58:52
 
Цитата
Андрей VG написал: Прошу не воспринимать ссылки за личную рекламу  
ни в коем случае не реклама, а спасибо за качественные примеры вашей руки!  :)  
P.S. добавлю ещё о Recordset, который не связан запросом к источнику данных  (также ваш) #105
Сохранить картинки из recordset в файлы #4
Изменено: JeyCi - 17.09.2016 20:57:22
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
столкнулась сегодня с ошибкой в Excel(vba):
- 2147467259 (odbc) ошибка
Несоответствие типов данных в выражении условия отбора
Источник: Microsoft Access Database Engine
Долго пыталась понять, что с типами данных  :cry: - пока не довёл поисковик до мысли...
Скрытый текст
, что как-то неверно "открываю-не закрываю" RecordSet...
поправила по правилам - вздохнула облегчённо:
Код
Set cn = CreateObject("ADODB.Connection")
       cn.Open sCon
       Set rs = cn.Execute(sSQL)    
       [u2].CopyFromRecordset rs    
 rs.Close:  Set rs = Nothing
 cn.Close:  Set cn = Nothing
p.s. пишу на случай, если кого-нибудь постигнет такая же беда (многие советуют драйвер переустанавливать - но по факту, оказалось, лучше свой синтаксис проверить  :oops: )
*******
p.p.s.
ещё одна возможная беда описана здесь -
не всегда стоит указывать жёсткий диапазон, xl может не понять при запросе sql
Изменено: JeyCi - 24.04.2016 12:39:15
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
Страницы: Пред. 1 2 3 4 5 6 7 8 9 След.
Наверх