Сложно ли написать примитивный складской учет на Access неподготовленному человеку? Почти невозможно. Тем не менее, подобная задача зачастую дается студентам младших курсов ВУЗов. Они, как правило, не знакомы ни с Access, ни с реляционной моделью данных вообще. А почему они не знакомы, могут спросить?

А потому что в среде программистов считается, что Access себя изжил и в большинстве случаев программисты обходятся без него. Тут дело вот в чем. Как БД, Access в настоящее время представляет интерес, в основном, как средство хранения информации в WEB на некоммерческих сайтах. В силу ограниченности ресурсов на WEB серверах и далеко не везде распространенной поддержки хостингами SQL Server'ов. В силу того, что файл БД Access просто хранить и администрировать.
На «земле», т.е. в интрасетях, он не в состоянии составить хоть сколько нибудь заметной конкуренции SQL серверам по производительности и по возможностям серьезной работы с информацией.

Но у Access есть еще одно бесспорное преимущество – это отличный инструмент для мгновенных разработок и для первых шагов в программировании.
Хотя, в последнее время, современное программирование уже настолько далеко отстоит от Access, что говорить о практической пользе получаемых при общении с ним навыков не приходится.
Разве только изучить VBA? Но кому это надо, если весь мир уже давно перешел на .NET? Так значит, Access таки мертв?
Надеюсь, данная статья поможет доказать обратное. Однако, перейдем к делу. Итак, нам нужны покупатели, поставщики, товары, продажи и поставки товаров.
Данное приложение не предполагает редактирования и ввод данных. Таблицы мы заполнили руками. Кому интересно, могут самостоятельно дополнить приложение формами ввода и редактирования данных.
Это было бы неплохим упражнением. В качестве отчетности изготовим для преподавателя два отчета: отчет по приходам на склад и отчет по продажам со склада.

Выглядеть, в итоге, это будет так:


И второй отчет: 



Начнем с составления таблиц нашей базы данных (БД). Судя по виду отчетов, может показаться, что нам нужны таблицы:
поставщики, покупатели, поставки, продажи, группы товаров, подгруппы товаров, типы товаров и, собственно, товары.
Мда, чего-то многовато получается для еще не окрепшего студенческого мозга.
Попробуем обойтись малой кровью и создадим всего три таблицы:
tb_clients, tb_goods и tb_warehouse.
Поверьте, этого вполне достаточно. Структуру таблиц вам придется набивать ручками. А вот данные лучше копипастить. Пожалейте маникюр и клавиатуру. Когда вы еще сможете купить себе новую?



Итак, первая из наших таблиц, таблица tb_goods хранит информацию по товарам.
Мы реализуем в нашем случае иерархическую модель хранения товаров, проще говоря, дерево, в одной таблице.
Это значительно увеличивает потребительскую пригодность всего приложения и незначительно увеличивает нагрузку на мозг разработчика.
Существует много споров на тему, как лучше реализовывать дерево. В одной таблице или в нескольких. Остановимся на одной.
Также существует много споров на тему, как его лучше реализовать в одной таблице – с одним дополнительным полем id_parent или с id_parent и несколькими дополнительными полями, left и right, позволяющими реализовать более гибкую структуру с неограниченной вложенностью ветвей, так называемую Nested Sets модель дерева.
Интересующихся отправим сюда: Nested Set. Там они почерпнут много интересного и, может даже, решат стать программистами, если не застрелятся сразу.
Мы же пойдем другим путем и не станем изобретать велосипед, поскольку наша задача сдать курсовую, а не свихнуться.
Нас устроит вложенность в четыре уровня, поскольку для учебной задачи непрофильного вуза этого более чем достаточно. Итак, структура таблички tb_goods приведена на рисунке выше. А вот и ее данные:  

