Создание таблицы КЛИЕНТЫ

Введение

Это пособие адресовано, прежде всего, тем, кто хочет научиться проектировать базы данных за короткий срок. Вы не найдете здесь объяснений и доказательств. Это пособие практическое, а не теоретическое. Это рекомендации. На простом примере показан процесс разработки базы от структуры данных до отчетов. В пособии уделяется внимание только тем моментам, на которые надо обращать внимание в первую очередь. Показан самый короткий путь создания форм для удобного ввода информации и форм для отбора и просмотра информации из базы (с фильтрацией данных по полям формы). Выбраны типовые, часто встречающиеся задачи и показаны пути их решения. Даны рекомендации по построению отчетов. И, наконец, показано, как сделать меню. Это пособие не предназначено для “ЧАЙНИКОВ”, которые не умеют и не хотят читать литературу. Это пособие – первый шаг в базы данных, снятие барьера, страха перед решением незнакомой и потому кажущейся сложной на первый взгляд задачи.Потом (или параллельно) можно читать литературу, изучать более подробно каждое понятие базы данных (таблица, поле, ключ, индекс, связь; форма, отчет, элемент управления, свойство, событие). Я не смогла удержаться и привела несколько строк кода, без которых пример стал бы уж очень примитивным и скучным.

Это первая часть пособия. Во второй части будут рассмотрены вопросы по улучшению интерфейса. Например, перемещение по форме с помощью клавиш управления курсором, разработка сложных отчетов и запросов, переброска данных в Word и др (тому, что, на мой взгляд, часто бывает нужно, а в книгах либо нет, либо зарыто уж слишком глубоко).

Мелким шрифтом набран текст, который сначала можно пропустить.

 

ШАГ 1. Разработка технического задания

 

Построение базы будем рассматривать на простом примере учета продаж постоянным клиентам в магазине. Главная задача магазина – продажи. Для успешной торговли необходимо, чтобы все наименования товаров присутствовали в продаже. Для привлечения покупателей предоставляются скидки, зависящие от объема покупок. Кроме того, администрация сообщает постоянным покупателям о поступлении новых товаров.

Магазин должен вести учет покупок, запоминая кто, когда, чего и сколько купил.

В связи с этим вырисовываются основные задачи базы данных. Перечислим их.

v Учет клиентов. (Фамилия, адрес и телефон).

v Учет товаров. (Название и цена).

v Учет покупок. (Клиент, дата покупки, номер чека(счета), товар, цена, количество).

v Печать чека (счета) для клиента.

v Отчеты по продажам за период. (по клиентам, по товарам)

Ø Продажи клиентам для назначения скидок. Фамилия клиента, сумма.

Ø Продажи товаров клиентам. Фамилия, список товаров с количеством.

Ø Продажи товаров. Название, сумма, количество.

Дополнительные условия:

· один клиент за один раз может купить несколько различных товаров,

· чек должен выдаваться общий на всю покупку, должны быть указаны: номер чека, клиент, дата покупки, список товаров с суммой по каждому товару и общая сумма.;

· при оформлении продажи после выбора товара из списка цена должна автоматически подставляться из прайса, с возможностью ее изменения.

· Отчет за период по продажам товаров должен позволять изменять данные фильтра периода, причем одна из дат или обе даты могут отсутствовать.

Информация о покупках по клиентам должна выводиться в форму, в которой можно выбирать клиента и период для отчета, любое из полей фильтра может отсутствовать (быть пустым). Если поле пустое, то его как будто бы нет (не учитывается).

 

ШАГ 2. Создание таблиц

Настройка интерфейса

Перед тем как начать делать первую базу, настройте интерфейс так, чтобы работать было удобно.

Пересечение окон или вкладки. Попробуйте, как вам больше нравится.

Здесь можно настроить заголовок приложения и изменить значок.

 

В текстовых полях стандартное количество символов установлено максимально возможным (255), это, пожалуй, многовато для таких полей как фамилия, телефон и адрес, предлагаю сразу изменить эту настройку.

 

Настройка количества символов в текстовом поле таблицы по умолчанию

Настройка работы макросов!!!!

Для того, чтобы ваши макросы (а они будут обязательно) работали, необходимо включить их в настройках безопасности. Это можно сделать в любой момент, тогда, когда вы напишите свой первый макрос (процедуру обработки события).

