Queries :: Divide Profit Over Turnover To Show Margin - Division By Zero Error
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 Replies
ADVERTISEMENT
Apr 25, 2012
I am receiving the following error when printing different reports to several different printers in Access 2007: "The section width is greater than the page width, and there are no items in the additional space, so some pages may be blank. For example, the report width may be wider than the page width." According to my co-workers this issue began immediately following the upgrade to version 2007 from 2003.
View 1 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
Jun 22, 2007
I have made a query with to take the percent difference of two mileages and then if they are greater than 2.5% to put a 1 or if not a 0. I try and put a 1 in criteria in order to only see the ones that are changed by greater than 2.5% and I get the divide by zero error. Any ideas how to fix this problem.
Here is my IFF statement
diff: IIf((Abs(([Mile1]-[Mile2]))/[Mile1]>=0.025),1,0)
View 14 Replies
View Related
Sep 12, 2006
How can I avoid a divide by zero error in an MS Access Query?
View 2 Replies
View Related
Aug 25, 2005
Hi all.
I have three subforms on my main form. They all display salary details from different sources, and each subform has 2 calculated fields in the form footer to show the average salary and the number of records on which the average has been calculated.
I then have a field on the main form to calculate the average of the averages which works OK unless one of the subforms has null or zero in it, in which case it returns no information.
Can anyone suggest a way of bypassing this divide by zero error? The code I'm using to calculate the average of averages is as follows:
Me!Text44 = ((Me![Frm1 subform].Form!text4 * Me![Frm1subform].Form!Text6) + (Me![Frm2 subform].Form!text4 * Me![Frm2 subform].Form!Text6) + (Me![Frm3 subform].Form!Text6 * Me![Frm3 subform].Form!Text8)) / (Me![Frm1 subform].Form!Text6 + Me![Frm2 subform].Form!Text6 + Me![Frm3 subform].Form!Text8)
Thanks for any help you can provide...
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
Aug 9, 2015
I am trying to divide each unique value in a column by the total column; so sales by total sales.
View 4 Replies
View Related
May 26, 2015
I have an alias named [Net Income]
[URL]...
I want to divide it's value by the sum of its values through another alias [%]. How to do it?
%:=[Net Income]/Sum([Net Income]) Not working
getting an error saying:
"You tried to execute a query that does not include the specified expression 'SR_NO' as part of an aggregate function."
View 4 Replies
View Related
Jul 31, 2014
Simple division, I thought! I'm dividing a Sum of hours by Pay Periods to determine the average of missing hours for year-to-date. Most of the division works perfectly, and some doesn't and appears to be related to values of 1 or less than one.The formula: [Sum]/[Pay Periods].Sum relates to total missing hours and pay periods are total pay periods for the year so far.What comes out wrong:
1 (hr) / 12 (pay periods) = 8.33, when it should be .0833
0.5 / 12 = 4.166 when it should be .0416
What is right is everything else, starting with 1.5 hrs
1.5 / 12 = 0.125
What do I need to do to make all of the values divide correctly?
View 3 Replies
View Related
Mar 17, 2013
I'm struggling with the correct syntax for this, but ideally what I want to achieve is:
SELECT (SELECT Count(Status) FROM P_T WHERE 'criteria are met') / (SELECT Count(Status) FROM P_T WHERE 'different criteria are met')
They are both fairly simplistic select statements, so I imagine this is not that difficult to achieve, but I'm not sure how to structure the query.
View 6 Replies
View Related
Feb 6, 2014
I want to take the sum of all records 'cost' i.e.
record 1 - 2
record 2 - 4
record 3 - 3
I want to take the sum and put it in a calculated field and use it to divide it by a predefined value 'no of responses.'
So I make the field in the query -> Calcfield: (Sum([cost])/[responses])
I am pretty new to this and i don't understand why this won't work? Is it something to do with the row Group by (total.)
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
Jan 10, 2006
I know in access 2003 i can use:
printer.leftmargin = 0
but in access 2000 there is no printer object. Is there a way of setting the margin with vba from access 2000?
View 5 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
May 8, 2007
Ok, so this one's been driving me batty for some time now. When I create a form in a new database, I can resize the design window to my heart's content, without any adverse effect to the form's rendered size. However, I also work with another database that was not created by me, and whenever I create a new form, the size of the form is entirely dependent on the size of the design window. This is extremely aggravating, as I have to pixel-by-pixel shrink the form renders the correct size. Is there an option somewhere in access or Form formatting to turn this "functionality" off, so that a form will only render the size that I designated to it, and not to its design window?
View 1 Replies
View Related
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 2 Replies
View Related
Aug 23, 2005
I'm trying to trouble-shoot an undocumented datbase built by someone else and running into a recurring problem. She has it set up where a macro runs several queries. When these macros are run, the queries cause "Division by Zero" errors which stops the macro in its tracks. The following is a query that causes this error, but by looking at this query, I see no place where a division takes place. I'm assumming that another porblem is occuring and Access just states that it's division by zero error when it's actually something else.
1) Has anyone seen a "Division by Zero" error when running a query when it's actually something else causing the problem?
2) Can anyone see from the query what could possibly cause this and give me a hint as to a good way to trouble-shoot these queries to possibly handle whatever problem is occurring.
Thanks,
The query is as follows:
SELECT [Buyer Category Fiscal Period for Report].FiscalMonth, [Buyer Category Fiscal Period for Report].FiscalYear, BuyerCategory.Buyer, BuyerCategory.Description, BuyerCategory.CategoryID, [008c GMROI by Category].[Category GMROI], [016c MTD GM by Category].[MTD GM], [017c YTD GM by Category].[YTD GM], [010c Turns by Category].[Category Turns], [005c MTD Cogs by Category qry].SumOfNetCogs, [004c YTD Cogs by Category qry].SumOfNetCogs, [006c MonthEnd Inv by Category qry].MonthEndInv, [014c GMROI Buyer Category].BuyerCategoryGMROI, [018c MTD GPM by Category].[Buyer Cat MTD GM], [019c YTD GPM by Category].[Buyer Cat YTD GM], [015c Turns Buyer Category].[BuyerCategory Turns], [005c MTD Cogs by Category qry].SumOfNetSales AS [MTD NDS], [004c YTD Cogs by Category qry].SumOfNetSales AS [YTD NSD], [Buyer Category Fiscal Period for Report].EndDate
FROM ((((((((((((BuyerCategory LEFT JOIN dbo_Category ON BuyerCategory.CategoryID = dbo_Category.CategoryID) LEFT JOIN [008c GMROI by Category] ON BuyerCategory.CategoryID = [008c GMROI by Category].CategoryID) LEFT JOIN [016c MTD GM by Category] ON BuyerCategory.CategoryID = [016c MTD GM by Category].CategoryID) LEFT JOIN [017c YTD GM by Category] ON BuyerCategory.CategoryID = [017c YTD GM by Category].CategoryID) LEFT JOIN [010c Turns by Category] ON BuyerCategory.CategoryID = [010c Turns by Category].CategoryID) LEFT JOIN [005c MTD Cogs by Category qry] ON BuyerCategory.CategoryID = [005c MTD Cogs by Category qry].CategoryID) LEFT JOIN [004c YTD Cogs by Category qry] ON BuyerCategory.CategoryID = [004c YTD Cogs by Category qry].CategoryID) LEFT JOIN [006c MonthEnd Inv by Category qry] ON BuyerCategory.CategoryID = [006c MonthEnd Inv by Category qry].CategoryID) LEFT JOIN [018c MTD GPM by Category] ON BuyerCategory.CategoryID = [018c MTD GPM by Category].CategoryID) LEFT JOIN [019c YTD GPM by Category] ON BuyerCategory.CategoryID = [019c YTD GPM by Category].CategoryID) LEFT JOIN [015c Turns Buyer Category] ON BuyerCategory.Buyer = [015c Turns Buyer Category].Buyer) LEFT JOIN [014c GMROI Buyer Category] ON BuyerCategory.Buyer = [014c GMROI Buyer Category].Buyer) LEFT JOIN [Buyer Category Fiscal Period for Report] ON (BuyerCategory.Buyer = [Buyer Category Fiscal Period for Report].Buyer) AND (BuyerCategory.CategoryID = [Buyer Category Fiscal Period for Report].CategoryID)
GROUP BY [Buyer Category Fiscal Period for Report].FiscalMonth, [Buyer Category Fiscal Period for Report].FiscalYear, BuyerCategory.Buyer, BuyerCategory.Description, BuyerCategory.CategoryID, [008c GMROI by Category].[Category GMROI], [016c MTD GM by Category].[MTD GM], [017c YTD GM by Category].[YTD GM], [010c Turns by Category].[Category Turns], [005c MTD Cogs by Category qry].SumOfNetCogs, [004c YTD Cogs by Category qry].SumOfNetCogs, [006c MonthEnd Inv by Category qry].MonthEndInv, [014c GMROI Buyer Category].BuyerCategoryGMROI, [018c MTD GPM by Category].[Buyer Cat MTD GM], [019c YTD GPM by Category].[Buyer Cat YTD GM], [015c Turns Buyer Category].[BuyerCategory Turns], [005c MTD Cogs by Category qry].SumOfNetSales, [004c YTD Cogs by Category qry].SumOfNetSales, [Buyer Category Fiscal Period for Report].EndDate
ORDER BY BuyerCategory.Buyer;
View 5 Replies
View Related
Jun 28, 2007
I have a field in the a query with the following:
% OVER NB: 1-([Monthly MTA Table]!NB/[main inventory table]!PRICE)
I then format the column in properties to percent
When I try to put anything in the criteria I get an error "Division by zero"
I want to only the records that are greater then 80%
View 1 Replies
View Related
Apr 9, 2008
I have a query with the following calc. but it has an error of 'division by o' when I run it. Some of the figures may be null or 0 within the fields and I still want it to return a % figure result
I know its probably the iif function but am not sure how to use it in this instance.
Process %: Sum([Process volume]*[se]/[minutes worked])
Thanks
View 3 Replies
View Related
Nov 2, 2007
I have a table named "Patients" with a field named "Code" and another field named "Patient" which contains the full name (LastName space FirstName) "Chaviatsos Nick" for example.
I want to make an other table from the table "Patients" with three fields:
1. A field named "Code"
2. A field named "LastName" which will contain the Last Name of the field "Patient" of the table 'Patients" and
3. A field named "FrstName" which will contain the Frst Name of the field "Patient" of the table 'Patients"
How Can I manage this?
View 7 Replies
View Related
Nov 8, 2007
Please help me with the round function. I want .5 to round to 1.
Here is an example of my data: (18+18+18+20)/4 = 18.5 rounds to 18. I want it to round to 19.
I used the following expression:
RoundACT Composite Score: Round((([Column1]+[Column2]+[Column3]+[Column4])/4),0)
Thank you.
View 6 Replies
View Related
Apr 19, 2008
Hi all
I've been using the following code to show Random records from an Access Database, the only problem with it is that every now and again it throws a "division by 0 error" any ideas how i can fix?
Code:strsql_videos = "select top 5 * from items ORDER BY RND(id)*(id*1000) MOD datePart('s', NOW()), id;"set rs_videos = server.createobject("ADODB.recordset")rs_videos.open strsql_videos, conn_videos, 3, 1, adCmdText
Iam hoping its possible with a few lines of code rather than some elaborate fix :P
View 4 Replies
View Related
Jul 30, 2013
So, I have some complicated calculations I am aliasing in my SQL query. My end goal would be to sort by my final calculation from all the other calculations I have performed but I am running in to some issues. I know I can't sort by referencing the alias itself without saying order by (column number). My calculations are usually pretty small (around .001 and up). Whenever I order by the column number I would like to use (select... from... where...order by 73) I get a "Division by zero" error in access. I figured this was an issue with how small the calculations were, so I multiplied by 1000000, then ordered by that column number. Still division by zero...I went ahead and changed all 0 values to 1, still division by zero. There are no zero values in the column itself, it's either 1 or something in the thousands. I can order by other columns, just not the one I want to use. I don't want to have to re-do my entire calculations in my order by clause.
View 3 Replies
View Related
Mar 13, 2006
Just wondering, if this is possible and if so how?
I got query results and would like to divide a figure from the number of results shown..
so if 4 results are shown from the query then i would like to divide 100 by 4, and if 5 results showed then divide it by 5?
View 5 Replies
View Related