id_good	id_parent	id_manufacturer	good_name
1	11	6	Escort
2	16	7	Докторская
3	10	5	ThinkPad
4	12	19	Cосиски Баварские
5	9	8	"Золотой фазан"
6	9	8	"Великопоповецкий Козел"
7	10	5	Мышь
8	11	6	Tranzit
9	19		Пиво
10			Компьютерное оборудование
11			Автомобили
12			Продукты питания
13	11	10	Primera
14	11	10	Pathfinder
15	11	10	Patrool
16	12		Колбаса
17	12	11	Сыр "Viola"
18	19		Вино
19			Алкоголь
20	16	7	Останкинская
21	18		Шампанское
22	19		Коньяк
23	19		Водка
24	21	13	Советское полусладкое
25	21	13	Брют
26	22	12	Арарат
27	22	12	Ахтамар
28	22	14	Дагестанский 3*
29	23	15	Русский Стандарт 0,5
30	23	15	Дипломат 1,0
31	23	16	Путинка
32	10	9	Aspire 5720
33	10	9	ЖК монитор 2216
34	0	12	Масло "Тысяча озер"

Что тут хочется заметить? Во-первых, поле id_good это счетчик, во-вторых, поле id_parent содержит ключик, указывающий на товар, к которому принадлежит данный товар.
Таким образом, можно реализовать неограниченную вложенность веток. Но. Мы споткнемся на вытаскивании информации.
Структура запросов получится довольно громоздкой и, при больших объемах хранимой информации, соответственно медленной.
Строки верхнего уровня, корни, не имеют в поле id_parent никакого значения, точнее имеют значение NULL. Наш выбор – 4 уровня вложенности.
Это видно по ветке: Алкоголь, Вино, Шампанское, Советское полусладкое.
Назначение остальных полей несложно понять из комментариев к полям на рисунке со структурой табличек.
С таблицей tb_clients все ясно. Она простая. Тонкость в том, что в ней хранятся данные как о поставщиках, так и о покупателях.
Чем же они отличаются друг от друга? Да ничем. Все отличия проявляются в третьей табличке tb_warehouse, хранящей строчки о совершенных по складу операциях.
Там точно так же в комментариях описано назначение всех полей. Обратить внимание стоит на поле operation_type.
Оно то и определяет, кем является субъект из таблички tb_clients по отношению к складу в текущей транзакции – покупателем или поставщиком.
Итак, структура таблички tb_warehouse приведена на рисунке выше.
Вот ее данные:

id_operation	id_client	operation_type	id_good	operation_date	delivery_count	delivery_price
1	12	0	26	31.12.2007	100	235
2	9	0	32	22.10.2007	20	30000
3	5	0	3	11.09.2001	3	55000
4	5	0	7	30.11.2007	150	300
5	9	0	33	22.10.2007	25	10000
6	6	0	8	24.07.2007	5	800000
7	6	0	1	20.08.2007	5	400000
8	7	0	2	20.08.2007	500	100
9	19	0	4	20.03.2007	500	120
10	8	0	5	20.02.2007	11000	20
11	8	0	6	24.05.2007	6000	21
12	10	0	13	27.06.2007	10	600000
13	10	0	14	04.07.2007	10	700000
14	10	0	15	08.09.2007	10	650000
15	11	0	17	10.10.2007	1000	55
16	11	0	34	10.01.2007	500	185
17	12	0	26	20.01.2007	300	100
18	12	0	27	20.02.2007	360	400
19	13	0	24	31.12.2007	50	1000
20	13	0	25	31.12.2007	55	1000
21	14	0	28	02.12.2007	100	150
22	15	0	29	04.12.2007	200	180
23	15	0	30	08.12.2007	200	180
24	16	0	31	12.12.2007	120	130
25	1	1	31	31.12.2007	2	150
26	1	1	24	31.12.2007	3	80
27	1	1	20	31.12.2007	1	160
28	1	1	6	31.12.2007	2	150
29	1	1	7	31.12.2007	1	330
30	2	1	34	31.12.2007	0,5	230
31	2	1	27	31.12.2007	1	500
32	2	1	25	31.12.2007	1	70
33	2	1	4	31.12.2007	1	150
34	17	1	3	31.12.2007	1	65000
35	18	1	14	20.08.2007	1	120000

Ну и, наконец, приведем данные таблицы tb_clients.


 