Структура данных

Теперь можно приступать к работе. Но сначала немного подумаем и порисуем.

Исходя из постановки задачи, разработаем структуру данных для хранения информации.

Для того, чтобы знать когда, кто, что, сколько и по какой цене покупал необходимо эту информацию сохранять. Информация хранится в таблицах. Количество таблиц определяется необходимостью. Подробно о том, как разрабатывать структуру базы данных можно посмотреть в литературе или в Интернете по адресу http://www.firststeps.ru/msoffice/access/

 

Сначала нарисуем информационную модель.

 

Стрелки показывают, что таблицы должны быть связаны между собой. Для печати чека придется взять информацию из всех таблиц базы данных.   Клиент с товаром связан через продажи.

 

Главная задача СУБД - УЧЕТ ПОКУПОК клиентов (продажи), в которой фиксируются:

КЛИЕНТ

ДАТА ПОКУПКИ

ТОВАР

КОЛИЧЕСТВО

ЦЕНА

НДС

СУММА

 

Необходимо хранить информацию о клиентах

 

ФАМИЛИЯ

АДРЕС

ТЕЛЕФОН

 

Информация о товарах должна быть в базе для предоставления прайса клиентам.

 

НАЗВАНИЕ

ЦЕНА

 

Хранить информацию в одной таблице невыгодно по двум основным причинам:

1. Информация о клиенте содержит три поля, и вводить их для каждой покупки не рационально. Лучше всего будет выбирать клиента по его фамилии (или по другому признаку), а в таблице учета запоминать только его порядковый номер.

2. Клиент может купить несколько товаров, и лучше запоминать дату и клиента один раз при совершении покупки, а не для каждого товара (кроме того, в более сложном случае здесь могут присутствовать данные о типе валюты, виде оплаты (нал., кредит или безнал.) и др. информация). Поэтому УЧЕТ следует вести в двух таблицах: (КЛИЕНТ, ДАТА, НОМЕР) и (НОМЕР, ТОВАР, КОЛИЧЕСТВО, ЦЕНА, НДС), сумму в данном случае хранить не следует, ее можно будет всегда посчитать.

Простое сравнение таблиц может прояснить ситуацию.

Одна таблица покупок будет иметь вид

 

Дата	Счет	Фамилия	Адрес	телефон	Название	Кол-во	Цена	Ставка НДС
08.03.2006		Петров		333-33-33	Телевизор	1,00	10000,00	0,18
08.12.2006	123/2	Петров		333-33-33	Телевизор	10,00	180000,00	0,18
08.12.2006		Петров		333-33-33	Холодильник	10,00	20000,00	0,10
08.12.2006		Иванов		222-22-22	Телевизор	1,00	10000,00	0,18
08.12.2006		Иванов		222-22-22	Холодильник	2,00	20000,00	0,18

 

Больше всего записей в базе данных будет в таблице покупок товаров, поэтому надо стремиться к сокращению числа полей в этой таблице.

 

Если при фиксации покупки запоминать номер клиента вместо фамилии, адреса и телефона, и номер товара вместо названия, то запись покупок можно будет делать сразу в две таблицы вида:

Счета КодСчета КодКлиента НомерСчета Дата 123/2 08.12.2006 08.03.2006 08.12.2006 08.12.2006

ТоварыПоСчету Код КодСчета КодТовара Кол-во Цена Ставка НДС 10,00 180000,00 0,18 2,00 20000,00 0,18 1,00 10000,00 0,18 1,00 10000,00 0,18 10,00 20000,00 0,10

Разобьем таблицу учета счетов на две таблицы и получим структуру базы в виде четырех таблиц. Поскольку мы решили в таблице учета хранить не клиентов, а их номера, а в таблице покупок – номера товаров, вместо названий, то в соответствующие таблицы также надо ввести номера.

В таблицу счетов запоминаем только код клиента, номер счета и дату.

В таблице товаров по счету должна присутствовать информация о том, к какому счету относится запись о покупке конкретного товара, поэтому для связи добавим в таблицу счетов еще одно поле “Код Счета”, и сделаем его ключевым.

Получилось 4 таблицы КЛИЕНТЫ, ТОВАРЫ, СЧЕТА и ТоварыПоСчету.

 

