Splitting rows in a table based on date fields.

[Tom123456]

Hey,

I have a list of bookings that have a start & end date and a amount.

I need to split the table so that each month has it's own line and % amount

For example before it would look like this.

Booking	Start Date	End Date
Booking 1	01/08/2017	30/11/2017
Booking 1	01/10/2017	30/11/2017
Booking 2	11/08/2017	30/09/2017
Booking 2	11/08/2017	30/09/2017
Booking 3	07/08/2017	31/08/2017

<tbody>
</tbody>

but i need to work out how to split it to become like so

Booking	Start Date	End Date
Booking 1	01/08/2017	31/08/2017
Booking 1	01/09/2017	30/09/2017
Booking 2	11/08/2017	31/08/2017
Booking 2	01/09/2017	30/09/2017
Booking 2	11/08/2017	31/08/2017
Booking 2	01/09/2017	30/09/2017
Booking 3	07/08/2017	31/08/2017

<tbody>
</tbody>

I've added columns that calculates the number of days/month in each row and the amount per day but don't know where to start with using this information to split like this- if anyone can point me in the right direction it would be a great help! Cheers, Tom

<tbody>
</tbody>

 

[Joe4]

Here is how I would do it.

I would use VBA, first sorting my table, then creating a record set and looping through it, creating the records that I need.
I cannot really think of another way to do it.

If that is an acceptable option, let me know, and I will see if I can whip up some code for you.

 

[Tom123456]

Hi Joe,

This would be an acceptable option - would you be able to provide some VBA code for me to test with?

Thanks,

Tom

 

[Joe4]

Give me a little time to whip something up. It has been a while since I have done something like this, so I need to consult my notes.

A few questions for you though:
- What is the exact name of your Table and Fields?
- What I am envisioning will create the records in a new Table. Is that OK?
- Can you explain why some bookings appear to be duplicated or overlapping (for example, Bookings 1 and 2 in your original example)?
- Are there other fields in this table which need to be populated too?

 

[Tom123456]

- What is the exact name of your Table and Fields?

Table is called 'Data' Fields are shown in this image (URL below)

https://pasteboard.co/GLf2Nff.png

What I am envisioning will create the records in a new Table. Is that OK?

A new table is ok

- Can you explain why some bookings appear to be duplicated or overlapping (for example, Bookings 1 and 2 in your original example)?

Sorry this is an error on my part when providing the sample data - Booking 1 should of been split into 4 lines (Aug,Sept,Oct,November) - this image should explain it correctly

https://pasteboard.co/GLf63oJ.png

- Are there other fields in this table which need to be populated too?

Yes they all need to be duplicated identically (except the start/end date fields & the amount field which i need to split based on days in each of the new rows - but i left this out as to not over complicate my starting issue)

 

[Joe4]

Sorry this is an error on my part when providing the sample data - Booking 1 should of been split into 4 lines (Aug,Sept,Oct,November) - this image should explain it correctly

Unfortunately, my work's Corporate Security Policy blocks those type of image sites, so I cannot see any of your image links.
Can you post a new data sample, like you did in your original post?

Yes they all need to be duplicated identically (except the start/end date fields & the amount field which i need to split based on days in each of the new rows - but i left this out as to not over complicate my starting issue)

There is a real danger in oversimplifying the issue, and that is you will probably get an oversimplified response (since it is based on your oversimplified question) that will not work for you, unless you are savvy enough to know how to update the VBA code to do what you want. I see it happen all the time. People get a response, and then respond that it will not work for them because ..., and they list a bunch of new conditions that they did not mention originally.

So, unless you feel comfortable enough with VBA and DAO Recordsets to update (yourself) the code I would write based on your original requirements, I think you will want to mention these other fields, and if something has to happen to this Amount field, explain that logic too.

 

[Tom123456]

Hi Joe,

Apologies for oversimplifying the issue.

Table is called Data and it has the following fields and a number of rows that follow the format shown below

Reference

Line ID

Sales Person

Customer

Start Date

End Date

Primary Contact

Brand Family

Product Platform

Product Type

Product Name

Unit of Measure

Amount

Contract Month

Customer Contract Date

Amount Currency

Navision ID

DCON-66606

338768

Chris

Axa - London

02/08/2017

25/11/2017

Phil

Insurance

Digital

Video

Bespoke

Exclusive

£20,0000

Aug 2017

07/08/2017

GBP

WB125

DCON-66606

338770

Chris

Axa - London

01/10/2017

30/11/2017

Phil

Insurance

Digital

Lead Generation

Hound

Leads

£5000

Aug 2017

07/08/2017

GBP

WB126

<tbody>
</tbody>

For every row in this table i need the VBA to look at the start & end date fields and if the period spans more then a single month it needs to do 2 things.

1) Duplicate the row and it's information (exlc start/end dates which change) by the number of months in that period
2) Calculate & update the new amount for the row (the new amounts on the duplicated line should sum to the original amount)

The new amount in these rows is calculated by

Original Line Amount / Days in Period = DailyRevenueRate

Days in New period * DailyRevenueRate = New Amount

Therefore the first row from above would be split into 4 rows with the amount value split by the days in it's period

DCON-66606

338768

Chris

Axa - London

02/08/2017

31/08/2017

Phil

Insurance

Digital

Video

Bespoke

Exclusive

£5000

Aug 2017

07/08/2017

GBP

WB125

<tbody>
</tbody>

DCON-66606

338768

Chris

Axa - London

01/09/2017

30/09/2017

Phil

Insurance

Digital

Video

Bespoke

Exclusive

£5172.314

Aug 2017

07/08/2017

GBP

WB125

<tbody>
</tbody>

DCON-66606

338768

Chris

Axa - London

01/10/2017

31/10/2017

Phil

Insurance

Digital

Video

Bespoke

Exclusive

£5344.83

Aug 2017

07/08/2017

GBP

WB125

<tbody>
</tbody>

DCON-66606

338768

Chris

Axa - London

01/11/2017

25/11/2017

Phil

Insurance

Digital

Video

Bespoke

Exclusive

£4482.75862

Aug 2017

07/08/2017

GBP

WB125

<tbody>
</tbody>

And the 2nd Row would be split into 2 rows in a similar manner

 

[Joe4]

OK. Give me a little time with this, and I will see if I can get something to you later today.

 

[Joe4]

A few more things I just noticed...

Going back to your example, when you show it broken out, you have different Amounts for August and September (5000 and 5172.314).
However, shouldn't they be the same? Aren't they both 30 days (2/8/17-31/8/17 and 1/9/17-30/9/17 are both 30 days)?

Also, are we concerned about rounding differences not adding up to exactly the original amount (maybe be off by a fraction)?

 

[Tom123456]

They should be the same - some bad mental math on my part adding days in a month

Line 1 has a 116 days in total split as below

Amount	Days
5172.413793	30
5172.413793	30
5344.827586	31
4310.344828	25

<tbody>
</tbody>

I am concerned about rounding differences that mean the sum of the lines do not equal the amounts but liberal on how to resolve (eg add the correct fractional amount to any line)

Cheers,
Tom