Earning Out Annual Premium Data Into Monthly Buckets
Nov 29, 2005
Hi there, thanks in advance for any help or input with this. I am working with tables that have annual data on insurance policies that are effective for 1 year. I have Policy number (PolicyNum), current premium (CurrentPrem), the date the policy went into effect (PolicyEffDate), and cancellation (CXDate, which is 1/1/2001 if the policy was not cancelled). I am trying to earn out the CurrentPrem into monthly buckets. The tables are by the effective year going back to 2004, so basically 2004, 2005, 2006.
I'm not sure how to go about tackling this and have tried a number of different ways. I sense that I will need a query that will have 24 fields for all the months over a two year period that any given 1 year policy can be effective over. For instance, a policy created on 5/1/2004 (May 2004) is effective until 4/31/2005 (April 2005), and a policy created on 12/31/2004 is effective until 12/30/2005.
So for every month between January 2004 and December 2005, there needs to be a value generated (either zero or a monthly premium value).
Attached is a sample table with data. Really all I've been able to do is calculate how long a given policy is in effect for (how many months) and then how much the monthly premium is. I cannot figure out how to appropriate the monthly premiums to the corresponding months a policy is effective for.
Note that the current premium value takes into account cancellation which implies that the monthly premium for a cancelled policy is not 1/12 * [CurrentPrem] but 1/[MonthsInEffect] * [CurrentPrem].
View Replies
ADVERTISEMENT
Jan 26, 2008
Need a little professional advice.
I have a product table. Those products expire over time. Their shelf life is monthly, quarterly, or annual.
What should I use for monthly, quarterly, and annual in my table.
My first thought was 30, 90, and 365. Then in my query I would use the datediff function. If the datediff was >= to ...then ...
However there are months with 28,29, and 31 days. With this quarterly can change as well. The annual as well by 1.
What is the rule, if there is one?
View 9 Replies
View Related
Mar 20, 2013
I am creating payment database for a community gym, the data will be created on a data sheet.
There are 2 payment types monthly and annual (these are on a dropdown box with their own id number).
I have a joining date and a paid date, but reflected against the payment type ie 31 days or 365 days.
I have been trying to do something along the lines of:
if [monthly] (is selected) (so this would call up a 31days) date() is > [joining date] then give message to renew and same kinda thing for annual.
View 1 Replies
View Related
May 18, 2014
I have a small clinic database. I've got tblAppointments to show AppointmentID and AppointmentDate among various other data, but only the dates matter for what I'm trying to achieve.
I'm trying to show a chart on a report that shows the number of appointments by months for this year and previous year. How can I do this...without using SQL, hopefully?
Here's what I tried: I made a cross-tab query to successfully show the years 2013 and 2014 in the rows, months in the columns, and number of appointments as values. But then, I didn't know how to graph it to compare the number of appointments for the two years by months.
View 5 Replies
View Related
Apr 22, 2015
I have a query for loan calculation, fields are;
Loanamount
loandate
monthlyinstalment
what i want is that query to start subtracting lmonthlyinstalment from loanamount on monthly basis
View 3 Replies
View Related
Jan 5, 2015
I was assigned by my manager to design an Access database system that is able to import all data from excel file monthly and creating charts & tables to analysis how each sales people and industry perform.
We originally have a big excel master sheet that has more than 10 sheets. I tried to import the current excel into access, but then i realized that this is not gonna work. because for next month, there will be new data and I can't do the whole import process over and over. Plus, after this system is designed, the users will be someone who has no knowledge in access, so i need to create a user-friendly system for them to use.
My questions is:since the data is always cumulative number, if I imported current excel file into access, when the next month comes, how to update the new data into excel. p.s. EXP. Mike's sale volume is different each month, and with the access system, for that column, it will be a cumulative number, like the total from the month of November to this month. how do i achieve this kind of update/import goal?I tried to link the excel to access, but by doing that, I will not be able to set relationship or change the attributes of any data type in access.
View 3 Replies
View Related
Nov 21, 2012
I have a Size Breakdown with 8 Buckets..I'd like to Sum the contents of the buckets and divide that to Total Yards then have the result saved to a field(Per Garment) in a table (Marker Details Table).Per Garment = Size Breakdown / Total Yards (25 / 7 = 3.57).
View 5 Replies
View Related
Nov 4, 2011
What I am trying to accomplish is import data from a CSV file every month and have it stored as a table and have that table automatically be converted into a printable invoice. I can do the import with no problem. But I am at a bit of a loss as to how I can go about creating the database.
There is only one client that I have to make up an invoice for, but will be more in the future. I need to store the data for each months invoice and then get yearly reports off that data.
#1, How would I go about connecting each months data?
#2, Should I be using a report to create the invoice layout?
#3, How do I go about automating creating the invoice from the imported data? (I am at an intermediate level with VBA, but access seems to be a whole different ball game)
View 5 Replies
View Related
May 5, 2014
I am constructing a database for a friend and need to filter data from a daily log to enable a report to be generated for each months actions. I have created fields of: entry date, month and year with the view of filtering by month and year. How do I perform this task?
View 3 Replies
View Related
May 23, 2012
How do I create a daily table from monthly data? I have a monthly table and want to split it into a daily table by dividing each monthly value by the number of days in that month. I need this so I can compare the new daily values to other daily values.
View 1 Replies
View Related
Apr 17, 2014
I have a table that track monthly data for several thousand products.
The idea is to grab the first month and the first three months of sales for each product.
So I simply need to have a statement that scans through a predefined set of fields and seeks the first none zero value. This become the first month of sales (even if it is the 5th month of the year). It then needs to be able to grab the proceeding 2 months to create a sum of sales in the first 3 months. (however that would be for a second field, so presumably that is just a slightly more complex version of the same formula used to find the first month of sales).
View 2 Replies
View Related
May 27, 2012
I receive monthly project tracking reports and I'm looking to compare a few things between these monthly updates. I should add I'm new to Access and have been using Excel and dual monitors to manually compare data sets!!! My life might be getting a LOT easier.
Here's what I'm tracking:
I get monthly excel reports that have unique values from a "Project ID"each "Project ID" stays the same every month, but the project status changes from "Installed", "Commitment", "Pre-Commitment", etcSometimes new projects are addedSometimes old projects disappear from the reportA few other columns have numerical data that changes every month as well "Project Cost", "$ Incentive", etc.
Here's what I'm trying to automate:
Find out what Project IDs are newFind out if old Project IDs are missing from the new reportSomehow "flag" or identify projects that have status changesSomehow "flag" or identify which numerical values increased or decreased i.e. "Project Cost", "$ Incentive", etc
Do I create two tables? Do I append the new monthly report to the old one when I import my data, then somehow work with the new information?
View 1 Replies
View Related
Feb 22, 2005
I've just been asked to generate an annual report, to the 1st of January of every year the current expression i'm using is for up to date, how would i change it to annual example Jan 2005
Years Of Service: DateDiff("yyyy",[EmpHireDate],Date())+(Date()<DateSerial(Year(Date()),Month([EmpHireDate]),Day([EmpHireDate]))) & " Years, " & (DateDiff("m",[EmpHireDate],Date())) Mod 12 & " Months"
Thanks...
View 4 Replies
View Related
Jul 30, 2012
I am fairly new to access but so far I have been able to get what i need from it, until now. I am trying to find a way of comparing two sets of data to find out an employee's average productivty.
Table 1 -Hours Worked (by day)
- contains 'name' 'date' and '# of hours' worked
- an employee would enter the hours here on a daily basis
Table 2 - Contracts Keyed (by month)
- contains the number of contracts worked that is derived from seperate system
- this is entered on a monthly basis (so for example: John keyed 30 contracts for the month of January)
- the system i am pulling this info from does not have the ability to pull a daily count of contract per employee, only a range of dates and it then provides the sum for that range (unless I ran a query for each day, for each employee which would take me hours)
- employees dont have access to this system to enter their own # of contracts keyed on a daily basis.
- for entry, so far i have just been putting the first of the month and then the # of contracts.
In a nutshell, this is the calculation I am trying to create:
(Sum of "# of hours" for the month) / (total "# of contracts keyed" for the month) = employees average hourly productivity.
I have tried to do this with various types of queries and reports but with no luck, I get a prompt saying that access can't compare the 2 fields.
Is there a way to compare the data that is entered daily with the data i would enter monthly?
View 2 Replies
View Related
Feb 21, 2014
I've been requested to make a bird count database.
The bird count data has been recorded on monthly papers like this:
[bird name] [week 1][week 2][week 3][week4]
The number of birds sighted for a given week is written in the [week x] columns.
What I need to do is make another column that shows whichever number is highest from columns [week 1], [week 2], [week 3], [week 4].
So for example:
Blackbird: Week1: 4, Week2: 2, Week3: 6, Week4: 2
highest: =6
View 5 Replies
View Related
Dec 5, 2005
:confused: I wish to construct a form that displays a graph showing the monthly sales totals year on year. In this way - I will be able to see quite easily how the present monthly income compares to previous years.
Therefore, each year will have its own data series on the graph... the problem I have is that I wish the graph to automatically recognise the roll-over to the next year thereby creating the next series for that year.
This would mean having the months Jan-Dec on the X axis and the income scale as the Y axis. Each data series representing the year.
Otherwise, the alternative is to manually change the underlying datasource every year to include .... has anyone done anything similar to this that they would be willing to share please?
Thank you.
Guido
View 7 Replies
View Related
Mar 6, 2015
I have a database with shifts in for staff. They have a bunch of times in and times out over a four week period. I have gotten an average weekly amount of hours for each staff member based on this but I need another equasion to work out their leave entitlement. It breaks down like this...
Average weekly hours x 5.6 x number of days working in this period (ie start date and end of financial year day count) divided by number of days in the financial year (ie 1/4/2015 - 31/3/2016 day count)
I'm just wondering of a way to do the day count based on me keying in the start date of the staff member (default 1/4/2015) and that access can work out the days in that financial year left and the actual days in that financial year.
It sounds simple enough but I want to get it to automate based on my start date.
View 14 Replies
View Related
Apr 12, 2014
I've worked out how to sum all my sales (turnover) from a given nominated date...but what I seek now is to project forward what the annual turnover will be (which will quickly give me a 'pulse' & indicate if I'm growing as my financial year progresses)
My financial year started on Feb 4 2014 ...I can easily sum all my sales from that date, but ideally what I'd like to do is divide that sum by the number of days elapsed (this will give the average turnover per day) & then project that forward to Feb 3 2015 (the last day of my financial year)
So is there anyway that access can work out the number of days that have elapsed between 4 feb & 'now' (to get the average) ....but more specifically also how many days between 'now' ....and Feb 3 2015 - I then can use the remaining number of days to multiply the average daily takins to give an indication of what my year end annual turnover will be :-)
Or am I faced with having to do such calculations manually outside of access?
View 2 Replies
View Related
Dec 1, 2014
On the surface this seems easy, but I am struggling. two tables
tbldata (four fields 6,270 rows)
custid 2015cellcode 2015qty 2015amt
12673 1243 100 1,000.00
12673 1250 200 2,000.00
etc etc
tblmntlyalloc (Three fields and 12 rows i.e. one row for each month)
2015mnth, 2015allocation, 2015wrkdays
jan 98 20
feb 93 19
etc etc etc
So in order to handle seasonality of sales, the sales department is given 1,200 points. you can then allocation any number of points (75 - 125) per month. But the total number of points must equal 1,200. So I created a table with the monthly allocation and workdays. I could hard code the % and work days into the formula like this
Jan1[2015amt]*(98/1200)) / 20
Feb1[2015amt]*(93/1200)) / 19
And this does work very well to get me my daily sales amount per month/wrkday
However I know sales will change the monthly allocation (still totals 1200) and HR could even have a change to the holiday schedule. So I want to utilize the table.
So my query brings in both tables, but they are not linked. And this is my formula.
Jan1[2015amt]*([2015allocation]/1200) / [2015wrkdays]
Feb1[2015amt]*([2015allocation]/1200) / [2015wrkdays]
Mar1[2015amt]*([2015allocation]/1200) / [2015wrkdays]
Apr1[2015amt]*([2015allocation]/1200) / [2015wrkdays]
etc etc for 12 months
So I need it to find the value for Jan in the Jan formula, and Feb in the feb formula etc. I cannot determine the correct query formula.
View 8 Replies
View Related
Sep 15, 2005
I have a large data set with values ranging from 1-25,000. I would like to be able to calculate how many entries there are in each increment of 10 (1-10, 10-20, etc.), then perform the same query for each increment of 100.
Any ideas?
View 3 Replies
View Related
May 10, 2005
ok i think i know what i need (searching the forums gave me the idea)
i have query that generates a report, i need this report to be flitered monthly
is it possible to have afield where i enter the month so when i press the monthly report button it just prints any entries in that month
please can someone oint me in the right direction.
View 2 Replies
View Related
Jun 28, 2006
Hi, I have Three tables
the first is a ContractTable
the second is a Monthly Invoice Details Table
and the third is a Detailed Payments table
Contract 1 -> Many Monthly Invoice 1 -> Many Detailed Payments
what I would like to do is ensure that there arent any duplicated Months for a Contract in the Monthly Invoice table
in other words For Contract 00023 I NEVER want it to have more than one invoice record for the month of JUNE 2006....
It may be easy but it has me all tied up, please assist.
View 4 Replies
View Related
Jan 13, 2006
I will try to explain my problem as best i can and would appreciate any thoughts other people have on it, it is surely similar in some degree to someone elses previous work!
I need to produce management information on a monthly basis, one example of this type of work are an employees one to ones.
table121 contains following fields, ID scheduledDate CompletedDate Completed(yes/no)
My report/query needs to group records by the month (which i do through formatting date fields to display mm/yy), count the number of scheduled one2ones, count the number of completed one2ones, display a %.
I have played around and got this to work using querys with grouping and sums.
My problem is if the schedule date and the completed date are in different months then all of the statistics become out of sync, particualy when there are more appraisals taking place than scheduled.
Any ideas?
View 2 Replies
View Related
Mar 16, 2005
I am Trying To calculate Costs for a particular month how do i do this when taking into account that the months obviously hae different lenghts? I have the overall week cost but how do i do it for the month?
View 3 Replies
View Related
Aug 2, 2006
I am trying to create an inventory which list sales by month. I have created a crosstab query from the detail history table which works fine except I would like a record for each month whether there was sales or not. So on the report I would have all 12 months for each item with the months with no sales displaying zero.
Any suggestions?
Valerie
View 6 Replies
View Related
Nov 26, 2007
I am having issues trying to run a query. What we have is a private club that keeps track of its members with an access 2007 database. What we need to do is to be able to print out new members monthly for a state audit. The way it is setup now, our members reports prints all of the members since day one. For example, we have a state audit coming up next month for November and we would only like to print out members that have joined during the month of November, but our report is printing out all of the members since we started in August.
We do have a date joined query. The fields we would like to print are: Date Joined, Member ID, FName, LName, City, State
Any help would be greatly appreciated, thank you.
View 8 Replies
View Related
