Агрегированный запрос в MS Access
Есть ли способ SELECT предотвратить дублирование при использовании функции Sum в MS Access? Я пытался:
SELECT StaffNo, StaffName, Sum([Pay]) As TotalPay
FROM tblHours
WHERE tblHours.Restricted ='Yes'
GROUP BY StaffNo, StaffName;
Но результаты возвращают повторяющиеся строки:
Отредактировано:
tblHours
Ожидал:
Или есть какие-то другие способы выполнить эту задачу?
С уважением
Ваше описание не соответствует данным вашего примера. Пожалуйста, предоставьте образцы данных в виде текста, а не изображения.
Ваш запрос написан так, чтобы делать именно то, что вы хотите. Это ваши данные плохие. Вероятно, в одной из этих записей есть пробелы (или что-то в этом роде). Например, Sam H или W001 , которые делают значение уникальным и приводят к сбою агрегации.
Попробуйте это: «выберите отдельные StaffNo, StaffName из заказа tblHours по Staffno», чтобы увидеть, что вы получите для W001. Если он там дублируется, значит, в этих полях есть какой-то непечатаемый символ или конечный пробел. Каковы типы данных (данные избыточны, вы не используете нормализацию, эта проблема может возникнуть в любое время)
Есть мысли о том, чтобы попытаться обрезать неагрегированные поля, чтобы посмотреть, удалит ли это пробелы и т. д.? SELECT Trim(StaffNo) AS StaffNo, Trim(StaffName) AS StaffName, Sum([Pay]) As TotalPay FROM tblHours WHERE tblHours.Restricted ='Да' GROUP BY TRIM(StaffNo), TRIM(StaffName);
@Gustav Данные верны, таблица имеет автоматический первичный ключ и принимает дубликаты StaffNo. Я исключил столбец с ограниченным доступом в образце изображения, поскольку он содержит значение «Да» для всех записей.
Я вижу, что есть много строк с Sam H., Sam H и Sam H. поэтому дайте мне дубликаты для W001 Есть ли способ обойти это?
@Fil очистите ваши данные и убедитесь, что такого не может произойти. В противном случае у вас всегда будет проблема «дерьмо туда, дерьмо»
Вероятно, лучше всего иметь таблицу поиска персонала и присоединиться к ней с помощью Staffno.
Согласитесь с @VLOOKUP: в tblHours не должно быть имени сотрудника, только идентификатор сотрудника. Присоединяйтесь к таблице персонала, где каждый человек уникален. И, возможно, используйте поле автономера в качестве ключа в Staff и присоединяйтесь к нему вместо текстового поля StaffNo. Кроме того, можно создать отчет и использовать его функции сортировки и группировки с агрегированными вычислениями в текстовом поле. Это позволяет просматривать подробные данные, а также сводные расчеты.
Как я уже говорил, это происходит из-за избыточных данных. Прочтите нормализацию хотя бы до 3-й нормальной формы. На практике в другой таблице (скажем, Staff) у вас должны быть StaffNo и StaffName, где StaffNo является уникальным. В этой таблице у вас должно быть только StaffNo для связи с таблицей Staff.
Ответы 1
В правильно спроектированной реляционной базе данных связь между StaffNo и StaffName хранится в отдельной таблице (называемой, скажем, tblStaff), где StaffNo — это первичный ключ (или уникальный ключ); тогда вы должны использовать соединение, чтобы получить это имя. Этот дизайн предотвращает повторение имени в нескольких формах, как у вас (найдите database normalisation, чтобы узнать об этом больше).
Спросите себя о тех, у кого есть «Сэм Х.». и «Сэм Х.» и «Сэм Х.». какой из них ты хочешь сохранить? если это не имеет значения, вы можете «составить» таблицу персонала, просто взяв одну из них для каждого StaffNo, вот так:
In this example, I use MIN to take just one value of StaffName: the one that comes first when they are alphabetically sorted) for each StaffNo.
SELECT th.StaffNo, Staff.MinStaffName as StaffName, Sum([Pay]) As TotalPay
FROM tblHours th
inner join
(select StaffNo, min(StaffName) as MinStaffName
from tblHours ths
group by StaffNo) as Staff
on th.StaffNo=Staff.StaffNo
WHERE th.Restricted =Yes
GROUP BY th.StaffNo, Staff.MinStaffName
Ужин! @Tinazmu, как будто функция «min» здесь является коэффициентом нормализации. Кажется, он работает безупречно, поскольку «StaffNo» является идентификатором результатов запроса, и нет необходимости различать «StaffName». Ваше здоровье!
Либо «W001», либо «Сэм Х» представляют уникальные значения. Вот почему. Итак, перепроверьте свои данные.