Annual Income Graph Problem
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 Replies
ADVERTISEMENT
Mar 15, 2013
I am trying to calculate variable income tax but can't seem to get it to work.
I have a query called payslips which calculates gross income based on hrs worked and payrate. I then have a separate tax table called TaxThreshWitheld which states the earnings and the tax witheld. I am trying to create a query that selects the tax witheld when earning from the taxwitheld table equals the gross field from the payslip query.
The two tables aren't linked and I am not sure how to link them. I tried to link "earnings" and "gross" but that causes the query not to run...
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
Jun 22, 2013
Access database to calculate income tax?
I'd like to be able to input the amount I invoice each month into a table and it would calculate Vat and Income tax due.
I'm sure its been done many times before however I cant find any examples.
View 1 Replies
View Related
Apr 1, 2013
I have some issues with summarizing in a query. My intention is to summarize income and expenses over periods
This is how it should look like.
Periods_____Total income___Total expenses
01-2013_____234__________435
02-2013_____533__________132
03-2013_____345__________853
etc
I made the following query to get the result I wanted.
Unfortunately, the result seems to be multiplied instead of summarized.
View 3 Replies
View Related
Dec 2, 2014
I have this columns :
Income | Outcome | Transport | Total Income | Date
Total Income (N) = Income (N) + Transport (N-1)
Total income equals income from that day + transport from previous day
Transport (N) = Total Income (N) - Outcome (N)
Transport from previous day equals Total Income minus outcome
How can I calculate the transport from previous day to sum to next day total income?
View 1 Replies
View Related
Apr 24, 2013
I have to create a query to calculate the Base Income per room.
Guest 1-4 is the standard room rate say $125/day, guests 5 & 6 pay additional $20 charge per day. Guests staying for seven days or more receive a 10% discount. How to build this expression into a query in access. If it was Excel that would be easier.
Table info I have is:
Room
Arrival Date
Departure Date
No of Guest (per room)
Daily Rate
View 6 Replies
View Related
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 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
May 6, 2013
I am currently using Access 2007 and have created a huge database for our investment managers to calculate the income based on percentages. The percentages are created in Excel. I have uploaded into the database both the June and the July percentages. When I run the query for my report using the date range for June, it works fine. When I request just the July information (entering the date range for July) the June data is doubled on the report.
View 4 Replies
View Related
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 1 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
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
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
Jun 13, 2005
I came across this Time line db using a box that grows depending on timeline.
Can anyone tell me if it is possible to plot a value using a box that grows in accordance with the value?
This should serve as a replacement for bar graph or an alternative at that.
Please help.
Edwin
View 8 Replies
View Related
Apr 29, 2008
Hi there,
I need to create a graph containing monthly data for numbers of changes raised.
It needs to display the total amount of changes raised split into priority per month like this:
http://i38/photobucket.com/albums/e111/Benson112/Changegraph.jpg
Do i need multiple queries for each month? How do I collate them all together to create the graph?
Thanks in advance
View 4 Replies
View Related
Aug 8, 2005
We have a daily task that generates a dozen graphs. Each day, we want to display the graphs on a large screen monitor for public viewing, preferably as a slideshow. Currently the graphs come from Access 2003. How can we automate getting the graphs in a format that will work with PowerPoint or some other slideshow software?
View 5 Replies
View Related
Sep 29, 2004
I have tried to create graphs in my reports and when I try to resize it, the right side of the graph gets cut off. I am using Access 2000.
When I do the same exercise in Access XP Pro, it works. Can someone help me? I have downloaded the most updated service pack for it as well.
View 2 Replies
View Related
Jun 28, 2006
I am trying to change the fill colors on a bar graph based on the value the bar graph has.
the bar graph gives the percentages of tasks and I would like to have it so that the fill color of a bar is based on the a set of values for the percentage... so if the percentage of task A is between 0 and 25% it should be red, if percentage of task A is between 25% and 50% it should be yellow, and if the percentage of task A is between 50% and 100% it should be green.
this is the query that the graph is based off of
Code:SELECT TEST_RawData.DeliverableDesc, FormatPercent(Avg(PercentComplete)) AS CurrentProgressFROM TEST_RawDataGROUP BY TEST_RawData.DeliverableDesc;
This is the data table of the graph
DeliverableDesc | CurrentProgress
test 1 | 82.64%
test 2 | 55.75%
test 3 | 30.09%
test 4 | 13.00%
now I have looked at this http://support.microsoft.com/?kbid=200527 and tried to remodel it to my situation but have some concerns. This is what I have so far
vba Code: Original - vba Code Private Sub Report_Open(Cancel As Integer) Dim chtObj As Object, strRowSource As String Dim rsRowSourceFiltered As Recordset Dim i As Integer, j As Integer Dim strArrTaskPercents() As String Dim intArrTaskColors() As Integer ' The color integers are those that are used by the QBColor function to assign point colors. Const cBadProgress_Red = 4 Const cOkayProgress_Yellow = 6 Const cGoodProgress_Green = 2 ' Place all the task percents values into an array. ReDim strArrTaskPercents(3) strArrTaskPercents(1) = "10.00%" strArrTaskPercents(2) = "25.00%" strArrTaskPercents(3) = "50.00%" ' Place the task color values into an array. ReDim intArrShipperColors(3) intArrTaskColors(1) = cBadProgress_Red intArrTaskColors(2) = cOkayProgress_Yellow intArrTaskColors(3) = cGoodProgress_Green Set chtObj = Me!Graph10.Object strRowSource = "SELECT TEST_RawData.DeliverableDesc, FormatPercent(Avg(PercentComplete)) AS CurrentProgress FROM TEST_RawData GROUP BY TEST_RawData.DeliverableDesc;" Set rsRowSourceFiltered = CurrentDb.OpenRecordset(strRowSource, dbOpenSnapshot) ' Check to see if the filtered recordset has any records. If rsRowSourceFiltered.BOF And _ rsRowSourceFiltered.EOF Then MsgBox "There are no records to chart." Exit Sub End If ' Loop through the recordset containing the chart's filtered RowSource. rsRowSourceFiltered.MoveFirst i = 0 While Not rsRowSourceFiltered.EOF ' Index i synchronizes the Points collection index with the current recordset row. i = i + 1 ' Loop through the task percents array and look for a match with the field names of the chart's filtered RowSource. For j = 1 To UBound(strArrTaskPercents) ' 1-based ' The first field in the recordset contains the task percent. Some tasks may not be in the filtered recordset. If rsRowSourceFiltered.Fields(0).Value = strArrTaskPercents(j) Then ' Because every task has a corresponding color, the arrays strArrTaskPercents and intArrTaskColors always contain the same number of elements. Assign the color of the chart column, bar, slice etc. Graph10.SeriesCollection(1).Points(i). _ Interior.Color = QBColor(intArrTaskColors(j)) End If Next rsRowSourceFiltered.MoveNext WendEnd Sub Private Sub Report_Open(Cancel As Integer) Dim chtObj As Object, strRowSource As String Dim rsRowSourceFiltered As Recordset Dim i As Integer, j As Integer Dim strArrTaskPercents() As String Dim intArrTaskColors() As Integer ' The color integers are those that are used by the QBColor function to assign point colors. Const cBadProgress_Red = 4 Const cOkayProgress_Yellow = 6 Const cGoodProgress_Green = 2 ' Place all the task percents values into an array. ReDim strArrTaskPercents(3) strArrTaskPercents(1) = "10.00%" strArrTaskPercents(2) = "25.00%" strArrTaskPercents(3) = "50.00%" ' Place the task color values into an array. ReDim intArrShipperColors(3) intArrTaskColors(1) = cBadProgress_Red intArrTaskColors(2) = cOkayProgress_Yellow intArrTaskColors(3) = cGoodProgress_Green Set chtObj = Me!Graph10.Object strRowSource = "SELECT TEST_RawData.DeliverableDesc, FormatPercent(Avg(PercentComplete)) AS CurrentProgress FROM TEST_RawData GROUP BY TEST_RawData.DeliverableDesc;" Set rsRowSourceFiltered = CurrentDb.OpenRecordset(strRowSource, dbOpenSnapshot) ' Check to see if the filtered recordset has any records. If rsRowSourceFiltered.BOF And _ rsRowSourceFiltered.EOF Then MsgBox "There are no records to chart." Exit Sub End If ' Loop through the recordset containing the chart's filtered RowSource. rsRowSourceFiltered.MoveFirst i = 0 While Not rsRowSourceFiltered.EOF ' Index i synchronizes the Points collection index with the current recordset row. i = i + 1 ' Loop through the task percents array and look for a match with the field names of the chart's filtered RowSource. For j = 1 To UBound(strArrTaskPercents) ' 1-based ' The first field in the recordset contains the task percent. Some tasks may not be in the filtered recordset. If rsRowSourceFiltered.Fields(0).Value = strArrTaskPercents(j) Then ' Because every task has a corresponding color, the arrays strArrTaskPercents and intArrTaskColors always contain the same number of elements. Assign the color of the chart column, bar, slice etc. Graph10.SeriesCollection(1).Points(i). _ Interior.Color = QBColor(intArrTaskColors(j)) End If Next rsRowSourceFiltered.MoveNext WendEnd Sub
these are my concerns
- first is the strucutre and syntax for the above Sub Report_Open good?
-second how do I match percents the query is outputting to the table to the percents I write in the strArrTaskPercents array
-third how would I change how it matches percents so that it matches a range of percentages, like the first example if something is between 50% and 100% it should be green.
if anyone can directly help with the code, or indirectly point me to some examples, articles, etc. that might help me it would be greatly appreciated.
View 3 Replies
View Related
Jul 3, 2005
A while back a colleague at work (who has since left my company) showed me an example of a graph within a Data Access Page in the Microsoft Northwind database. The Northwind database that I've downloaded doesn't contain such an example. Does anyone know of a Northwind DB with an exmaple of a graph embedded into a Data Access Page?
View 2 Replies
View Related
Jan 4, 2006
I am trying to export the result of a query to a specific Excel sheet where I have a dynamic graph. The results are exported on a monthly basis, so I am just adding the data, and the graph reflects that. Now, I can export to excel easily enough by using the Transfer Spreadsheet method, but this creates a new worksheet, thus destroying my lovely graph. I have even tried the Output Query method, but to no avail. Is there any way I can export to excel and keep the graph intact?
David
View 2 Replies
View Related
Nov 16, 2006
Hello,
I got a simple graph on my form based on the following query:
date | ordercount:
-------------------------
16/11/06 20
15/11/06 18
14/11/06 19
13/11/06 18
10/11/06 19
09/11/06 18
My graph is leaving out the TOP record and my datasheet I get this result:
16/11/06 | 20
-------------------------
15/11/06 18
14/11/06 19
13/11/06 18
10/11/06 19
09/11/06 18
So as you can see, my graph is actually confusing my TOP record with the title and that's why it's not showing. So how can I fix this?
On a sidenote:
As you can see, I didn't work on 11/11 & 12/11 but my graph is still showing those dates but of course with value 0. How can I leave out dates where I created no orders?
Kind regards,
B.
View 2 Replies
View Related
Jun 25, 2007
Hello :D I have an issue with Access I was hoping someone could help me on. So I have this pie chart graph with 3 sections, colored red for East, Green for West, and Blue for North. Now the problem is that sometimes, the data that I pull in doesn't have a "West" and therefor doesn't add it to the chart. This, however, throws the coloring scheme off as it assigns colors so that 1 is red, 2 is green, 3 is blue rather than East is red, West if Green, and North is Blue. I'll provide some images so it'll be easier to understand. (Clickable thumbnails)
http://img509.imageshack.us/img509/3600/beforefb5.th.jpg (http://img509.imageshack.us/my.php?image=beforefb5.jpg) http://img514.imageshack.us/img514/4678/aftermo0.th.jpg (http://img514.imageshack.us/my.php?image=aftermo0.jpg)
View 3 Replies
View Related
Jan 31, 2008
Is it possible to put a graph in an graph in an outlook message? I realize I will need to use an outlook object but not sure how to start off - ?
View 3 Replies
View Related
Apr 14, 2005
Hi,
I would like to display a graph in a form along with the fields from a table. The user will be allow to update the table and the graph will be updated. So far I am able to display the graph on the report but I cannot find a way to display the graph on the form. Can somebody help? Thanks in advance.
View 2 Replies
View Related