Создание таблиц.

 

Откроем Access и создадим новую базу данных.

В открывшемся окне выберем пункт меню Создание, затем выберем Создание таблицы в режиме конструктора

 

Таблица в конструкторе выглядит так

Наши действия:

· задаем названия полей (колонок)

· указываем их типы (какие данные могут содержаться в колонке)

· задаем значение по умолчанию (чтобы пользователю потом было поменьше работы)

· задаем подписи (чтобы нам было поменьше работы при разработки интерфейса)

· определяем ключевой поле (обычно это поле, которое потом будет определять связь).

Теория: Обычно в современных реляционных БД допускается хранение символьных, числовых данных, битовых строк, специализированных числовых данных (таких как "деньги"), а также специальных "темпоральных" данных (дата, время, временной интервал). Типы данных объеденены в группы. В Access это:

Значение	Тип данных	Размер
Текстовый	Текст или числа, не требующие проведения расчетов, например номера телефонов.	Число знаков, не превышающее минимальное из двух значений: 255 или значение свойства
Поле МЕМО	Очень длинный текст или комбинация текста и чисел.	До 65 535 знаков
Числовой	Числовые данные, используемые для проведения расчетов. Байт; Действительное; Целое; Длинное целое; Одинарное с плавающей точкой; Двойное с плавающей точкой; Код репликации	1, 2, 4 или 8 байт (16 байт только если для свойства Размер поля (FieldSize) задано значение «Код репликации»).
Дата/время	Даты и время, относящиеся к годам с 100 по 9999.	8 байт.
Денежный	Денежные значения и числовые данные (от одного до четырех знаков в дробной части), используемые в математических расчетах, проводящихся с точностью до 15 знаков в целой и до 4 знаков в дробной части.	8 байт.
Счетчик	Уникальные последовательно возрастающие (на 1) или случайные числа, автоматически вводящиеся при добавлении каждой новой записи в таблицу. Значения полей типа счетчика обновлять нельзя.	4 байта
Логический	Значения «Да» и «Нет», а также поля, содержащие только одно из двух возможных значений (Да/Нет, True/False или Вкл/Выкл).	1 бит.
Поле объекта OLE	Объект (например, электронная таблица Microsoft Excel, документ Microsoft Word, рисунок, звукозапись или другие данные в двоичном формате), связанный или внедренный в таблицу Microsoft Access.	До 1 Гбайт (ограничивается объемом диска).
Гиперссылка	Текст или комбинация текста и чисел, хранимые как текст и используемые в качестве адреса гиперссылки. Адрес гиперссылки может состоять максимум из трех частей: текст — текст, выводимый в поле или в элементе управления; адрес — путь к файлу (в формате пути UNC) или странице (URL); дополнительный адрес — расположение внутри файла или страницы; подсказка — текст, отображаемый в виде всплывающей подсказки;	Длина каждой из трех частей гиперссылки не более 2048 знаков.
Мастер подстановок	Создает поле, позволяющее выбрать значение из другой таблицы или из списка значений с помощью списка или поля со списком. Выбор этого параметра запускает мастер подстановок, создающий Поле подстановок. После завершения работы мастера устанавливается тип данных, основанный на значениях, выбранных в мастере.	Тот же размер, что и у первичного ключа поля, используемого в подстановке, обычно 4 байта.

 

Создание таблицы КЛИЕНТЫ

 

 

Про типы полей я не буду особо распространяться, скажу только, что для номеров записей, значения которых нам не нужны (они необходимы только для связи с другими таблицами, а мы зачастую их даже не видим) лучше всего подходит тип СЧЕТЧИК, это избавляет пользователя от необходимости следить за уникальностью поля и даже не думать о его существовании.

Что тут важно знать:

1. Имя поля. Лучше всего его писать латинскими буквами без пробелов. Иначе могут быть проблемы в дальнейших действиях. Ни при каких обстоятельствах не ставьте тире и скобки (тире – это знак минус, а скобки – вызов функции). Два пробела внутри имени может привести в тупик при построении формы.

2. Размер поля. Для текстового поля – количество символов в строке (до 255), для числового поля выбираем либо длинное целое (для целых) или двойное с плавающей точкой для всех остальных (ну их, эти денежные форматы).

