Зарегистрировано: 318




Помощь  Карта сайта

О чем пишут?

Open Observatory Control System

Дальше..

Я так вижу!

DSC_0299-1.jpg

DSC_0299-1.jpg

Австрийский городок Куфштайн. Самая старая улица - ей около тысячи лет.


Тексты. Прозариум

Тексты на сайте могут публиковаться как в составе книг, по которым они "разложены", так и по отдельности. Тексты можно публиковать на странице их владельца, в блогах, клубах или рубриках сайта, а так же в виде статей и объявлений. Вы можете публиковать на сайте не только собственные тексты, но и те, которыми хотите поделиться с читателями, соблюдая авторские права их владельцев.
Prozarium CMS | Реклама, сотрудничество | Разработка, продажа сайтов

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



Опубликовано в: Клуб: MS Access
<--Программирование
<--IT Информационные технологии
<--Бизнес по сферам деятельности

0





Складской учет на Access. Пример для студентов
/pterodactilus vulgaris/
04.12.2021


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

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

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

Выглядеть, в итоге, это будет так:
складской учет на Access. Приход

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

складской учет на Access. Расход

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

складской учет на Access. Таблицы

Итак, первая из наших таблиц, таблица 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/Россия


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

складской учет на Access. Приход. Конструктор


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

 складской учет на Access. Расход. Конструктор

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

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