Hi Guys,
New here hoping for somebody to help me understand how to create this query.
I have a database of customer records which span years 2000 to current and are trying to create a query to show the customer (row) by the amount of units received each year (column).
Problem is I want the query to return 0 for the years that they didn't deal with us or stopped dealing with us
how would I do this ? closest I have so far is adding an IN Clause to the SQL like below but I don't know how to make it dynamic so it adds a new column each year?
TRANSFORM Count(tbl_GoodsInAndStockData.JobNumber) AS CountOfJobNumber
SELECT tbl_GoodsInAndStockData.CustomerName
FROM tbl_GoodsInAndStockData
GROUP BY tbl_GoodsInAndStockData.CustomerName
PIVOT Year([DateIn])
IN (2000, 2001, 2002, 2003, 2004, 2005, 2006, 2007, 2008, 2009, 2010, 2011, 2012, 2013, 2014, 2015, 2016, 2017, 2018, 2019, 2020, 2021, 2022, 2023, 2024);
New here hoping for somebody to help me understand how to create this query.
I have a database of customer records which span years 2000 to current and are trying to create a query to show the customer (row) by the amount of units received each year (column).
Problem is I want the query to return 0 for the years that they didn't deal with us or stopped dealing with us

how would I do this ? closest I have so far is adding an IN Clause to the SQL like below but I don't know how to make it dynamic so it adds a new column each year?
TRANSFORM Count(tbl_GoodsInAndStockData.JobNumber) AS CountOfJobNumber
SELECT tbl_GoodsInAndStockData.CustomerName
FROM tbl_GoodsInAndStockData
GROUP BY tbl_GoodsInAndStockData.CustomerName
PIVOT Year([DateIn])
IN (2000, 2001, 2002, 2003, 2004, 2005, 2006, 2007, 2008, 2009, 2010, 2011, 2012, 2013, 2014, 2015, 2016, 2017, 2018, 2019, 2020, 2021, 2022, 2023, 2024);