By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,148 Members | 765 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,148 IT Pros & Developers. It's quick & easy.

Distribute the value into multiple field base on date start and date end

P: 5
I'd like to seek help with my access database. i would like to distribute value (format on currency) into multiple field base on date start and date end.

for example on:
if field Spend Amount value is - $10,000
if field start date is - Jan-2018
if Field end date is - April-2018

then the Jan field will be $2,500
and Feb field should be $2,500
Mar field also $2,500
april field also $2,500

basically the amount should be equally divided base on start month and end month category

appreciate your help please
Jan 27 '19 #1

✓ answered by PhilOfWalton

Try

Expand|Select|Wrap|Line Numbers
  1. MnthlyAmount = Format(Amount / (DateDiff("m", StartDate, EndDate) + 1), "Currency")
The DateDiff function gives the correct number of months even if different years are involved. It is assumed that the dates are in US format (mm/dd/yyyy), not UK Format (dd/mm/yyyy)

Phil

Share this Question
Share on Google+
11 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,284
NakedEye,

Welcome to Bytes!

First, it appears that your DB is not properly designed, if you have separate fields for each month, as usually, you will want to have one record for each distributed amount.

However, that aside, if you want to use start and stop dates as your starting point, you would determine the number of month of the start date and subtract that from the number of month from the stop date and add one, to get the number of months. This can easily be done using the Month() function. Using your example, Jan-2018 is Month=1, April-2018 is Month=4; 4 - 1 = 3, add 1 and you get 4. Divide the total amount by 4 and you get four easy payments of only $2,500.

However, as you can imagine, this method quickly breaks down if your dates span any period that begins a new year. A preferred method would be to simply identify the start date and the number of months that this value should be distributed over.

However, without more information, we can’t provide any better solution. You may want to provide a more detailed description of what it is you are trying to do.

Hope this hepps.
Jan 27 '19 #2

Expert 100+
P: 1,035
if you want to use start and stop dates as your starting point, you would determine the number of month of the start date and subtract that from the number of month from the stop date and add one, to get the number of months. This can easily be done using the Month() function.
But, remember!, this will fail if the month are in a different year.

So, please als use Year(), and not only Month() or, even better, use DATEDIFF()


Expand|Select|Wrap|Line Numbers
  1. SELECT DATEDIFF(month, '2018-11-01','2019-02-01')
returns 3.
Jan 27 '19 #3

PhilOfWalton
Expert 100+
P: 1,430
Try

Expand|Select|Wrap|Line Numbers
  1. MnthlyAmount = Format(Amount / (DateDiff("m", StartDate, EndDate) + 1), "Currency")
The DateDiff function gives the correct number of months even if different years are involved. It is assumed that the dates are in US format (mm/dd/yyyy), not UK Format (dd/mm/yyyy)

Phil
Jan 27 '19 #4

P: 5
Hi guys, thanks for the input..

PhilOfWalton solutions works! it gives the total cost value per month base on date range. but how can I distribute the monthly value amount with the correct field on another table.

to give more clarity. I have another table where field name Jan-2019 | Feb-2019 | Mar-2019 | April-2019 | May-2019 | AND SO ON TILL 2022 for example.

now how can I automatically input the monthly value in the correct month field?

thanks for your help
Jan 28 '19 #5

twinnyfo
Expert Mod 2.5K+
P: 3,284
Again, as stated in my earlier post, the main issue is the design of your table. You should never have dates hard-coded as names for fields, as time constantly changes. What happens when you go beyond 2022? Do you simply make your table wider? This is simply a poor design. Rather, you would want to have one record for each transaction with one field for TransactionDate.

Also, again, rather than looking at calculating the number of months (although Phil has supplied an excellent solution for that method), I would still recommend simply using a start date and the number of months for the transactions. For example, when one takes out a 10-year loan, one does not have a start date of January 2019 and a stop date of December 2028. One has a start date of January 2019 and 120 payments. This allows one to calculate based upon actual transactions—although they do actually occur once every month.

Finally, two more thoughts. Keep in mind that “currency” is merely a format for a Number. So, in Phil's example, there is no need to apply the format to the calculation. Additionally, if the StartDate and EndDate are Date data types, their format is immaterial, as a date is a date, irrespective of the format and the DateDiff() function will work. The only problem comes when you send DateDiff() hard-coded dates such as “28-Jan-2019” vs. “2019-01-28”. The first value may work in some regions of the world, but the second will work everywhere, because it is a universally recognized date format of “yyyy-mm-dd”.

Again, hope this hepps.
Jan 28 '19 #6

P: 5
thank you twinny got your point.

but if I will say yes i am willing to make my table wider after 2022 or to 2023. would you tell me what is the solution to populate the cost value in each month? base on start and end date? or base on my first and second question?

please help i am desperate to achieve it.

thanks in advance
Jan 28 '19 #7

twinnyfo
Expert Mod 2.5K+
P: 3,284
NakedEye:
but if I will say yes i am willing to make my table wider after 2022 or to 2023. would you tell me what is the solution to populate the cost value in each month? base on start and end date? or base on my first and second question?
I wil respond by simply saying I won’t give you a solution to a table design that is destined to fail in the long run. Please understand that to do so would be a dis-service to you and to others viewing this forum.

One aspect of this forum is to teach others proper design in order to prevent future headaches. I can’t think of any other moderators here who will intentionally teach you how to do design your database wrong so that you can fix it at a later time.

Not knowing anything more about your overall design, I can’t give you additional guidance. However, my guess is that this particular table is not the only one that is designed poorly.

Please don’t take this response as being harsh or cruel. We want you to succeed at your project, but to encourage you to continue down a path that is destined for major significant problems in the future would be the wrong answer, no matter how completely it “solved” your problem.
Jan 28 '19 #8

P: 5
Twinny - I just thought one aspect of this forum is to answer what is asked! so why I ask specific question and expecting specific answers!

And yeah you're right, not knowing my overall design you cant give me additional guidance, so why i asked question only in one particular subject which I am planning to add to my database. so I suggest answer the questions or suggest other way of doing it but don't judge the entire project. I suggest with all due respect not to response if you do not know the answer on the particular question.
Jan 28 '19 #9

twinnyfo
Expert Mod 2.5K+
P: 3,284
I suggest you review other threads on this forum and see how I and other moderators have tried to guide posters toward reliable, tried and true database design.

This is not a judgment against you, it is an assessment of your table design, and a perception, based upon experience--but without true knowledge--that there may be additional design flaws in some of your other tables. Trust me--we have all had to learn how to do things the right way, and sometimes we have learned the hard way by doing it wrong in the first place. This is why we guide you toward a better design.

You will also quickly find on this forum that those who are unwilling to learn or simply reject sound advice will not receive the help they desire.

This is your first thread--and your first experience--with Bytes, so I will grant you a certain, small degree of latitude in your previous post. Respect is mutually given and earned. Advice to redesign your tables so you don't experience future migraines over a poor design would be, I should think, a way of showing respect to a first-time poster. I want you to mature in your database design and avoid the same mistakes I have made over the years.
Jan 28 '19 #10

P: 5
Suggest to answer the question (if you know the answer) and then advice the better way of doing it!
Jan 28 '19 #11

NeoPa
Expert Mod 15k+
P: 31,494
@NakedEye.
I've sent you a Private Message. Please read that very carefully before posting again on Bytes.com.

-NeoPa (Admin).
Jan 28 '19 #12

Post your reply

Sign in to post your reply or Sign up for a free account.