Создание базы данных ACCESS 2003, страница 2

Рис 42. Кнопка - Цены детали.

Кнопка «Радиостанция» - служит для ввода данных о комплектной радиостанции.

Рис 43. Кнопка - Радиостанция.

ЗАПРОСЫ

Рис 44. Вкладка «Запрос».

Тексты запросов:

1)  Выдать списки отсортированные:

1.1 в алфавитном порядке

---------------------------------------------------------

SELECT DETAILS.detail_name, DETAILS.detail_code, DETAILREPAIR.*

FROM DETAILS INNER JOIN DETAILREPAIR ON DETAILS.detail_id=DETAILREPAIR.detail_id

ORDER BY DETAILS.detail_name;

 ---------------------------------------------------------

1.2 по причине отказа

---------------------------------------------------------

            SELECT DETAILS.detail_name, DETAILS.detail_code, DETAILREPAIR.*

            FROM DETAILS INNER JOIN DETAILREPAIR ON DETAILS.detail_id = DETAILREPAIR.detail_id

 ORDER BY rto_brokenreason2;

 ---------------------------------------------------------

1.3 по дате выпуска

---------------------------------------------------------

SELECT DETAILS.detail_name, DETAILS.detail_code, DETAILREPAIR.*

FROM DETAILS INNER JOIN DETAILREPAIR ON DETAILS.detail_id=DETAILREPAIR.detail_id

ORDER BY DETAILREPAIR.rto_begindate;

 ---------------------------------------------------------

1.4 по цене

---------------------------------------------------------

SELECT DETAILS.detail_name, DETAILS.detail_code, DETAILREPAIR.*, DETAILS_PRICE.price

FROM (DETAILS INNER JOIN DETAILREPAIR ON DETAILS.detail_id = DETAILREPAIR.detail_id) INNER JOIN DETAILS_PRICE ON DETAILS.detail_id = DETAILS_PRICE.detail_id

ORDER BY DETAILS_PRICE.price;

 ---------------------------------------------------------

1.5 по дате расконсервации

---------------------------------------------------------

SELECT DETAILS.detail_name, DETAILS.detail_code, DETAILREPAIR.*

FROM DETAILS INNER JOIN DETAILREPAIR ON DETAILS.detail_id = DETAILREPAIR.detail_id

ORDER BY DETAILREPAIR.rto_rkonsdate;

 ---------------------------------------------------------

2)  Найти:

2.1 самый дорогой блок

---------------------------------------------------------

SELECT DETAILS.detail_name, DETAILS.detail_name, DETAILS_PRICE.price

FROM DETAILS INNER JOIN DETAILS_PRICE ON DETAILS.detail_id=DETAILS_PRICE.detail_id

WHERE DETAILS_PRICE.price =  (

SELECT MAX(DETAILS_PRICE.price)

FROM DETAILS INNER JOIN DETAILS_PRICE ON DETAILS.detail_id = DETAILS_PRICE.detail_id

      );

---------------------------------------------------------

2.2 самый дешевый

---------------------------------------------------------

SELECT DETAILS.detail_name, DETAILS.detail_name, DETAILS_PRICE.price

FROM DETAILS INNER JOIN DETAILS_PRICE ON DETAILS.detail_id=DETAILS_PRICE.detail_id

WHERE DETAILS_PRICE.price =  (

SELECT MIN(DETAILS_PRICE.price)

FROM DETAILS INNER JOIN DETAILS_PRICE ON DETAILS.detail_id = DETAILS_PRICE.detail_id );

---------------------------------------------------------

2.3 среднюю стоимость

---------------------------------------------------------

SELECT AVG(DETAILS_PRICE.price)

FROM DETAILS_PRICE;

---------------------------------------------------------

3)  Для заданной организации найти все изделия, всех виновников отказа

---------------------------------------------------------

SELECT DETAILREPAIR.rto_exporgname, DETAILS.detail_name, DETAILS.detail_code, DETAILREPAIR.rto_brokenworker

FROM DETAILS INNER JOIN DETAILREPAIR ON DETAILS.detail_id=DETAILREPAIR.detail_id

WHERE (((DETAILREPAIR.rto_exporgname) Like '*'+Forms!ФормаЗапросов!Запрос3Наименование+'*'));

---------------------------------------------------------

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

---------------------------------------------------------