кстати (около темы) когда-то давно встречал пример кода создания mdb-файла из Excel без установленного Access. конечно, весь функционал, начиная от создания таблиц, приходится прописывать самому. не говоря уж об интерфейсе, но и плюс очевиден - использование БД без Access (ну не у всех пользователей он установлен).
если кто-то кинет ссылочкой - буду благодарен.
фрилансер Excel, VBA - контакты в профиле "Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
ikki написал: пример кода создания mdb-файла из Excel без установленного Access
На ум приходит библиотека "Microsoft ADO Ext. 6.0 for DDL and Security":
Код
Sub CreateAccessDatabaseWithoutAccess()
Dim cat As ADOX.Catalog
Dim t As ADOX.Table
Dim c As ADOX.Column
'// Новая база
Set cat = New ADOX.Catalog
cat.Create "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Temp\1.mdb"
'// Таблица
Set t = New ADOX.Table
t.Name = "User"
'// Два столбца: Id и Name
Set c = New ADOX.Column
c.Name = "Id"
c.Type = adInteger
t.Columns.Append c
Set c = New ADOX.Column
c.Name = "UserName"
c.Type = adWChar
c.DefinedSize = 255
t.Columns.Append c
'// Добавляем таблицу в базу
cat.Tables.Append t
Set cat = Nothing
End Sub
Мне вот непонятно зачем в Access запихали REPLACE, если после этого нельзя получить данные из Access. Приходится длинно и долго ЗАМЕНЯТЬ через Instr и Mid.
также на заметку: NZ() - Ексель не понимает функцию Nz()
Цитата
функция NZ, это чисто access..ная функция и к Jet отношения не имеет.
Функция Nz - данная функция возвращает значение не NULL. Принимает один обязательный параметр с типом variant и один не обязательный. Она работает следующим образом, если входящий параметр NULL она преобразует его в другое значение, для предотвращения использования NULL значений в выражении. Также если указан второй параметр, то в случае если первый параметр NULL то она вернет его, т.е. второй параметр.
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
Sub tt()
bn_ = "База.accdb"
zn_ = "Test_zapr"
Set a_ = CreateObject("Access.Application")
On Error GoTo A
With a_
.Visible = 0
.opencurrentdatabase ThisWorkbook.Path & "\" & bn_
.docmd.OpenQuery zn_
End With
Exit Sub
A:
On Error Resume Next
Set a_ = Nothing
On Error GoTo 0
End Sub
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
If the value of the variant argument is Null, the Nz function returns the number zero or a zero-length string (always returns a zero-length string when used in a query expression), depending on whether the context indicates the value should be a number or a string. If the optional valueifnull argument is included, then the Nz function will return the value specified by that argument if the variant argument is Null. When used in a query expression, the Nz function should always include the valueifnull argument. If the value of variant isn't Null, then the Nz function returns the value of variant.
фрилансер Excel, VBA - контакты в профиле "Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
NZ это геморрой. SQL его не понимает, в Excel данные не затянешь из Access. Можно пользоваться только в самом Access. Советую использовать универсальное IIf([поле] Is Null;0;[поле])
Михаил Лебедев написал: Чего нет в акцесс, но есть в ексель: 4. Сводные таблицы, конечно же.
вот ещё один момент заметила: после экспорта данных из access в xl - сводная строиться не хочет далее из этих данных (кнопка построения сводной в xl не подсвечена)... побороть эту беду получилось птицей в поле "Экспортировать данные с макетом и форматированием" (access 2010) - чтобы потом из экспортированных данных можно было строить сводную в xl... ! p.s. хотя, конечно же можно подключиться и напрямую к БД - (линк поста #28), или Сводная из базы данных Access
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
Нетрудно заметить, что создание внешней связи, при которой возвращаются все записи из обеих таблиц, как имеющие соответствующие ключевые поля, так и не имеющие их, не предусмотрено. Для решения этой задачи используется трюк, в котором все три доступных типа соединения объединены в один запрос. В это л итоговом запросе используется оператор Union, объединяющий результаты всех трех запросов.
Код
SELECT tbl_Покупатели .Фамилия , tbl_ПокynKи.Дата
FROM tbl_Покупатели INNER JOIN tbl_Покупки ON
tbl_Покупатели.Покупатель_ID =tbl_Покупки. Покупатель_ID
Union
SELECT tbl_Покупатели .Фамилия , tbl_Покупки .Дата
FROM tbl_Покупатепи LEFT JOIN tbl_Покупки On
tbl_Покупатели.Покупатель_ID = tbl_Покупки.Покупатель_ID
Union
SELECT tbl_Покупатели .Фамилия , tbl_Покупки .Дата
FROM tbl_Покупатели RIGHT JOIN tbl_Покупки ON
tbl_Покупатели.Покупатель_ID = tbl_Покупки. Покупатель ID;
Оба столбца (взятые из разных таблиц) наряду со строками, заполненными записями, имеющими соответствия, имеют и пропуски в тех местах, где их записям не находится соответствий в другой таблице.
т к LEFT JOIN, и RIGHT JOIN уже содержит в себе все данные INNER JOIN - то, многие считают, что избыточно включать 3-й запрос в UNION. - поэтому как-то так полное внешнее объединение может выглядеть:
Код
SELECT * FROM
(SELECT Field1, ...
FROM Table1 AS T1 LEFT JOIN Table2 AS T2 ON T1.X = T2.X
UNION SELECT Field1, ...
FROM Table1 AS T1 RIGHT JOIN Table2 AS T2 ON T1.X = T2.X) AS Data
Select Distinct * from ((select
* from t1 left join t2 on t1.id=t2.id ...) union (select * from t1 right join t2
on t1.id=t2.id ...))
не всегда есть аналогом FULL OUTER JOIN Например, если в
Код
(t1 left join t2 on t1.id=t2.id ...)
условия t1.id=t2.id ... не будут обеспечивать связь один к одному, то Distinct удалит не только одну из задвоенных записей, включенные в выборку и по left join и по right join, но и удалит нужные записи, полученные в результате соединения один ко многим. --------------------------------------------------- !!! -- А вот это и есть та самая тонкость реализации. Ибо, с точки зрения сиквела, - мы предполагаем связь именно по уникальному ключевому выражению...
... сижу вот, сама не знаю, что делаю, - всё задваивается... пока в раздумьи... но, наверно, всё-таки если правильно JOIN'ить (придать бы уникальности ключам) , то таких казусов не должно быть??... p.s. надо, наверно, ставить проверку на IS NULL....... чтобы не мечтать бороть задвоение DISTINCT'ом....... p.p.s off ! спасибо vikttur ... я тоже подправила #39... - happy week-end
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
если надо сделать несколько JOIN'ов (пример итогового запроса из кн. Р.Дженнингса)...
Код
SELECT Customers.Country, Categories.CategoryName,
Sum(CCur([Order Details].[UnitPrice]*
[Quantity]*(1-[Discount]))) AS Amount
FROM (Categories
INNER JOIN Products
ON Categories.CategoryID = Products.CategoryID)
INNER JOIN ((Customers INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID)
INNER JOIN [Order Details]
ON Orders.OrderID = [Order Details].OrderID)
ON Products.ProductID = [Order Details].ProductID
GROUP BY Customers.Country, Categories.CategoryName
ORDER BY Categories.CategoryName;
иногда легче (в Конструкторе точно легче, чем join'ить несколько раз) использовать WHERE оператор, чтобы задать все нужные связи между таблицами:
Код
SELECT Customers.Country, Categories.CategoryName,
Sum(CCur([Order Details].UnitPrice*
[Quantity]*(1-[Discount]))) AS Amount
FROM Customers, Orders, [Order Details], Products, Categories
WHERE Categories.CategoryID=Products.CategoryID
AND Customers.CustomerID=Orders.CustomerID
AND Orders.OrderID=[Order Details].OrderID
AND Products.ProductID=[Order Details].ProductID
GROUP BY Customers.Country, Categories.CategoryName
ORDER BY Categories.CategoryName;
... что по сути даст ту же выборку, что и 1-й запрос с inner join'ами ...
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
JeyCi, full outer join в Access можно реализовать следующим образом
Код
Select t1.id From t1 Inner Join t2 On (t1.id=t2.id)
Union
Select t1.id From t1 Left Join t2 On (t1.id=t2.id) Where t2.id Is Null
Union
Select t2.id From t2 Left Join t1 On (t1.id=t2.id) Where t1.id Is Null
Не увидел очень полезного сочетания клавиш: Ctrl+2. Копирует в активную ячейку значение из ячейки над активной. В таблицах акцесс и формах акцесс. И не важно, в режиме формы или в режиме таблицы находится форма.
Всё сложное - не нужно. Всё нужное - просто /М. Т. Калашников/
Михаил Лебедев написал: Ctrl+2. Копирует в активную ячейку значение из ячейки над активной
буквально вчера делала это так (как по линку)
Цитата
Вставка значения из того же поля предыдущей записи CTRL+АПОСТРОФ (')
т е апостроф, который на одной кнопке с "(кавычки) и с буквой "э"... недалеко от Enter... и, что интересно, на вашей кнопке 2 (слева вверху клавиатуры) - тоже нарисованы кавычки (")... наверно, access'у всё равно, где на клавиатуре брать эти кавычки - он всепонимающий
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
Андрей VG написал: full outer join в Access можно реализовать
Андрей, спасибо большое - задвоение прошло благодаря вам ... ваш №43 очень интересен тем, что 2 LEFT JOIN - я пока не очень лихо переворачиваю глазами RIGHT JOIN и LEFT JOIN - спасибо вам за этот альтернативный пример - беру себе на заметку... я в итоге сделала, как в вашем примере, только с Right JOIN последним
Код
... UNION Select t1.id From t1 RIGHT Join t2 On (t1.id=t2.id) Where t2.id Is Null
(надеюсь не ошиблась) - вроде, что хотела отобрала... p.s. просто для инфо по ветке: не во всех СУБД можно использовать Right Join (точно помню, где-то только Left, но не помню где)... НО в Access можно и Правое Объединение... как говорится, разница между левым и правым - лишь в том, с какой стороны мы смотрим на таблицы...
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
нашлась ещё одна особенность запросов в Access (справедливо и для xl т.к. один провайдер БД) - отсутствие EXCEPT оператора
Цитата
R Dmitry написал: Except в Jet нет... но по сути Except это аналог NOT IN, и очень медленный
#22 (18 Мар 2013) - при использовании NOT IN тоже не надо забывать про особенности троичной логики ()
Скрытый текст
Например, выбрать список цветов, еще не использованных в продуктах. Другими словами, нам нужно сконструировать запрос, возвращающий только те цвета, для которых нет продуктов такого цвета. На первый взгляд может показаться, что предикат NOT IN предоставляет очень интуитивный способ создания такого запроса, очень близкий к тому, как проблема выражается на простом английском языке:
Код
SELECT C.color
FROM Colors AS C
WHERE C.color NOT IN (SELECT P.color
FROM Products AS P);
Казалось бы, запрос вернет две строки ('black' и 'green'), но на самом деле он возвратит пустой результат. Это, очевидно, «неверно»!! Просто SQL использует троичную логику, управляемую наличием NULL, являющимся не значением, а маркером, указывающим на отсутствующую (или неизвестную) информацию. Когда оператор NOT применяется к списку значений из подзапроса в предикате in, это переводится как: "color NOT IN (Red, Blue, NULL)". Что эквивалентно: "NOT(color=Red OR color=Blue OR color=NULL)" Выражение "color=NULL" вычисляется как UNKNOWN и, по правилам троичной логики, NOT UNKNOWN также сводится к UNKNOWN. В результате все строки отфильтровываются, и запрос выдает пустой результат.
Эта ошибка часто всплывает при изменении требований, когда ранее не содержавшая NULL колонка изменяется и начинает содержать NULL. Это также подчеркивает потребность в тщательном тестировании. Даже если в исходном дизайне колонка не допускала наличия NULL, нужно убедиться, что запросы будут нормально работать с NULL !!
верное решение выглядит так (убрать влияние NULL): - использовать осторожно - может долго исполняться (см. спойлер ниже)
Код
'/* IS NOT NULL in the subquery */
SELECT C.color
FROM Colors AS C
WHERE C.color NOT IN (SELECT P.color
FROM Products AS P
WHERE P.color IS NOT NULL);
NOT IN
Почему SQL в случае Not In не хочет остановиться после того, как будет найдено совпадение слева и справа и (в отличие от In) уже НЕ ВКЛЮЧАТЬ это значение в окончательный список - я не знаю, это вопрос к производителям движков SQL. возможно: NOT IN – это проверка среди списка значений. Поэтому после выполнения подзапроса должно произойти сравнение указанного в главном запросе поля с этим полученным списком. Причем, раз стоит условие NOT IN, значит, проверить на несовпадение надо ВСЕ значения из списка.
альтернативное верное ещё так (учесть поведение NULL):
Код
'/* LEFT OUTER JOIN */
SELECT C.color
FROM Colors AS C
LEFT OUTER JOIN Products AS P
ON C.color = P.color
WHERE
P.color IS NULL;
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
(по предыдущей задаче) ... так же можно исключить отдельные записи из итогов выборки, используя двоичную логику EXISTS (то есть TRUE/FALSE)... 3-й верный вариант вместо Except:
Код
SELECT C.color
FROM Colors AS C
WHERE NOT EXISTS (SELECT *
FROM Products AS P
WHERE C.color = P.color);
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
последние 2 поста - copy-write, показавшийся важным... если кто захочет видеть всю статью, то: Десять распространенных ошибок SQL-программирования (наверно, не всё относится к Access - но варианты на заметку можно почерпнуть)
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
(к #48 - как понимаю) (в случае чего исправления принимаются ) попробую прокомментировать двоичную логику EXISTS (булево): - если внутренний запрос возвращает хотя бы одну строку -- условие Exists приимает значение TRUE --> выполняется внешний запрос... (полагаю, ТОЛЬКО с теми записями, которые выполнили условие внутреннего запроса) - если внутренний запрос не возвращает ни одной строки -- условие Exists приимает значение FALSE --> НЕ выполняется внешний запрос
логика NOT EXISTS показалась ещё интереснее ("два буля"): - если внутренний запрос не возвращает ни одной строки -- условие Exists приимает значение FALSE --> выполняется внешний запрос!! - если внутренний запрос возвращает хотя бы одну строку -- условие Exists приимает значение TRUE --> НЕ выполняется внешний запрос!
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
JayBhagavan написал: Лучше было бы разместить данное сообщение в теме ДжейСи
сделано доброе дело общими усилиями... всем спасибо
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
Примечание Плоский файл — это структура данных, не являющаяся реляционной. В качестве примера - список Excel: его записи находятся в строках, а поля — в столбцах. Это двумерная, или плоская структура данных. Однако зачастую использование подобной организации данных влечет за собой множество проблем.
Именно общее поле (оно называется связующим - или Ключом) позволяет установить связь между таблицами и превращает базу данных в реляционную структуру. Она больше не является простым плоским файлом.
ЗАЧЕМ же повторять огромные фрагменты постоянной (!) информации о двери в записях, посвященных ее периодическому обслуживанию?.. ЕСЛИ можно создать нормальную реляционную структуру ...
там же примеры Объединения родительских и дочерних записей (дубль линка #7)
p.s.
моё глубокое убеждение: лучше один раз понять релятивную природу Access - чтобы логики правильного ведения БД придерживаться и при создании списков в Excel для комфортной работы с информацией... все прежнии заблуждения о комфорте работы с таблицами можно исправить Редизайнером таблиц
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
JeyCi написал: лучше один раз понять Access - чтобы логики правильного ведения БД придерживаться
Не Access зависит от "логики БД", а наоборот. Access для того и создали, чтобы воплотить идеи дизайна базы данных. Скажу больше, что Access, в частности (а любая RDBMS - в целом), является лишь физической имплементацией схемы базы данных. Поэтому самое главное - это создать правильную базу данных. И что такое - "понять Access"? Его менюшки-плюшки? Ерунды не говорите и других не смущайте.
Johny написал: самое главное - это создать правильную базу данных.
об этом и речь... - почувствовать вкус Реляционных структур... Access даёт эту возможность, как и другие СУБД...
Цитата
Johny написал: Ерунды не говорите и других не смущайте.
... а вы не смущайтесь - а за всеми кажущимися ограничениями умейте видеть и возможности... p.s. речь не о всеобъемлющей корпоративной БД (она не всем дома нужна), а лишь о взаимополезности знакомства с обоими - Excel и Access -
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
JeyCi написал: о взаимополезности знакомства с обоими - Excel и Access
Открою секрет, что работать с БД Access можно и без самого Access'а. Всё, что нужно - это просто драйвер. Другое дело, когда нужен удобный конструктор таблиц и другие плюшки, то тут нужен сам Access, а для отправки запросов - он необязателен.
работа с русскоязычным Access проявила одно (не)большое неудобство. Если была практика решения этого вопроса - поделитесь, please?? Функция FormatDateTime - даёт очень скромные возможности по заданию нужного формата Даты. Функция Format даёт 3-хбуквенный месяц на русском языке в таком виде: Format([t].[dating];'mmm') - согласно то ли параметрам языковой настройки системы, то ли согласно русскоязычному Access... НО проблема в том, что не получается получить 3-хбуквенную аббревиатуру месяца на Англ.яз??
Скрытый текст
(т к надо потом по этому значению отобрать из столбца, где SEP15 текстовый - такая выгрузка из стороннего источника и далее по многим вопросам нужен этот столбец в таком виде - текстовом английском - особо не переделаешь, а отобрать по месяцу и году надо)
может быть, кто-то имел с этим дело? и как сказать, например, в запросе?
Код
SELECT Format(t.dating,'mmm') AS mn FROM base AS t WHERE UCase(Format(t.dating,'mmm'))= LEFT(t.Exp,3)
- где mmm - на рус.яз (т к Access рус.яз, да и система рус.яз), а t.Exp - например SEP15 (на англ.яз)? ...
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
не нашла лучшего варианта, кроме как изменить Параметры Языковых стандартов на Англ-Новая Зеландия с форматом вывода даты d.mm.yy - тогда можно сопоставить в запросе англ.яз аббревиатуру месяца с числовым выражением месяца - поживу пока в Новой Зеландии... Если найдётся др. решение - вернусь домой...
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
можно сказать, съездила в отпуск - возвращаюсь из Новой Зеландии: 1) JayBhagavan, спасибо, что напомнили, - эта таблица у меня есть в рабочем файле - но для др целей - захотела реализовать более компактно (без неё) - да и напрочь забыла про неё... 2) цифру в англ 3-хбуквенную аббревиатуру получилось превратить так (рус.яз. Access):
... ну, а дальше, можно использовать этот штрих под любой запрос, где из Даты надо сделать "заморские" буквы, чтобы потом их крутить-вертеть в запросе, как понравится...
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)