tb_clients
id_client	client_name	client_address
1	Ваня	Выборг
2	Света	Петербург
5	IBM	USA
6	Ford Motors	Germany
7	Мясо-молочный комбинат №1	Россия
8	Пивоваренная компания	Чехия
9	Acer	Австрия
10	Nissan	Japan
11	Valio	Финляндия
12	Армен-Алко	Армения
13	Завод шампанских вин	Санкт-Петербург
14	Дагвино	Махачкала
15	Ливиз	Санкт-Петербург
16	Кристалл	Москва
17	John	New-York
18	Петр Семенович Лопухов	Мухосранск
19	"Пит-Продукт"	Москва

С таблицами все.
Теперь самое сложное. Нам надо составить четыре запроса.
В целях уменьшения количества свалившегося на нашу голову геморроя и прогрессирующего в массах маразма, я приведу запросы в SQL представлении.
Дело в том, что так они выглядят информационно полнее и намного компактней, нежели в конструкторе Access, который прячет от глаз так необходимые нашему пытливому уму детали.
Если кому-то покажется, что разобраться с этим нереально, то это они зря.
Просто скопируйте текст запроса в соответствующее SQL представление построителя запросов и потом в нем же переключитесь в режим конструктора. Тогда все станет намного понятней.

Первый, самый простой, запрос Корни выглядит так:
SELECT tb_goods.id_good, tb_goods.good_name FROM tb_goods WHERE (((tb_goods.id_parent) Is Null));

И возвращает следующие данные:

Корни
id_good	good_name
10	Компьютерное оборудование
11	Автомобили
12	Продукты питания
19	Алкоголь

Второй запрос Сборный_по товарам несколько сложнее по структуре.
Его задача развернуть дерево товаров в вид, пригодный для табличного представления.
Вот его текст:
SELECT IIf([id_3] Is Not Null,[id_3],IIf([id_2] Is Not Null,[id_2],IIf([id_1] Is Not Null,[id_1],[id_0]))) AS id_good, Корни.id_good AS id_0,
Корни.good_name AS name_0, G1.id_good AS id_1, G1.good_name AS name_1, G2.id_good AS id_2, G2.good_name AS name_2,
G3.id_good AS id_3, G3.good_name AS name_3, IIf([id_3] Is Not Null,[name_2] & " / " & [name_3],IIf([id_2] Is Not Null,[name_1] & " / "
& [name_2],IIf([id_1] Is Not Null,[name_0] & " / " & [name_1]))) AS good_name_complex
FROM (((Корни INNER JOIN tb_goods AS G0 ON Корни.id_good = G0.id_good)
INNER JOIN tb_goods AS G1 ON G0.id_good = G1.id_parent)
LEFT JOIN tb_goods AS G2 ON G1.id_good = G2.id_parent) LEFT JOIN
tb_goods AS G3 ON G2.id_good = G3.id_parent;

Читается тяжело, согласен. Зато посмотрите, как с его помощью легко теперь читаются записи таблицы tb_goods:

Сборный_по товарам
id_good	id_0	name_0	id_1	name_1	id_2	name_2	id_3	name_3	good_name_complex
7	10	Компьютерное оборудование	7	Мышь					Компьютерное оборудование / Мышь
3	10	Компьютерное оборудование	3	ThinkPad					Компьютерное оборудование / ThinkPad
32	10	Компьютерное оборудование	32	Aspire 5720					Компьютерное оборудование / Aspire 5720
33	10	Компьютерное оборудование	33	ЖК монитор 2216					Компьютерное оборудование / ЖК монитор 2216
1	11	Автомобили	1	Escort					Автомобили / Escort
13	11	Автомобили	13	Primera					Автомобили / Primera
14	11	Автомобили	14	Pathfinder					Автомобили / Pathfinder
15	11	Автомобили	15	Patrool					Автомобили / Patrool
8	11	Автомобили	8	Tranzit					Автомобили / Tranzit
4	12	Продукты питания	4	Cосиски Баварские					Продукты питания / Cосиски Баварские
17	12	Продукты питания	17	Сыр "Viola"					Продукты питания / Сыр "Viola"
20	12	Продукты питания	16	Колбаса	20	Останкинская			Колбаса / Останкинская
2	12	Продукты питания	16	Колбаса	2	Докторская			Колбаса / Докторская
5	19	Алкоголь	9	Пиво	5	"Золотой фазан"			Пиво / "Золотой фазан"
6	19	Алкоголь	9	Пиво	6	"Великопоповецкий Козел"			Пиво / "Великопоповецкий Козел"
28	19	Алкоголь	22	Коньяк	28	Дагестанский 3*			Коньяк / Дагестанский 3*
26	19	Алкоголь	22	Коньяк	26	Арарат			Коньяк / Арарат
27	19	Алкоголь	22	Коньяк	27	Ахтамар			Коньяк / Ахтамар
29	19	Алкоголь	23	Водка	29	Русский Стандарт 0,5			Водка / Русский Стандарт 0,5
30	19	Алкоголь	23	Водка	30	Дипломат 1,0			Водка / Дипломат 1,0
31	19	Алкоголь	23	Водка	31	Путинка			Водка / Путинка
24	19	Алкоголь	18	Вино	21	Шампанское	24	Советское полусладкое	Шампанское / Советское полусладкое
25	19	Алкоголь	18	Вино	21	Шампанское	25	Брют	Шампанское / Брют

