Creating a Record for Each Customer for Each Period

[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

 

[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.