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.
so the query I'm looking for would look like:
| Month | SumofPurchaseTotal | SumofInvoiceTotal | Profit |
I would then graph month and profit so my company could compare it's monthly performance ...
View Replies
ADVERTISEMENT
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
Mar 10, 2015
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)
View 2 Replies
View Related
Jul 23, 2013
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.
View 2 Replies
View Related
Feb 25, 2014
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.
TblMbM Layout (titles)
-Unique_ID
-title
-code
-Feb Cost
-March Cost
At the moment, there are no costs in the march column, but i know next week i will need to update tblMbM with the latest costs.
View 3 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 27, 2013
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.
View 4 Replies
View Related
Jun 19, 2013
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.
View 2 Replies
View Related
Oct 7, 2013
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")
What I need:
([FrmTable].[CurrentABCS]='A')
View 2 Replies
View Related
Oct 8, 2004
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.
Thank you!
View 1 Replies
View Related
Aug 20, 2004
Access ’97 ----
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.
Thanks very much.
Papa
View 4 Replies
View Related
Mar 17, 2015
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...
View 3 Replies
View Related
Oct 8, 2014
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?
View 1 Replies
View Related
Jun 5, 2013
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.
View 5 Replies
View Related
Sep 9, 2013
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?
View 2 Replies
View Related
Mar 3, 2014
I'm currently stuck on trying to create a worksheet which calculate the balance remaining at the end of each Monthly Payment Period.
Monthly Payment Period: 1 to 300
Initial Balance: balance outstanding at the beginning of the Monthly Payment Period.
Interest: calculated by multiplying the Initial Balance by the APR (5%) divided by 12
Balance Remaining: calculated by adding the Interest and subtracting Total Monthly Payment.
The final Balance Remaining should be exactly £0.00.
View 1 Replies
View Related
Jul 29, 2013
Is it possible to update the data for a chart graph in a word document from Access using VBA?
View 4 Replies
View Related
Mar 3, 2007
My wife is slimming, I would like to record weekly results.
How do I make a new entry compare with her ideal weight and flag up her progress.
e.g. ideal weight =10 entry 1 13 entry 2 12.9 etc
Can I get the cell holding the new entry to refer to the ideal weight and flag up the difference each time I update the weight for the new week?
View 3 Replies
View Related
Apr 22, 2013
I want to make a query to figure out profit made by month year and room type.
View 1 Replies
View Related
Sep 10, 2013
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.
View 3 Replies
View Related
Apr 3, 2006
Loss of Primary Key when importing csv file
Hi All,
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.
Any comments and advice?
Thanks.
Adrian
View 3 Replies
View Related
Jun 8, 2007
Hi,
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!)
Thanks,
Jonathan
View 12 Replies
View Related
Apr 10, 2013
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.
View 5 Replies
View Related
Aug 10, 2012
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.
View 2 Replies
View Related
Aug 7, 2014
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.
View 8 Replies
View Related
Oct 21, 2012
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....
View 2 Replies
View Related