Оставим на моей совести вопросы оптимизации его выполнения. Это нам не SQL Server в продакшене корпорации. И записей в нашем приложении я насчитал не так много.
Так что, помним, други мои, что наша цель – сдать курсовую. Поэтому поехали дальше. Итак, самое сложное уже позади.
Теперь нам надо слепить совершенно несложный запрос по продажам и такой же по поставкам продукции на наш склад.
Вот текст запроса Продажи по складу:

SELECT tb_clients.client_name, [Сборный_по товарам].good_name_complex, tb_warehouse.delivery_price AS Цена, tb_warehouse.delivery_count AS Количество,
tb_clients_1.client_name & "/" & tb_clients_1.client_address AS Поставщик
FROM (((tb_clients INNER JOIN tb_warehouse ON tb_clients.id_client = tb_w arehouse.id_client)
INNER JOIN [Сборный_по товарам] ON tb_warehouse.id_good = [Сборный_по товарам].id_good)
INNER JOIN tb_goods ON [Сборный_по товарам].id_good = tb_goods.id_good)
INNER JOIN tb_clients AS tb_clients_1 ON tb_goods.id_manufacturer = tb_clients_1.id_client
WHERE (((tb_warehouse.operation_type)=1))
ORDER BY tb_clients.client_name;

Он вернет нам примерно следующее:

Продажи по складу
client_name	good_name_complex	Цена	Количество	Поставщик
John	Компьютерное оборудование / ThinkPad	65000	1	IBM/USA
Ваня	Компьютерное оборудование / Мышь	330	1	IBM/USA
Ваня	Колбаса / Останкинская	160	1	Мясо-молочный комбинат №1/Россия
Ваня	Пиво / "Великопоповецкий Козел"	150	2	Пивоваренная компания/Чехия
Ваня	Шампанское / Советское полусладкое	80	3	Завод шампанских вин/Санкт-Петербург
Ваня	Водка / Путинка	150	2	Кристалл/Москва
Петр Семенович Лопухов	Автомобили / Pathfinder	120000	1	Nissan/Japan
Света	Коньяк / Ахтамар	500	1	Армен-Алко/Армения
Света	Шампанское / Брют	70	1	Завод шампанских вин/Санкт-Петербург
Света	Продукты питания / Cосиски Баварские	150	1	"Пит-Продукт"/Москва

А вот текст последнего запроса Поставки на склад:

SELECT [Сборный_по товарам].name_0, tb_clients.client_name, [Сборный_по товарам].good_name_complex, tb_warehouse.delivery_price AS Цена,
tb_warehouse.delivery_count AS Количество, tb_clients_1.client_name & "/" & tb_clients_1.client_address AS Поставщик
FROM (((tb_clients INNER JOIN tb_warehouse ON tb_clients.id_client = tb_warehouse.id_client)
INNER JOIN [Сборный_по товарам] ON tb_warehouse.id_good = [Сборный_по товарам].id_good)
INNER JOIN tb_goods ON [Сборный_по товарам].id_good = tb_goods.id_good)
INNER JOIN tb_clients AS tb_clients_1 ON tb_goods.id_manufacturer = tb_clients_1.id_client
WHERE (((tb_warehouse.operation_type)=0))
ORDER BY [Сборный_по товарам].name_0, tb_clients.client_name;

Это возвращаемые им данные:

