Блог

Главная - Вопросы по программированию - Полное внешнее соединение в доступе при подсчете элементов

Полное внешнее соединение в доступе при подсчете элементов

#sql #ms-access #ms-access-2013

#sql #ms-доступ #ms-access-2013

Вопрос:

Я пытаюсь создать полное внешнее соединение в Access, подсчитывая элементы и получая процент.

Вот мой код:

 SELECT Main.Draft, Count(Main.Draft) AS MainCount, (Count([Main.Draft])/(Select Count(Main.Draft) from Main)) AS Percentage
FROM Main LEFT JOIN Main_ChangeLog ON Main.ID = Main_ChangeLog.ID
GROUP BY Main.Draft;
UNION ALL 
SELECT Main.Draft, Count(Main.Draft) AS MainCount, (Count([Main.Draft])/(Select Count(Main.Draft) from Main)) AS Percentage
FROM Main RIGHT JOIN Main_ChangeLog ON Main.ID = Main_ChangeLog.ID
WHERE (((Main.Date) Between [Forms]![Main Reports]![txtbegin] And [Forms]![Main Reports]![txtend]))
GROUP BY Main.Draft;

Вот мой результат:

 Version_1   1   0
Version_2   1   0   
Version_3   3   0
Version_1   4   0
Version_2   3   0

Вот результат, который я хочу:

 Version_1   5   0.50
Version_2   4   0.15
Version_3   3   0.35

Вот основная таблица:

 ID  CreateDate  FirstName   LastName   Draft

1   10/01/2020   First       One        Version_2
2   11/20/2020   Second      Person     Version_3
3   11/20/2020   Third       Girl       Version_3
4   11/21/2020   Fourth      Boy        Version_3
5   11/22/2020   Fifth       Guy        Version_1

Вот таблица Main_ChangeLog:

 ID CreateDate    FirstName   LasteName   Draft
1  9/10/2020      First       One       Version_1
2  9/10/2020      Second      Person    Version_1
2  10/10/2020     Second      Person    Version_2
3  9/10/2020      Third       Girl      Version_1
3  10/10/2020     Third       Girl      Version_2
4  9/5/2020       Fourth      Boy       Version_1
4  10/10/2020     Fourth      Boy       Version_2

Каждый раз, когда запись обновляется в основной таблице, предыдущая запись сохраняется в Main_ChangeLog. Это помогает получить истинное представление о том, сколько версий было создано каждый месяц.

Я только что изменил свой код на этот:

   SELECT t.Draft, Count(t.Count) AS DraftCount, (Count(t.Draft)/(Select Count(t.Draft) from Main)) AS [Percentage]
    FROM 
    (SELECT Main.Draft as Draft, Main.Draft as [Count]
    FROM MainLEFT JOIN Main_ChangeLog ON Main.ID = Main_ChangeLog.ID 
    WHERE (((Main.Date) Between [Forms]![Main Reports]![txtbegin] And [Forms]![Main Reports]![txtend]))
    UNION ALL 
    SELECT Main.Draft AS Draft, Main.Draft as [Count]
    FROM MainRIGHT JOIN Main_ChangeLog ON Main.ID = Main_ChangeLog.ID
    WHERE (((Main.Date) Between [Forms]![Main Reports]![txtbegin] And [Forms]![Main Reports]![txtend])))  AS t
    GROUP BY t.Draft

Теперь я получаю эту ошибку:

 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Спасибо.

Комментарии:

1. Пожалуйста, покажите данные в таблицах. Трудно следить за вычислениями. Как вычисляются доли? И опишите логику, которую вы хотите реализовать.

2. union all Сначала выполните и сгруппируйте результаты.

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

4. Как вычисляются желаемые проценты, например, 50% для version_ 1? Я вижу 1/5 или 5/12.

5. Я пытаюсь разделить общее количество выбранного черновика на общее количество всех черновиков.

Ответ №1:

это один из способов сделать это с помощью двух запросов:

  1. Первый запрос (который мы вызовем qryMainData ) объединит все ваши данные о версии вместе, используя UNION ALL :
 SELECT ID, Draft, CreateDate FROM Main
UNION ALL 
SELECT ID, Draft, CreateDate FROM Main_ChangeLog;
  1. второй запрос будет основан на первом, потому что вы хотите использовать общее количество всего результирующего набора запроса 1 ( qryMainData ) для вычисления процентов:
 SELECT Draft, Count(ID) as DraftQty, (SELECT COUNT(ID) FROM qryMainData) as TotalDraftQty,
ROUND(DraftQty/TotalDraftQty, 2) as DraftPercentage
FROM qryMainData
WHERE CreateDate Between [Forms]![Main Reports]![txtbegin] And [Forms]![Main Reports]![txtend]
GROUP BY Draft

MS Access позволяет использовать псевдонимы полей в вычислениях. Я также использовал эту ROUND функцию для округления результатов до двух знаков после запятой.