Блог

База данных настроена для отслеживания членства в сообществе соседей. Для контекста: район разделен на десять зон, причем каждая зона подразделяется на уровень улицы. Каждая подзона имеет одну или несколько улиц, либо частичных, либо полных. Любая улица может охватывать несколько подзон. Адреса фиксированы, но семьи переезжают и наши. Членство необязательно. Для отслеживания всего этого настроено несколько таблиц. У меня возникли проблемы с настройкой одного конкретного отчета. Из-за запросов Access «не совсем перетаскивание», когда речь идет об условных обозначениях для левых соединений («Левое соединение B левое соединение C где C.this_column равен null»), я не смог выполнить отчет с помощью одного запроса. Таким образом, базовая структура:

• Запрос отчета, используемый для получения названий улиц, адресов и идентификаторов для зоны / подзоны
• подзапрос, используемый для запроса резидента, если таковой имеется, и статуса членства за последние пять лет
• Определены два уровня группы — подзона (по одному на страницу) и улица (один или несколько на страницу)
• Флажки отражают статус членства за каждый год и извлекаются из подзапроса с обязательным «= IIF(iserror(…» для размещения свободных адресов.
• нижний колонтитул подзоны содержит текстовые поля для общего количества участников по годам (флажок установлен)

Проблема: я не могу заставить нижние колонтитулы подзоны «добавить». Предыдущий отчет, написанный для всего этого проекта (одна таблица, около 60 столбцов, с новыми столбцами каждый год), смог выполнить «=Sum ([checkbox-element-name])» для этого. Когда я пытаюсь сделать то же самое и запустить отчет, я получаю всплывающее окно с запросом значения для .

Из-за явного разочарования и грубой силы, окончательное, отвратительное решение, которое я придумал, было:

• Установите флажок в подзапросе
• Установите скрытые текстовые поля для хранения значения каждого флажка с надписью «Текущая сумма = по группе»
• В нижнем колонтитуле street для скрытых текстовых полей установлено значение имя элемента checkbox (отображает текущий итог). Для них также установлено значение «Текущая сумма = по группе». Обратите внимание, что пропуск этого шага означал, что подзоны, охватывающие несколько улиц, будут распространяться только на последнюю улицу в нижний колонтитул подзоны
• В нижнем колонтитуле подзоны для текстовых полей заданы имена текстовых полей нижнего колонтитула подзоны — они правильно отображают итоги по всем улицам в пределах подзоны.

Теперь, когда он работает, я ненавижу оставлять его в этом состоянии. Используя построитель выражений на каждом этапе, он распознавал каждый элемент, когда я пытался использовать «= Sum [element-name]», но все равно выдавал всплывающее приглашение. Есть идеи, что здесь происходит, и как я могу успешно это исправить? Это Office Professional 2016, работающий в Windows 10 Home.

Редактировать, для ясности: это полная перезапись старой базы данных. Это более старая таблица, которая представляла собой «одну таблицу, 60 столбцов, добавление столбцов в год». Новая база данных была нормализована, чтобы предотвратить такую необходимость в будущем. Я упоминаю старое только потому, что там работали суммирования. Они не работают в новом дизайне.

Редактировать 2: детали запроса Если бы я заменил все это одним запросом в mysql, вот как это выглядело бы. Пожалуйста, обратите внимание, что это вводится холодно и фактически не выполняется. Если бы я мог перенести это в MS Access, вся моя проблема была бы решена. Это работало … пока я не ввел требование «end_date равно null» — это указывает на текущий резидент. Рассматривается сценарий, в котором дом переходил из рук в руки в течение последних пяти лет. Мы хотим, чтобы адрес был указан только один раз, с текущим резидентом.

 -- house: id, number, address, area, subarea
-- family: id, family_name
-- house_family: house_id, family_id, start_date, end_date (when family moved in/out)
-- membership: house_id, family_id, year (family was a member in this house, for this year)

SELECT h.area, h.subarea, h.street, h.number, f.family_name,
       IF(m4.year IS NULL, 'Y', 'N') 'Year-4',
       IF(m3.year IS NULL, 'Y', 'N') 'Year-3',
       IF(m2.year IS NULL, 'Y', 'N') 'Year-2',
       IF(m1.year IS NULL, 'Y', 'N') 'Year-1',
       IF(m0.year IS NULL, 'Y', 'N') 'Year-0',
FROM house h
     LEFT JOIN house_family hf ON h.id = hf.house_id
     LEFT JOIN family f ON hf.family_id = f.id
     LEFT JOIN membership m4 ON m4.house_id = h.id AND m4.family_id = f.id AND m4.year = YEAR(DATE_SUM(CURDATE(), INTERVAL 4 YEAR))
     LEFT JOIN membership m3 ON m3.house_id = h.id AND m3.family_id = f.id AND m3.year = YEAR(DATE_SUM(CURDATE(), INTERVAL 3 YEAR))
     LEFT JOIN membership m2 ON m2.house_id = h.id AND m2.family_id = f.id AND m2.year = YEAR(DATE_SUM(CURDATE(), INTERVAL 2 YEAR))
     LEFT JOIN membership m1 ON m1.house_id = h.id AND m1.family_id = f.id AND m1.year = YEAR(DATE_SUM(CURDATE(), INTERVAL 1 YEAR))
     LEFT JOIN membership m0 ON m0.house_id = h.id AND m0.family_id = f.id AND m0.year = YEAR(CURDATE())
WHERE hf.end_date IS NULL
GROUP BY h.id