3. Формат поля. Для чисел выбираем фиксированный формат (там 2 знака после запятой). Для даты – краткий формат даты (удобнее всего вводить).

4. Значение по умолчанию. К этому свойству надо относиться с вниманием. Для чисел уже обычно стоит ноль. Тут все зависит от таблицы. Желательно, чтобы все поля таблицы имели значения (не были бы пустыми).

- Если это связанное поле, то значение по умолчанию лучше убрать. Это поле должно иметь определенное значение из родительской таблицы и его необходимо формировать, а не ставить какой-то там ноль.

- Для даты можно поставить текущую дату Date().

- Для текстовых полей можно оставить пусто

5. Комментарии нужны для того, чтобы не забыть, зачем в таблице эти поля.

6. Подпись. Вот здесь можно писать по-русски и с пробелами, скобками, тире. Это тот заголовок, который вы увидите в таблице, запросе, в форме и отчете. Например В поле кодКлиента подпись КЛИЕНТ, а в поле КЛИЕНТ - подпись ФАМИЛИЯ.

Ключевое поле

Для задания ключевого поля нужно в режиме конструктора щелкнуть правой кнопкой по имени поля и выбрать “ключевое поле”.

 

Закроем таблицу, сохранив ее под именем КЛИЕНТЫ.

 

Таблица ТОВАРЫ

предназначена для хранения списка товаров и их текущих цен.

В этой таблице 4 поля:

Код товара - cчетчик,

название – текстовое поле,

цена - числовое – двойное с плавающей точкой , фиксированный формат с 2 знаками,

ставка НДС - числовое – одинарное с плавающей точкой , фиксированный формат с 2 знаками .

Код товара – счетчик, ключевое поле.

 

Таблица СЧЕТА

предназначена для фиксации факта покупки клиента, хранит уникальный номер записи (КодСчета), номер чека(счета), код клиента и дату покупки.

Таблица ТоварыПоСчету

хранит список товаров, купленных клиентами. Хранению подлежат: кодСчета (чтобы можно было найти в таблице СЧЕТА когда и кто купил этот товар), количество товара, цена(поскольку цена товара в прайсе со временем изменяется, в этом поле хранится реальная цена, по которой был куплен товар), ставка НДС (она тоже меняется, и зависит от товара. Размеры полей выбираем следующие:

код счета, код товара, количество – длинное целое

цена продажи – двойное с плавающей точкой,

НДС– одинарное с плавающей точкой.

Значения по умолчанию везде убираем.

Свойство “Обязательное поле” выставим всем полям “ДА “.

 

Структура таблиц готова.

Связывание таблиц

 

Теперь таблицы надо связать между собой. Зачем? Во-первых, нужно следить за целостностью базы данных, не должно быть в таблице счетов номеров несуществующих клиентов, и в таблице товаров – номеров несуществующих товаров. За этим может следить сама СУБД, если только мы ей скажем об этом. Во-вторых, поставив связь один раз в схеме данных, мы навсегда избавимся от необходимости устанавливать связи в запросах к нескольким таблицам. Итак – вперед.

 

Выбираем иконку , щелкаем правой кнопкой мыши внутри открывшегося пустого окна и добавляем все таблицы, по очереди. Мышкой выбираем нужное поле одной таблицы (КЛЮЧЕВОЙ ПОЛЕ) и тянем его на поле другой таблицы (только не наоборот).

В открывшемся окне связей устанавливаем целостность. Выбираем ОБЕСПЕЧЕНИЕ ЦЕЛСТНОСТИ и КАСКАДНОЕ ОБНОВЛЕНИЕ. Проверьте, как правило, внизу должно быть отношение ОДИН КО МНОГИМ (если это не так, вы сделали на вторичной стороне счетчик, а там должно быть ЧИСЛО ДЛИННОЕ ЦЕЛОЕ).

Берем поле КодКлиента из таблицы КЛИЕНТЫ и тянем его в таблицу СЧЕТА на поле КодКлиента. Берем поле кодТовары в таблице ТОВАРЫ и тянем его в таблицу ТоварыПоСчету на поле кодТовара. Совсем не обязательно называть связываемые поля в таблицах одинаковыми именами, для проектирования это может быть и удобно, но в запросах потом приходится писать много дополнительных слов. Кто как привык.

 

После того, как все связи будут установлены получиться такая схема данных.