Access query design help (1 Viewer)

[mitchk]

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);

 

[GPGeorge]

To make it dynamic, you could try using a talley table of years. A talley table is a one field table of the values you want to include. In this case that would be years from 2000 to 2024. In future, all you need to do is add the next year, e.g. 2025.

I don't have your table and sample data to validate on, so this is a suggestion to try.

Include the year talley table in the query with a left outer join to the [DateIn] field in the existing table, so that one record is returned for each year, whether it has a corresponding record or not. Pivot on that field.

 

[theDBguy]

Hi. Welcome to AWF!

You can either have a separate table of all the years that you update manually or use a query to list all the years from your original table assuming there are no gaps and then use it to join with your customer table.

 

[arnelgp]

column count can be of max 255 only.
you need VBA to update your Crosstab query with the latest Year to add.

make to copies of this query (name the queries, qryOrig and OriginalNameOfYourQuery):

TRANSFORM Count(tbl_GoodsInAndStockData.JobNumber) AS CountOfJobNumber
SELECT tbl_GoodsInAndStockData.CustomerName
FROM tbl_GoodsInAndStockData
GROUP BY tbl_GoodsInAndStockData.CustomerName
PIVOT Year([DateIn])

next create a sub/function on Module to update the years on your query:

Public Sub subUpdateMyQuery()

    Dim db As DAO.Database
    Dim qd As DAO.QueryDef
    Dim sql As String
    Dim sYr As String
    Dim i As Integer, j As Integer, k As Integer
    
    j = 2000
    k = Year(Date)
    
    'adjust the beginning year
    If k - j > 254 Then
        j = k - 254
    End If
    
    For i = j To k
        sYr = sYr & "," & i
    Next
    
    sYr = Mid$(sYr, 2)
    
    
    Set db = CurrentDb
    sql = Replace$(db.QueryDefs("qryOrig").sql, ";", "")
    Set qd = db.QueryDefs("OriginalNameOfYourQuery")
    qd.sql = sql & " IN (" & sYr & ");"
    Set qd = Nothing
    
End Sub

 

[nector]

Just filter a query either <> 0 in the query grid, I always do it that way

 

[mitchk]

Morning Guys,

thankyou so much for your help - I've been able to use both the talley table and vba solutions for my query