I'm trying to automatically generate data that varies based on which years there are values and populate an excel sheet for analysis. My skill at coding VBA is mediocre, so I'm aiming to populate specific fields to ease the burden. I'm pulling sales over a ten year period where it's grouped by part number, the year is the column, and the total sales are the sum value in the row, it looks something like this:

The issue is that the years vary per run, so some runs will have 2010, 2012, others 2017, 2018, 2019, etc. And when I try a generic update (or append) query to push this data into fields labeled by year from 2010 to 2021, any time a field doesn't exist, it throws an error instead of just ignoring it.

Using the above example, there are only 2-year columns in this crosstab. But my query has updates for all possible years, 2010-2021. For missing fields, I've tried phrasing like NZ([theData]![2019],"") when updating or appending, hoping if the field didn't exist it would just toss in nulls, but it errors when the field doesn't exist. Is this possible to get around? Or do I need to build a VBA loop that checks the header value and pushes the data into the matching field in the table?

CodePudding user response：

Since you are not providing the SQL code that you are using, it is unclear what you are doing. If you post your source table and the SQL code you tried, it would be easier to answer. With the information you provide, what I think would solve your problem is:

a) Create a Table (e.g., tblYearlySales) in your Access database. This Table is where the information will be automatically introduced, and that you can then export as an Excel file, in which you can process the information using Excel. This Table should have fields "Part", "2010", "2011", ..., "2021".

b) Create a delete Query to delete all the information from Table tblYearlySales:

DELETE FROM tblYearlySales

c) Create an insert Query to insert the information you want into Table tblYearlySales. Such an insert Query would be:

  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 ;

This last Query is used to convert rows into columns. You may want to check the Query "K_Rows_into_columns_1" from the database of examples downloadable from LightningGuide.net.

d) Whenever you want the information, run the delete query followed by the insert query, and then export the table tblYearlySales as an Excel file, using command "Excel" from ribbon "External Data".