Modules & VBA :: Profit And Loss Graph On Monthly Basis
Sep 12, 2013
I'm trying to make a graph of Monthly profit and loss
I've got 2 tables: Invoices and Purchases.
Invoices contains the fields:
InvoiceTotal and InvoiceDate
Purchases contains the fields:
PurchaseTotal and PurchaseDate
I know I need to take one from the other to create my profit figure but what I can't figure out is how to incorporate the dates.
InvoiceDate and PurchaseDate might be different but both occurred in the same month, so I would like to sum my PurchaseTotal and InvoiceTotal during this month.
I am looking automation through VBA for the calculation of HR employees turnover on monthly basis, i have a query showing fields ..
employee name joining date department section employee left (Yes/No) Left date
Now I am looking for;
i) Opening strength: Total employees strength through joining date with criteria of department and section wise at month start
ii) Closing Strength: Total employees strength through joining date with criteria of department and section wise at month end (subtracting the employees left through employee left field y/n)
iii)Left Employees: number of employees left at the end of each month with the criteria of department and section wise
iv) Turnover: The number of left employees will be divided by the dividend of (sum of opening & closing employees divided by 2)
Me having a db which is having a student table which is keeping all student info, second i have a Van table which is keeping van info. I wanted to know how it is possible to calculate monthly van fee for each student on monthly basis which db do it itself based on system date/time and calculate monthly required fee for each student. I also would like to know that if a student do not pay in a month then checking the next month or checking unpaid student through date criteria it displays all due amount may be in sum.
I have a table with certain parameters in, and I need to update it in a monthly basis with new costs. Basically, its a list of component costs which change on a monthly basis, and the query/report needs to pull the latest version. But, the old costs need to be kept for legacy/comparison purposes.
I have a form containing client demographics. One of the fields is the DateOfBirth. I would like to control the visibility of a subform based on this DateOfBirth field.
I have tried using the code below:
Private Sub Form_Current() If DateOfBirth > 11 / 11 / 1994 Then subEligibility.Visible = False End Sub
This works when the form is opened for the first record, but the visibility does not change as I scroll through the rest of the records.
I have some graphs on a report that I need to dynamically position. The user selects which graphs he wants to put in and then the reports repositions the graphs based on input.
I've been using the following code to move the graph:
reports!rpoly.gGPCMWbyDRI.top = 3.625
which compiles, but moves the chart to the "0" position. I tried using a string ("3.625"""), or a stint ("3.625") but neither worked.
I have a split form with graphs in the upper design section and the table of the data that the graphs represent in the data view underneath. I would like to make the graphs dynamic with the data from the forms if the data is filtered in design view.
I know how to get the filter from the data view by using the .filter and I would like to use that as part of an SQL statement in my Rowsource for the graphs to dynamically change the graphs when a filter is used. My problem is the data is returned with .filter function returns with quotation marks (example below), and because I'm wanting to use that data in a string to change my rowsource the quotes need to be replaced with an apostrophe. How can I change the quotes to an apostrophe, or is there a better way to "filter" a graph?
Example: Returned from .filter ([FrmTable].[CurrentABCS]="A")
I am trying to print out a report that displays the jobs(records) from the dates "User entry" to "User Entry" and then display the total profit at the bottom. I need help setting up the query or report to display the line items (each record) and the display the total at the bottom of the form between a user defined time period. (ex: 5/5/04 - 6/5/04) It is a "Gross Profit" report.
I have a field called ProfitToDate (currency) and a field called Profit. Each record will have a dollar/cent amount posted in the Profit field. I want the ProfitToDate field to be updated for each record. In other words, I want the ProfitToDate field to show the totaled amount of the Profit field for all preceding records and the current record. How do I calculate this in my form design? Please give an example and keep it simple. I’m new at this stuff.
I am trying to update the range for the graph automatically and I'm calling this from Access because my data exists in Access. I've attached a sample file.what I am trying to do:
1) click on the button 2) button will update the usedrange in "trending" tab 3) have the graph's data range based on the usedrange.
Code:
Dim xlApp As Object Set xlApp = CreateObject("Excel.Application") xlApp.Visible = True xlApp.Workbooks.Open "C:Documents and SettingsgjiaDesktopmetric_DEV.xlsx", True, False
[code]...
note: the columns will grow bigger to the right, which is dynamic, so I've used usedrange method, but the code fails on the usedrange line saying the object or method is not being supported...
i have a table with 7 columns. Assume THE columns are a,b,c,d,e,f,g. In this table there is only one row and each column is given a number value. For example, a is 1, b is 5 and so forth. Can i make a pie graph in access with these 7 labels, and show there totals in the graphs?
Whenever I double-click a graph in a form that is being viewed it opens up MS Graph in edit form. This is confusing/ugly/etc and I don't want it to do that.
I tried adding some code to the double click event to make it do some other action (bringing up some graph modification tools I created), but as soon as those are closed MS Graph will then open up in edit form.
How to turn this off? It's incredibly unprofessional.
I'm creating an accounts package..I've used access chart wizard to create a chart that shows total gross income per month.This displays correctly but the months start at January and end in December. It would be more useful if the months could start and end for the financial year. The syntax generated by access for the current implementation is:
Code: SELECT (Format([DatePaid],"MMM 'YY")),Sum([TotalPaid]) AS [SumOfTotalPaid] FROM [Q_AllCust_Gross] GROUP BY (Year([DatePaid])*12 + Month([DatePaid])-1),(Format([DatePaid],"MMM 'YY"));
How do I edit this to make say September my start date?
I've created an access chart to show total sales ordered by customers.
I'm using a form with 5 comboboxes to select 5 particular customers from a customer table and pass this information into a query.
This query is then used on a report to create the graph.
Finally there is a button on my form that prints this report.
My problem is that the chart displays the customers in alphabetical order and I would like to order the customers numerically based on total sales value.
If I change my query so that the total sales order by is ascending then when I run the query from the report I am asked to enter a parameter value for the total sales field. Entering nothing and pressing ok simply returns the same graph I would have got had I not changed the order option at all.
*The customer filed in the report is set to group by and my totals field is set to sum - but I need both of these set to produce the graph.
We are submitting an Access db table with ID as primary key to an outside company for processing. We export it as csv file and that's how it comes back.
After importing it back, we noticed that our IDs, which previously had 'gaps' as a result of deleted records, had been changed in to a new continuous sequence (Autonumbered). This, of course, completely destroys our database since related tables depend on IDs being unchangeable.
We ***suspect*** that the IDs are being destroyed during the process of re-importing the csv file. We are using the Access import wizzard.
Access msg: "Access recommends that you define a Primary Key for your new table". We then are given the following options:
1Let Access ADD primary key 2Choose my own primary key 3No primary key
During Option 1, the ID column is displayed and high-lighted. But we do NOT want that option since it offers to ADD a primary key, whereas we already have one (ID). At most, we want to 'declare' or 'define' a primary key, but we do not want Access to ADD one.
So we try Option 2, hoping that this will give us the chance to CHOOSE ID as our primary key. But the moment we select that option, the ID column disappears. We are not given the chance to select ID as our primary key.
So we try Option 3, hoping to re-instate ID as primary key as soon as we have an Access table again. But the moment we select this option, the ID column disappears.
-----------------------
If we GO THROUGH with Option 1, we notice that Access has renumbered our ID, the IDs of all our 'deleted' records have been re-assigned. Or so it appears.
If we GO THROUGH with Option 3, the resulting table does not contain an ID column at all.
Of course, the damage may have been done by the outside company which processed our file, but if so, we have to prove it, and we have to take measures to avoid it.
I'm a network manager at a school running 5 windows server 2003 servers (3 of which are DCs) and 350 windows XP clients. Network speed and traffic is excellent and most of the time the only software used is microsoft office. Most computers run office 2003 but some have office xp.
Here are a few scenarios of the problems:
Scenario 1 A teacher looks back at some work of a student who left a year or so ago to find all work as expected however all access databases are empty (no tables, forms etc. Just blank database files)
Scenario 2 A student says they were working on an access file only moments ago and everything vanished (resulting in a blank database file). They claim the file has been there for weeks, and has had data in it for weeks. They have been working on it a number of times a week. When I look through the backups, the database file exists but is empty on every occasion.
This problem occurs over 4 ICT suites (120 computers) at random and is always a different user. There seem to be no patterns. We get the Scenario 2 problem about once or twice a month, although sometimes a lot more and sometimes a lot less.
Any ideas or comments?! (I need some response even if it's not very helpful!)
I am running a very simple query that divides profit over turnover to show the margin. I have many queries throughout my database that do this, here is my little formula;
Margin: Round(([profit]/[Turnover])*100,2)
Now, this runs just fine but if I put in a criteria of <10 I get the division by zero error. The reason I am confused is that there are no zeros or error values or even negative values in either of the profit or turnover columns?
Also I have an almost identical query in another database that has a <5 criteria in it and it works a peach.
I have searched but all I am getting is the usual definition of the division by zero error.
I have a A2007 database that is split to a shared server that is experiencing unidentified network problems. While working in the front-end users receive the following message periodically:
Quote:Your network access was interrupted. To continue close the database and then open it again.If the users are working in a form including queried data, the data is lost and the file crashes. This happens even though the front-end is on the user's computer and no tables are currently being queried. My question is why must the network be stable even though the database is split and queries are not occurring? The form's record source is a table though. But even with this the record source connection is not needed until the form is closed.
First thing: I have a navigation form that leads through 5 subforms on my database. One is a data entry form, the rest are various informative forms with explanations, images and other text mainly. In this scenario, a user enters data on the first tab but doesn't finish it. The user clicks over to check up on some guidelines on the other tabs and then comes back to the first tab for find that all of the fields he entered reset and he lost everything.
Is there a way of preventing this from happening? Plan A would be for me to allow the user to switch around tabs without losing the data he or she entered into textboxes or dropdowns. Plan B would be for me to have a popup that appears when he tries to switch tabs that warns him of data loss and that he should save before moving tabs.
PS: One other thing: I have the standard Access wizard search button on my first tab that allows the user to search records. This works well, but I'd like for the search box to close immediately after a search turns up a matching record. This seems odd, but the users requested it because manually closing the search box after every search can add up to a lot of wasted time.
I have a multivalue text field called "Groups" that is generated through a relationship with another table called "Comments". When I set this up I left the field as a text field when it should have been a number field, and now I can't use the database with sharepoint, because the field needs to be a number field.
My Question is there a way to convert it without loosing all my data, as there are over 5000 records?
My Table with the multivalue field is called "Contacts", the field is called "Groups".
I tried update and append queries but I must be doing something wrong....
