#vba #ms-access
Вопрос:
Я пытаюсь автоматически генерировать данные, которые варьируются в зависимости от того, в какие годы существуют значения, и заполнить лист Excel для анализа. Мои навыки программирования на VBA посредственны, поэтому я стремлюсь заполнить определенные поля, чтобы облегчить нагрузку. Я собираю продажи за десятилетний период, где они сгруппированы по номеру детали, год — это столбец, а общие продажи — это сумма в строке, это выглядит примерно так:
| часть | 2018 | 2020 |
|---|---|---|
| Азбука | $2,000 | $10,652 |
Проблема в том, что годы варьируются в зависимости от запуска, поэтому некоторые запуски будут иметь 2010, 2012, другие 2017, 2018, 2019 и т.д. И когда я пытаюсь выполнить общий запрос на обновление (или добавление), чтобы поместить эти данные в поля, помеченные по годам с 2010 по 2021 год, каждый раз, когда поле не существует, оно выдает ошибку вместо того, чтобы просто игнорировать ее.
Используя приведенный выше пример, в этой перекрестной таблице есть только столбцы за 2 года. Но в моем запросе есть обновления для всех возможных лет, 2010-2021. Для отсутствующих полей я пробовал формулировать, например NZ([theData]![2019],"") , при обновлении или добавлении, надеясь, что если поле не существует, оно просто добавит нули, но это приведет к ошибке, когда поле не существует. Возможно ли это обойти? Или мне нужно создать цикл VBA, который проверяет значение заголовка и помещает данные в соответствующее поле в таблице?
Комментарии:
1. Зачем вам это нужно для привязки к таблице? Почему бы не экспортировать запрос? В противном случае, да, нужно, чтобы VBA управлял набором записей. Если у вас есть какой-то код с конкретной проблемой, опубликуйте вопрос с подробностями.
2. Потому что в конечном итоге эти данные должны быть загружены в лист Excel. Моей первоначальной целью был быстрый экспорт, при котором все данные о продажах за все годы каждый раз заполняются в одних и тех же столбцах. Если у run one есть только 2 года, а у run 2 — 6 лет, у меня будут данные, передаваемые в неправильные места, если я просто экспортирую их вслепую. Вот почему сначала нужно перейти в установленную таблицу. Или мне придется потратить слишком много времени на кодирование маленького винтика на этой большой машине, что, похоже, мне придется сделать. Я надеялся, что есть простое решение, которого я не видел.
Ответ №1:
Поскольку вы не предоставляете используемый вами код SQL, неясно, что вы делаете. Если вы опубликуете свою исходную таблицу и код SQL, который вы пробовали, будет легче ответить. С предоставленной вами информацией, я думаю, что это решит вашу проблему:
a) Создайте таблицу (например, tblYearlySales) в вашей базе данных Access. В эту таблицу будет автоматически вводиться информация, которую затем можно экспортировать в виде файла Excel, в котором вы можете обрабатывать информацию с помощью Excel. В этой таблице должны быть поля «Часть», «2010», «2011», …, «2021».
б) Создайте запрос на удаление, чтобы удалить всю информацию из таблицы tblYearlySales:
DELETE FROM tblYearlySales
c) Создайте запрос на вставку, чтобы вставить нужную информацию в таблицу tblYearlySales. Такой запрос на вставку будет:
INSERT INTO tblYearlySales
SELECT Part
, Max(Iif(Year=2010, Sales, 0)) AS 2010
, Max(Iif(Year=2011, Sales, 0)) AS 2011
, Max(Iif(Year=2012, Sales, 0)) AS 2012
, Max(Iif(Year=2013, Sales, 0)) AS 2013
, Max(Iif(Year=2014, Sales, 0)) AS 2014
, Max(Iif(Year=2015, Sales, 0)) AS 2015
, Max(Iif(Year=2016, Sales, 0)) AS 2016
, Max(Iif(Year=2017, Sales, 0)) AS 2017
, Max(Iif(Year=2018, Sales, 0)) AS 2018
, Max(Iif(Year=2019, Sales, 0)) AS 2019
, Max(Iif(Year=2020, Sales, 0)) AS 2020
, Max(Iif(Year=2021, Sales, 0)) AS 2021
FROM Table_where_your_source_info_is
GROUP BY Part ;
Этот последний запрос используется для преобразования строк в столбцы. Возможно, вы захотите проверить запрос «K_Rows_into_columns_1» из базы данных примеров, загружаемых с LightningGuide.net .
d) Всякий раз, когда вам нужна информация, запустите запрос удаления, за которым следует запрос вставки, а затем экспортируйте таблицу tblYearlySales в виде файла Excel, используя команду «Excel» с ленты «Внешние данные».