Query calculations

[JMarcus]

In need of help with the building of a query with the following criteria:

Find out which members are still active for the entire year of 2015, field is either Active, Cancelled or Disenrolled
If members were active**all of 2015 from*1/1/2015*to*12/31/2016*they get full amount which is 215.00
If members were active all of months in 2015 find out how many months, and divide total amount by number of months which is 12.
Calculate the monthsIf =*1/1/2016*and greater than*12/31/2015full pay 215.
Calculations are as follows Number of months ex. January to June 6/12 months for the year. $215/6 = $35.83
Should I just build a table with # of months and the commission amount then link it that way

 

[arnelgp]

what are the fieldnames on your table, do you have separate field dates for when a person get activated and cancelled/disenrolled?

 

[JMarcus]

Yes two seperate dates so I know I would need to calculate the amount of months

 

[The_Doc_Man]

What level of rounding do you allow?

If you use DateDiff with the "m" date unit, it will tell you the difference in months between two dates - but it will round the result. If rounding is not a viable solution, you need to more closely define the problem as to how you handle month fractions.

 

[JMarcus]

Nevermind. Figured it out. Had to convert the raw data to date format. Thanks so much. Saved me time.