Creating a Record for Each Customer for Each Period (1 Viewer)

[FinanceGuy]

I'm an advanced user of Access for someone in corporate finance, however a novice compared to those in technology. I've gotten by with simple queries and basic SQL language.

I have a list of about 400 customers. I would like to create a record for each customer, for each month. For example:

Customer | Period
Coca-Cola | 2021-01
Coca-Cola | 2021-02
Coca-Cola | 2021-03
Pepsi | 2021-01
Pepsi | 2021-02
Pepsi | 2021-03

I would need to do this for each customer for multiple years (2013 through 2029 at the moment). Being a novice, the only way I can think of doing this is writing multiple queries that first appends the list with the group of customers, then updates that list with the first period. Repeat and update the list where null with the second month, and so on and so on.

There has to be a better way that I don't know of. Any suggestions would be appreciated.

Thanks.

 

[bob fitz]

Is the field "Period" to be a "Date" type field or a "Short Text" type field

 

[FinanceGuy]

"Short Text" type field. I am flexible and can either change it to a "Date" type field or create a new field that is "Date" type if that helps.

 

[theDBguy]

Hi. Welcome to AWF!

Maybe take a look at my Cartesian Query articles to see if they give you any ideas.

Cartesian Query

When creating a query involving multiple tables, we can select from three types of table joins depending on the desired result of the query....

thedbguy.blogspot.com

A practical use of a Cartesian Query

As promised in my previous blog on Cartesian Query , I will now try to describe in detail one example of a practical use of a Cartesian Quer...

thedbguy.blogspot.com

 

[Pat Hartman]

theDBguy's suggestions to use a Cartesian Product will do what you want but -- What is the point of making "empty" records? This is not typically done in relational database applications. It sounds more like a spreadsheet concept.

 

[FinanceGuy]

theDBguy - Thank you. This works!

Pat Hartman - I was provided with the monthly revenue value for each customer subscription contract. I need to compare each period (month) from each year to December from the prior year. Unfortunately, the dataset provided to me only goes out to the end of each contract. I need to figure out a way to fill in "the gap" for the remaining periods in the year.

 

[Pat Hartman]

Rather than adding empty rows, you can use left joins in the compare query. That way you don't have to add the empty rows to your existing table. Just remember that the data in the "missing" rows in the left join will be Null rather than zero so use the Nz(YourAmt, 0) function to convert the amount to zero.

I would not add "empty" rows to a table. I don't know that they will cause you a problem but consider what happens when you average data:

the average of three rows (3, 0, 3) = 2
However,
the average of two rows (3,3) = 3
So, adding the "empty" row would affect any average you did on the data.

However, if you added the "empty" rows with null amounts, you will get away with it because: the average of (3, null, 3) = 2 snce null values are ignored.