Поставки на склад
name_0	client_name	good_name_complex	Цена	Количество	Поставщик
Автомобили	Ford Motors	Автомобили / Tranzit	800000	5	Ford Motors/Germany
Автомобили	Ford Motors	Автомобили / Escort	400000	5	Ford Motors/Germany
Автомобили	Nissan	Автомобили / Patrool	650000	10	Nissan/Japan
Автомобили	Nissan	Автомобили / Primera	600000	10	Nissan/Japan
Автомобили	Nissan	Автомобили / Pathfinder	700000	10	Nissan/Japan
Алкоголь	Армен-Алко	Коньяк / Ахтамар	400	360	Армен-Алко/Армения
Алкоголь	Армен-Алко	Коньяк / Арарат	100	300	Армен-Алко/Армения
Алкоголь	Армен-Алко	Коньяк / Арарат	235	100	Армен-Алко/Армения
Алкоголь	Дагвино	Коньяк / Дагестанский 3*	150	100	Дагвино/Махачкала
Алкоголь	Завод шампанских вин	Шампанское / Советское полусладкое	1000	50	Завод шампанских вин/Санкт-Петербург
Алкоголь	Завод шампанских вин	Шампанское / Брют	1000	55	Завод шампанских вин/Санкт-Петербург
Алкоголь	Кристалл	Водка / Путинка	130	120	Кристалл/Москва
Алкоголь	Ливиз	Водка / Русский Стандарт 0,5	180	200	Ливиз/Санкт-Петербург
Алкоголь	Ливиз	Водка / Дипломат 1,0	180	200	Ливиз/Санкт-Петербург
Алкоголь	Пивоваренная компания	Пиво / "Великопоповецкий Козел"	21	6000	Пивоваренная компания/Чехия
Алкоголь	Пивоваренная компания	Пиво / "Золотой фазан"	20	11000	Пивоваренная компания/Чехия
Компьютерное оборудование	Acer	Компьютерное оборудование / ЖК монитор 2216	10000	25	Acer/Австрия
Компьютерное оборудование	Acer	Компьютерное оборудование / Aspire 5720	30000	20	Acer/Австрия
Компьютерное оборудование	IBM	Компьютерное оборудование / Мышь	300	150	IBM/USA
Компьютерное оборудование	IBM	Компьютерное оборудование / ThinkPad	55000	3	IBM/USA
Продукты питания	"Пит-Продукт"	Продукты питания / Cосиски Баварские	120	500	&quo t;Пит-Продукт"/Москва
Продукты питания	Valio	Продукты питания / Сыр "Viola"	55	1000	Valio/Финляндия
Продукты питания	Мясо-молочный комбинат №1	Колбаса / Докторская	100	500	Мясо-молочный комбинат №1/Россия

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




Группировка включена по полям name_0 и client_name.
Примечание группы client_name содержит итоговые поля отчета.
Поле с информацией по клиенту: ="Итого " & [client_name] & ": (" & Count(*) & " " & IIf(Count(*)=1;"поз.";"поз.")& ")".
Поле с суммой: = Sum([Цена])
Примечание группы name_0 также содержит 2 итоговых поля отчета.
Данные в них такие:
="Итого по группе " & [name_0] & ": (" & Count(*) & " " & IIf(Count(*)=1;"поз.";"поз.")& ")"
и
= Sum([Цена])
Второй отчет Продажи по складу устроен аналогично и даже проще, т.к. в нем на один уровень группировки меньше.
Источником данных для него является запрос Продажи по складу.

 

Итоговые поля в примечании группы client_name:
="Итого " & [client_name] & ": (" & Count(*) & " " & IIf(Count(*)=1;"поз.";"поз.") & ")"
и такое же, как в предыдущем отчете, поле суммы:
=Sum([Цена])
Одна маленькая, но не очевидная деталь. В отчетах присутствует колонка № п/п, содержащая счетчик строк.
Реализуется это вставкой в это поле в области данных отчета значения: =1 и включением суммы с накоплением для этого поля для группы.
Вот, собственно, и все, что требовалось сказать по этому поводу, уважаемые мои.

(С)Евгений Трифонов, aka p.v.
pterodactilus@rambler.ru
г.Питер, 2007