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

FinanceGuy

New member
Local time
Today, 14:37
Joined
May 7, 2021
Messages
5
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

AWF VIP
Local time
Today, 19:37
Joined
May 23, 2011
Messages
4,269
Is the field "Period" to be a "Date" type field or a "Short Text" type field
 

FinanceGuy

New member
Local time
Today, 14:37
Joined
May 7, 2021
Messages
5
"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

I’m here to help
Staff member
Local time
Today, 11:37
Joined
Oct 29, 2018
Messages
15,655
Hi. Welcome to AWF!

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

 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 14:37
Joined
Feb 19, 2002
Messages
32,310
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

New member
Local time
Today, 14:37
Joined
May 7, 2021
Messages
5
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

Super Moderator
Staff member
Local time
Today, 14:37
Joined
Feb 19, 2002
Messages
32,310
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.
 
Last edited:

Users who are viewing this thread

Top Bottom