Запрос доступа ms (доступ ms зависает)

У меня есть этот отчет, и мне нужно добавить итоги по каждому человеку (красный кружок) существующий отчет

новый отчет

Я не могу изменить существующий отчет, поэтому экспортирую данные из MS SQL в MS Access и создаю там новый отчет. У меня он работает для одного сотрудника, но у меня проблемы с запросом, который подходит для нескольких сотрудников.

Этот запрос извлекает данные, которые используются в качестве входных:

SELECT [TIME].[RCD_NUM], [TIME].[EMP_ID], [TIME].[PPERIOD], [TIME].[PRUN], [TIME].[TDATE], [TIME].[PC], [TIME].[RATE], [TIME].[HOURS], [TIME].[AMOUNT], [TIME].[JOB_ID], [TIME].[UPDATED], [TIME].[UPDATED_BY], [TIME].[LOG_DATE], [TIME].[ORIGINAL_REC_NUM]
FROM [TIME]
WHERE ((([TIME].[EMP_ID])=376) And (([TIME].[TDATE])<=#12/31/2006# And ([TIME].[TDATE])>=#1/1/2006#) And (([TIME].[PC])<599));

этот запрос заполняет отчет:

SELECT *
FROM TIME1
WHERE RCD_NUM = (SELECT Max(RCD_NUM) FROM [TIME1] UQ WHERE UQ.PPERIOD = [TIME1].PPERIOD AND UQ.PC = [TIME1].PC);

проблема в том, если я удалю EMP_ID из первого запроса, подобного этому

SELECT [TIME].[RCD_NUM], [TIME].[EMP_ID], [TIME].[PPERIOD], [TIME].[PRUN], [TIME].[TDATE], [TIME].[PC], [TIME].[RATE], [TIME].[HOURS], [TIME].[AMOUNT], [TIME].[JOB_ID], [TIME].[UPDATED], [TIME].[UPDATED_BY], [TIME].[LOG_DATE], [TIME].[ORIGINAL_REC_NUM]
FROM [TIME]
WHERE ((([TIME].[TDATE])<=#12/31/2006# And ([TIME].[TDATE])>=#1/1/2006#) And (([TIME].[PC])<599));

то второй запрос не работает и доступ ms зависает при выполнении этого запроса.

любая помощь / идея, пожалуйста?

ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
2
0
1 453
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

Предостережение: Я не буду делать вид, что знаю точную причину проблемы, но мне приходилось неоднократно реорганизовывать запросы в Access, чтобы заставить их работать, даже если исходные операторы SQL полностью действительны с точки зрения синтаксиса и логики. Иногда мне приходилось свертывать последовательность запросов, чтобы избежать ошибок в Access. Доступ часто бывает довольно глупым и просто (повторно) выполняет запросы и подзапросы в точности так, как указано, без оптимизации. В других случаях Access будет пытаться объединить запросы, выполнив некоторые внутренние оптимизации, но иногда они приводят к разочаровывающим ошибкам. Такая простая вещь, как изменение имени или переупорядочение столбцов, может быть разницей между работающим запросом и тем, который приводит к сбою или зависанию Access.


Сначала подумайте:

  • Можете ли вы оставить данные на SQL Server и связать их с результатами в Access (а не экспортировать / импортировать их в Access)? Даже если вам нужно или вы предпочитаете использовать Access для создания фактического отчета, вы можете использовать всю мощь SQL Server для запроса данных - это, вероятно, менее ошибочно и более эффективно.
    • Распространенной передовой практикой является создание хранимых процедур SQL Server, которые возвращают именно те данные, которые вам нужны в Access. В Access создается сквозной запрос для извлечения данных, но все операции с данными выполняются на сервере.
  • Возможно, это просто проблема производительности, когда ограничение набора [EMP_ID] выбирает небольшое подмножество, но полная таблица достаточно велика, чтобы «заморозить» доступ.
    • Как долго вы позволяли доступу оставаться замороженным, прежде чем завершить процесс? Будьте терпеливы ... как много-много минут (или часов). Начни с утра и проверяй после обеда. :) Он может в конечном итоге вернуть набор результатов. Это не означает, что это приемлемо или что нет другого решения, но может быть полезно знать, возвращает ли оно в конечном итоге данные или нет.
    • Сколько существует возможных записей?
    • Правильно ли проиндексированы импортированные данные? Добавьте индексы ко всем ключевым полям и тем, которые используются в предложениях WHERE.
    • База данных находится в общей сетевой папке или она локальная? Попробуйте скопировать базу данных на локальный диск.

Другие подсказки:

  • Попробуйте использовать оператор BETWEEN для дат в предложении WHERE.

Попробуйте провести рефакторинг «второго» запроса, выполнив соединение в предложении FROM, а не в предложении WHERE. При этом вы также можете сохранить подзапрос как именованный запрос (так же, как сохраняется [TIME1]). Независимо от того, сохранен ли запрос или встроен в другой оператор, МОЖЕТ изменить поведение Access (см. Предупреждение), даже если результаты должны быть идентичными.

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

SELECT TIME1.*
FROM TIME1 INNER JOIN
  (SELECT UQ.PPERIOD, UQ.PC, Max(UQ.RCD_NUM) As Max_RCD_NUM
   FROM [TIME1] UQ
   GROUP BY UQ.PPERIOD, UQ.PC) As TIMEAGG
  ON (TIME1.PPERIOD = TIMEAGG.PPERIOD) And (TIME1.PC = TIMEAGG.PC)
    AND (TIME1.RCD_NUM = TIMEAGG.Max_RCD_NUM)

Здравствуйте, большое спасибо за ваши предложения. Я бы предпочел сделать это в Access (локальном) по соображениям безопасности (по крайней мере, сейчас), поскольку я не проектировал исходную базу данных MS SQL. Когда я запускаю свой запрос, диспетчер задач показывает «Доступ не отвечает ...» Количество записей не должно быть проблемой, поскольку первый запрос выбирает только около 3500 записей. Я попробовал ваш запрос, и он выглядит многообещающим - он не зависает, дает некоторые записи, к сожалению, он также дает мне сообщение об ошибке «Синтаксическая ошибка в предложении FROM» как в параметрах Open, так и в параметрах редактирования, и я не могу ни использовать данные, ни редактировать их. запрос.

user10360284 14.09.2018 19:22

Хммм ... Я не понимаю, как запрос возвращает записи и ошибку «в предложении FROM». Если в предложении FROM есть ошибка, он вообще не должен возвращать никаких записей. Кроме того, что вы имеете в виду, что «не можете ни использовать данные, ни редактировать этот запрос»? Вы имеете в виду, что вы не можете редактировать SQL или что вы не можете редактировать данные, однажды возвращенные в таблицу? Если вы имеете в виду данные в таблице, это правильно - они не обновляются, потому что они присоединяются к агрегированному запросу, но, насколько я могу судить, это не важно, поскольку это только для отчета.

C Perkins 14.09.2018 19:30

Да, он возвращает записи и одновременно показывает поверх них сообщение об ошибке. Когда я нажимаю «ОК» в сообщении об ошибке, записи исчезают (окно набора записей закрывается). Когда я открываю запрос в Edit, происходит то же самое - сообщение об ошибке, нажмите, все закрывает.

user10360284 14.09.2018 20:06

Непонятно. Я никогда не испытывал такого особенного поведения. Вы пробовали восстановить и сжать базу данных? Как я уже упоминал в ответе, попробуйте сохранить и запустить встроенный подзапрос как именованный запрос. Он работает без ошибок? Если это сработает, отредактируйте «второй» запрос, чтобы присоединиться к вновь сохраненному запросу вместо встроенного оператора.

C Perkins 14.09.2018 20:32

Мне удалось составить и запустить отчет по вашему запросу, сообщений об ошибках нет. Но результаты неверны - некоторые отсутствующие сотрудники (получили 27 из 75), отсутствующие периоды оплаты, некоторые неправильные суммы для конкретных сотрудников и периодов. Выполнение встроенного подзапроса в качестве именованного запроса прошло без ошибок.

user10360284 14.09.2018 20:48

Рад, что запрос работает без ошибок, но все это выходит за рамки этого вопроса, особенно без знания многих других деталей данных. Я бы с подозрением отнесся к точности процесса экспорта / импорта. Все ли значения передаются правильно? Вы импортируете напрямую через ссылку ODBC или через текстовый файл CSV? Можете ли вы проверить все строки и столбцы по отдельности после импорта? Есть проблемы с форматированием? Я предлагаю выполнить различные запросы на проверку данных, чтобы убедиться, что все данные есть и не повреждены.

C Perkins 14.09.2018 21:06

Большое спасибо за ваш вклад и помощь. Я импортировал через текстовый файл CSV. Импорт был правильным, я получил все данные в MS Access; Я могу видеть, какие записи отсутствуют или неверны в MS Access, поэтому попытаюсь найти причину / шаблон для этих недостающих / неправильных записей.

user10360284 14.09.2018 21:14

Хорошо, удачи. Но как бы то ни было, ваше утверждение снова кажется противоречивым :). Импорт правильный, но вы подразумеваете, что данные отсутствуют / неверны. Я полагаю, это означает, что значения исключаются из экспорта из SQL Server? В любом случае удачи и, если нужно, задайте еще один вопрос с новыми подробностями.

C Perkins 14.09.2018 21:20

Извините за недопонимание; Я имел в виду, что импорт из MS SQL в Access был правильным, но отсутствующие / неверные данные были в отчете, который я запускал на основе вашего запроса (а не в импортированных данных). Спасибо еще раз!

user10360284 14.09.2018 21:28

Другие вопросы по теме