Date problem

[mohiahmed]

Hi all,

I am really stuck with this problem. This is a two part problem and I don't know if it can be solved in access but I know it can be done in Excel.

1) I have the field "Application Date" and "DOB." I want to find out the amount of days between "Application Date" and "DOB+3years."
i) is it possible to create a field that adds 3 years to the date of birth(DOB)?


ii)Would access give me, in days, the difference between "Application Date" and "DOB+3years."

2) If the above is possible I then want to have a calculated field which takes into account the difference mentioned above. I want the top 25% to receive a certain amount of points, lets say 25 points then the next 25% to receive 15 points and then the next 25% to receive 5 points and 0 points for the final 25%.

Could this be done in access? If so can anyone guide me please?

Thank you.

[aytee111]

1) Use the DateDiff function - it will do all you ask.

2) what do you mean by "top 25%"? What is the actual formula to get that number?

[mohiahmed]

1) Thank you, this worked perfectly!

2) What I mean is, each individual record will now have a figure for the difference. Lets say we have 4 records only, e.g.
Name Difference(days)
1) John Smith 38
2) Gordan Bennet 55
3) Casper White 88
4) Bart Simpson 101

I would like to give points 25 points to the top 25% (according to the least days difference) - So John Smith should receive 25 points as he is in the top 25% being the lowest of the four. Then Gordan would receive 15 points for being the in the next 25% etc...

[aytee111]

Access does not do what Excel does - you have to do it all yourself. So you need to write out the formula first, then it can be translated into Access. You need to be able to write it out in such a way that you or I or anybody can take any set of records and apply them to the formula and the same answer will come out each time. Using 4 records is too easy, use 11. Once we know what the formula is we can then translate it into query code or VBA or whatever.

[mohiahmed]

Access does not do what Excel does - you have to do it all yourself. So you need to write out the formula first, then it can be translated into Access. You need to be able to write it out in such a way that you or I or anybody can take any set of records and apply them to the formula and the same answer will come out each time. Using 4 records is too easy, use 11. Once we know what the formula is we can then translate it into query code or VBA or whatever.

Ok, so with 11 records on the database, the top 2(with the least difference between the date they applied and their dob+3years) will receive 25 points, then the 3rd to the 5th will receive 15points, the 6th to 8th should receive 5points and 9th to 11th 0 points.

I hope I've made myself clear. I'm not sure if access can do this?

[aytee111]

Like I say, Access can do just about anything - it is just that YOU have to tell Access to do it. If something can be written down then it just needs to be converted into Access-speak. Access doesn't understand words like "top". A formula is written like this:
(a + b - (c / d)) * (100 / 7.5) etc.
That is what you need to get to before you can put it into Access, a formula that will work on any set of record.
I'm not good with mathematics at all (I always find someone to help me!). It may be something like add up all the number of days and dividing by 4, or something like that?

[orange]

3 Years from today (today) is Date
dateadd("yyyy",3,date) = 21/01/2015

Days difference between today and 3 years from today

?datediff("d",date,dateadd("yyyy",3,date))
1096

With your fields ApplicationDate and DOB

DobPlus3 = Dateadd("yyyy",3,DOB)

DifferenceInDays is DateDiff("d",ApplicationDate,DateAdd("yyyy",3,DOB)

Create a query to get that info.
Select PersonName,DateDiff("d",ApplicationDate,DateAdd("y yyy",3,DOB) as DifferenceDays FROM MyTable Order By DifferenceDays;

[mohiahmed]

Thank you orange, if I understand right then what you have mentioned just orders them but is there no formulae that gives points depending on number of ppl in the database?

[orange]

True, now you can create another query to act on the first query or create some vba to process the first query as a recordset.