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].
I'm new to Access. It is obviously a very powerful database program I've developed a data base of my agency's service purchases for a variety of clients. I have to generate a report totaling the monthly purchases and counting the number of unduplicated clients. We make multiple purchases for clients monthly. There must be a feature on this program that will sort through the client ID Numbers and count them (rejecting duplicates) in that one month or quarter or year. I've been exporting to excel, sorting by client ID and then counting each new ID number.
I need help with a calculation in my form. I have a form named families. IN this form I have 12 check box's, one for each month. I would like to set up another box which would take the average of the past 3 months and tell me what percentage of the time the box is checked. For example, since it is december, I would like a box named quarterly average to look at the past 3 months, obviously september, october and november, and tell me in percentages what the percentage is that the past 3 months check boxes have been checked. This is the basic code which I created for my unbounded box, but I want it to be dynamic, so that it recognizes what the month is today and tells me automatically what the percentage is.
Control sourceis set to =Abs(([Sep]+[Oct]+[Nov])/(3))
Hello, I’m new to this board and to Access as well. My Database has two tables. Once contains product data including quarterly volumes. The other contains quarterly pricing data. I’d like to query the pricing data so that it returns the proper price based on the quarter the volume ocurred in. This would then be used to calculate Revenue. How do I write a query to select the correct price based on the quarter value in another table?
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"
: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?
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.
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?
i would like to know how to replace the sales values in the quarterly sales report with tax values so as to create a "Quarterly tax report" in Northwind.
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
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.
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.
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?
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?
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.
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
Every month for my construction company, I receive a pay application that consists of the following:
1) about 100 "Line Items" (each one being a different type of work performed)
2) a Subcontractor associated with each line item (a line item may have only 1 Sub, but a Sub may be listed multiple times for different line items)
3) Amount of work performed (in $$) on that line item by that particular Sub during that pay period.
My boss wants a db to track all of this info. She thinks the best way would to create a new table for each Pay Application, every month. Basically, we would be starting from scratch every month with a blank table. But, I think it would work better if the months were linked to each other in some way.
I could have a field called "Pay_App" and enter 1 or 2, etc. on every row for every line item every month. But, then each month, I would be entering a 1 (or whatever the month is) for every single line item and that seems stupid. I want to be able to pull up the entire pay schedule for a particular Sub (all of his payments throughout the entire job). Is there a way to link the different pay application tables once the data has been entered? Or is there an easier way to do this? I would appreciate any help that can be provided. Thanks.
I have stuck in something and i hope someone will help me with this: I have created a DB which shows all the students of a musical school. Each student pays a monthly fee (sep-june, 10 payments per year). I dont want to create all these columns in my main table beacause it will be too crowded. The only thing I can think of is to create 10 different tables one for each month but i think it won't work well when I want to see how much a specific student pays.. any ideas? the information for each month that i want to have is the student first and last name, date of payment, level, fees amount and money paid (and perhaps month name..). Thank you in advance guys!
