| С праздником ! |
| Поздравляю прекрасную половину подписчиков с праздником 8 Марта. Желаю большого личного счастья, море цветов, кучу подарков, теплых и нежных слов, а также прекрасного настроения в эти дни. Второй половине - желаю проявить побольше изобретательности и щедрости при подготовке праздника. |
| Новости |
• Малютин Николай дал дополнительные интересные ответы на вопросы 239 (26 выпуск) - клавиатура, 252(26 выпуск) - архивация.
• Alexander Tumarov добавил свою точку зрения на проблемы 237 вопроса (26 выпуск)
• Добавлена ссылка на конвертор AccessToVB: 243 (26 выпуск, mdb->exe) |
|
Access 2000. Урок 7. Выборка из нескольких таблиц
|
Конечно, разрабатывая базу данных Вам потребуется создавать запросы не только из одной, а из двух и более таблиц. Разработку таких запросов желательно начинать не с конструктора запросов, а с детальной проработки схемы связей таблиц. В этом случае, Вам придется меньше времени потратить на подготовку самих запросов, т.к. ядро Access в этом случает будет автоматически формировать в конструкторе запросов связи между таблицами.
Можно предложить 3 (наиболее употребительных) способа объединения таблиц.
Во первых, с использованием слова WHERE.
Во вторых, с использованием слов INNER[1], LEFT[2], RIGHT[3] и JOIN[4]
В третьих, с использованием слова UNION[5].
Примеры использования приведены в таблице. Желательно их смотреть через проводник запросов файла la_query.mdb
| Название |
SQL - запрос |
| WHERE объединение |
SELECT Курс.Дата, Сумма.Название, Сумма.Сумма, Курс.Курс FROM Курс, Сумма WHERE Курс.Дата = Сумма.Дата |
| INNER объединение |
SELECT Курс.Дата, Сумма.Название, Сумма.Сумма, Курс.Курс, [Сумма]/[Курс] AS USD FROM Курс INNER JOIN Сумма ON Курс.Дата = Сумма.Дата |
| LEFT объединение |
SELECT Курс.Дата, Сумма.Название, Сумма.Сумма, Курс.Курс, [Сумма]/[Курс] AS USD FROM Курс LEFT JOIN Сумма ON Курс.Дата = Сумма.Дата |
| RIGHT объединение |
SELECT Курс.Дата, Сумма.Название, Сумма.Сумма, Курс.Курс, [Сумма]/[Курс] AS USD FROM Курс RIGHT JOIN Сумма ON Курс.Дата < Сумма.Дата |
| UNION объединение |
SELECT Сумма.Название, Сумма.Сумма FROM [Сумма]
UNION SELECT "$ от " & Дата as Название, Курс as Сумма FROM [Курс] |
P.S. Домашнее задание.
1. Большинство разработчиков используют при объединении таблиц только равенство полей, попробуйте задать в проводнике различные уcловия <, >, between для ключей. Это поможет Вам более детально разобраться в структуре запросов.
2. При решении задачи в предыдущем уроке невозможно создать запрос с использованием всех предложенных слов. Решение заключается в создании нескольких запросов и объединении их с помощью UNION. |
| Словарь |
• [1] INNER (внутри) - выбираются только уникальные записи совпадающие с ключами таблиц
• [2] LEFT (слева) - все данные берутся из таблицы СЛЕВА, а из правой таблицы ведется отбор по ключевым полям.
• [3] RIGHT (справа) - все данные берутся из таблицы СПРАВА, а из левой ведется отбор по ключевым полям
• [4] JOIN (объединить) - слово применяется для отбора записей по словам INNER, LEFT, RIGHT
• [5] UNION (объединить) - слово применяется для объединения таблиц без использования ключевых полей. |
Ответы на вопросы
На вопросы отвечает также и Малютин Николай.
| Вопрос 62 (15.11.2000). После установки Office 2000 в новом Access исчез мастер построения выражений. Скажем, если в конструкторе форм выбрать некий элемент управления и встать в его окошко свойств "Данные", то кнопочка мастера там даже не появляется. Аналогично этот мастер исчез из контекстного меню в модулях VBA. Замечу, что Access у меня установлен полностью. |
| Ответ. Не знаю почему у Вас удалены управляющие элементы меню, но их можно восстановить командой Reset. Например, CommandBars("Строка меню").Reset |
| Вопрос 116 (06.12.2000). Как предотвратить несанкционированный доступ к объектам программы и коду перед ее распространением? |
| Ответ. Теоретически никак, потому что существуют декомпиляторы, которые могут перехватить любое событие вызываемое msaccess.exe. Чаще всего программу переводят в mde формат. Если это не возможно, то надо по возможности некоторые программы на бэйсике перевести в DLL библиотеки и использовать доступ к ним. Установка паролей, защита объектов по уровню доступа (администратор, группа, пользователь) не очень эффективна. |
| Вопрос 175(09.01.2000) На MS SQL Server (7.0) имеется процедура добавления/изменения/удаления данных в трех связанных таблицах в зависимости от добавляемой/изменяемой строки в четвертой. Процедура вызывается из модуля не связанной с источником данных формы. Процедура работает, данные изменяются согласно заложенному алгоритму, отлаживалась с/без Access (97 SR2) при пустых/небольших таблицах. Модуль отрабатывает без ошибок и предупреждений. Файл БД закрывается нормально, сжатие/восстановление проблем не вызывает. ODBC (Access 4.00.35.13, SQL 3.70.06.23), win95, winNT. Если процедура не вызывалась, и БД и Access закрываются как положено, в противном случае закрывается только БД. Может, посоветуете, где с бубном попрыгать? |
| Вопрос снят. if me.child_form.option_button then. |
| Вопрос 206(31.01.2000) Хочу повысить свой уровень владения акцессом, и поэтому ищу - где можно пройти курсы обучения по А97 - А2000. Интересуют только краткосрочные в любом городе. Рекомендации и советы очень желательны. |
| Ответ. К сожалению рекомендацию, в какие курсы лучше всего вкладывать деньги, дать не могу. Попытайте удачу сами. |
| Вопрос 257(27.02.2000) Возможно ли программно вставлять данные из clipboard'а в форму, а именно в TextBox? |
| Можно, пример дан в файле la_api.mdb. |
Вопрос 262(01.03.2000) Можно ли из модуля закрыть текущую базу данных и открыть другую. Это нужно для создания резервной копии текущей базы. Перед копированием необходимо ее сжать, а это невозможно сделать пока она открыта. Поэтому я
планировал открыть другую вспомогательную базу, где есть модуль сжимающий основную базу и копирующий ее на ZIP. Возможно есть другое решение этого вопроса? |
Ответ. Малютин Николай. Совсем не обязательно прекращать работу в БД, можно воспользоваться функцией CopyFile
Private Declare Function CopyFile Lib "kernel32" Alias "CopyFileA" _
(ByVal lpExistingFileName As String, ByVal lpNewFileName As String, _
ByVal bFailIfExists As Long) As Long
' процедура копирует исходный файл БД в новый с именем ( на усмотрение)
' и сжимаем новый файл БД с помощью ключа командной строки
Public Sub CopyF(strPath As String)
Dim strNewFile As String
On Error GoTo Er_Sub
strNewFile = Left(strPath, Len(strPath) - 4) & "_Copy_" & Date &
Right(strPath, 4)
CopyFile strPath, strNewFile, 1
Shell ("msaccess.exe " & Chr(34) & strNewFile & Chr(34) & " /compact")
Exit Sub
Er_Sub:
End Sub
' Пример вызова:
Private Sub command1_Click()
Call CopyF(CurrentProject.Path & "\" & CurrentProject.Name)
End Sub |
Вопрос 264(01.03.2000) Я делаю базу данных для школы, где есть такое поле, как "Стаж". Но так как я до этого не сталкивался с Access'ом, у меня возникает
такой вопрос: как сделать чтобы стаж автоматически увеличивался на 1 по истечении года, а то исправлять вручную каждому работнику слишком долго. |
Ответ 1. Создайте запрос на обновление. Например, так:
Set dbs = CurrentDb 'Выбираем БД
sqlDate = Format(Date, "\#mm\/dd\/yyyy\#") 'Подготавливаем дату
dbs.Execute "UPDATE Таблица SET Таблица.Стаж = [Стаж]+1 WHERE [Дата]<=" & sqlDate
Ответ 2. Alexander Apazidi Вместо отдельной колонки [стаж] нужно сделать колонку [hire_date (дата приема)] , а стаж можно вычислять в запросе как select date()-hire_date from employees. Тогда не нужно будет обновлять стаж каждый год, месяц, etc.
Ответ 3. Андрей Беляков При том решении вопроса, которое предложено вами, будет нужно контролировать количество выполнений этого запроса. Это будет много сложнее, чем менять 50-60 записей "в ручную" Лучше решение - хранить дату найма и считать, что надо, каждый раз: SELECT DateDiff("y",[EmplDate],Now()) AS YearsEmploee FROM Table; |
Вопрос 267(05.03.2000) Я столкнулся с такой проблемой. Попросили меня написать БД по учету заготовок на складе. Я создал следующие таблицы: - КаталогЗаготовок (Код, Название), - ПриходныеДокументыЗаголовки (Код, НомерДокумента, ДатаДокумента), -ПриходныеДокументыТаблица(Код, НомерДокумента - код в таблице
ПриходныеДокументыЗаголовки, КодЗаготовки - код в таблице КаталогЗаготовок, Количество), -по тому же принципу создал таблицы для расходного документа,
-главная таблица -Регистр заготовок (Код, Заготовка - код в таблице КаталогЗаготовок, Документ - Номер приходного или расходного документа, ТипДокумента, Количество - по документу). Теперь, для получения остатков я создал запрос, который суммирует/вычитает движение заготовок по Регистру. Все работает безукоризненно, но страшно медленно. Я думаю, что неправильно организовал Регистр, а как сделать правильно не знаю. Можете что-нибудь сказать по этому поводу? Буду очень признателен за информацию. |
Ответ 1. Вопросы оптимизация кода достаточно сложные. Надо пересматривать не только таблицы, но vba-код. Это может сделать качественно только профессионал. Самый лучший способ нанять мастера по Access (хотя бы для разработки таблиц). С другой стороны, если нет такой возможности, то прочтите хотя бы информацию в предыдущих выпусках рассылки.
Ответ 2. Андрей Беляков. Для того, чтобы это работало быстро, надо _иметь_ остатки в отдельной таблице остатков и корректировать их при внесении нового приходно/расходного документа. |
| Вопрос 268(05.03.2000) Если вам не тяжело, то ответьте, пожалуйста, но один вопросик ... он до легкий, но все же ... можно ли в ТАБЛИЦЕ (А не запросе) сделать например суммирование столбцов находящихся в данной таблице ... без использования запроса ... ? ! ? |
| Ответ. По правилам ведения реляционных баз данных в таблицах не должно быть повторяющих значений, а Вы целую колонку пытаетесь заполнить повторяющимся "мусором". Так, что задача не имеет практической ценности. С другой стороны, она может быть интересна на олимпиаде по программированию для школьников или студентов. Попробуйте предложить ее им. |
Вопрос 269(05.03.2000) Не могли бы Вы помочь мне решить одну несложную задачку по базам данных. Дело в том, что сейчас я занимаюсь проектированием
базы данных ЭЛЕКТРОННЫЕ ТОРГИ, так вот, у меня есть список акций, котирующихся на бирже в одной таблице, и список котировок во времени в другой. Задача состоит в том, что мне нужно вывести в запросе пят�� последних котировок по каждой из бумаг. По-видимому, здесь нужно воспользоваться предикатом TOP и писать что-то вроде SELECT TOP 5 ...,
однако вся беда в том, что таким образом я могу выбрать либо пять последних котировок по заранее заданной акции, либо вообще пять последних котировок из всех имеющихся, а как выбрать пять последних котировок по каждой из бумаг я пока не придумал. Причем список
котируемых бумаг может изменяться |
Ответ 1. Алгоритм решения задачи такой. Составьте общий запрос на объединение 2 таблиц (я думаю, что это не сложно). Далее отсортируйте его по времени торгов в порядке убывания (ORDER BY [Время] DESC). Теперь можно вставить и предикат ТОP. Он равен [Число акций]*5. Отсортировав отчет по названию акций, Вы и получите искомое решение задачи. Могут быть и другие варианты, например, с использованием в запросе слова BETWEEN или WHERE.
Ответ 2. Alexander Apazidi Если объединить две таблицы и выбрать select top [число_акций*5] мы получим не последние пять котировок по акциям а именно [число_акций*5] последних котировок. Необязательно чтобы все акции за один период времени поменяли свои котировки именно пять раз. Пример: Пусть у нас будут две акции: MSFT (Microsoft NASDAQ),CSCO (Cisco NASDAQ). Если в период с 10:00 До 10:45 по MSFT было 2 сделки, а по CSCO было 8 сделок то select top 10 from [something order by [sale_date] desc вернет 10 котировок, среди которых 2 MSFT и 8 CSCO (а должно быть 5 MSFTx5 CSCO).
Правильный вариант: select shares.name,(select top 5 rate from rates where rate.share=shares.name) as Rate from shares |