Convert quarterly rows into monthly rows

[zach9208]

I have a table called master that has records stored at a quarterly level ie. each record has a fndng_dt=1Q2017, 2Q2017, 3Q2017 ect.

I need to take each record and triplicate each record with the same data and stamp the appropriate fndng_mo_dt based on the fndng_dt field. For example, a row with fndng_dt=1Q2017 would need need two new rows added and I need the fndng_mo_dt to show as 01, 02, 03 respectively across these three records. In addition, I have a field called wgtd_sls_calc_amt in this table and I need to equally allocate this amount to the new 2 rows.

I am looking for suggestions on how to best do this. I am hoping I can do this in a query but the only way I can think of doing it is with a new table where I append the original data 3x times to get the extra rows. After that, I am still unsure how I can get each row to correctly stamp the fndng_mo_dt to each of the 3 rows based on what is in the fndng_dt field.

Any suggestions or ideas?

 

[kbrummert]

Are you looking for an Excel answer or a database answer?

 

[zach9208]

Preferably in Access, but I am open to Excel VBA if there is not a reasonable solution in Access.

Are you looking for an Excel answer or a database answer?

 

[kbrummert]

Try...

Create a mapping table with the left two characters of the quarter date (1Q, 2Q, 3Q...). In column 2 of this table, put the months that go with each. In column 3, you can put 1/3 (this can be fancier using counts and subqueries, but for now you know that there are only 3 months per quarter. To summarize, this table will have 12 rows and 3 columns: qtr, month_no, column_3_fraction.

After you have completed the table, create a query joining the two tables on left(yourtable.fndng_dt, 2) = mappingTable.qtr. Add a calculated field: calcFld = yourtable.Wgtd_sls_calc * mappingTable.column_3_fraction.