Excel Formula into access

Question

I have an excel formula that i need to make into a access formula using a query. 

Now i have a excel  formula :

=IFERROR(SUM(COUNTIFS('Import Report'!E:E,{"CM","MMNRO","MMROI","PMCM","PMINS","PMOR","PMPDM","PMREG","PMRT"},'Import Report'!K:K,">="&(A2-1/24),'Import Report'!K:K,"<"&(C2+23/24),'Import Report'!F:F,"*COMP*",'Import Report'!O:O,">="&(A2-1/24),'Import Report'!O:O,"<"&(C2+23/24),'Import Report'!X:X,"LEWMXTCHE"))/SUM(COUNTIFS('Import Report'!E:E,{"CM","MMNRO","MMROI","PMCM","PMINS","PMOR","PMPDM","PMREG","PMRT"},'Import Report'!K:K,">="&(A2-1/24),'Import Report'!K:K,"<"&(C2+23/24),'Import Report'!X:X,"LEWMXTCHE")),0)

I have a query put it's not working right.

You can download it from address: http://s000.tinyupload.com/?file_id=70403559699337344234

Answer 1

A relational database is very different from a spreadsheet in both concepts and methodologies, so in most cases an Excel worksheet formula will not translate easily to an expression in an Access query.  You need to express the logic of the formula in a way which an Access query can evaluate.  I don't think I can do more than outline some basic principles and refer you to some examples.

In Access a query is restricted either by a WHERE clause and/or a HAVING clause.  The difference is that a WHERE clause operates on the base data before any aggregation, e.g. to find all customers WHERE City = "London" (all csutomers located in London).  A HAVING clause on the other hand operates after aggregation e.g.  all customers HAVING SUM(OrderAmount) >= 10000 (all customers who have made orders totalling 10,000 GBP or more).

In both cases the WHERE or HAVING clause must do one simple thing only, which is to evaluate to TRUE on the basis of the values in each row, or subset of rows in the case of a HAVING clause, in a table.  So the expression must be one which is capable, using Boolean logic, of evaluating to TRUE or FALSE.  To this end it uses the usual equality or non-equality operators (= and <>) along with Boolean operators AND, OR, NOT and , rarely, XOR.

The order of evaluation of sub-expressions within an overall expression is controlled by the use of parentheses.  This is particularly important when combining the Boolean AND and OR operations in an expression.  Often an OR operation will need to be evaluated independently of an AND operation, in which case it will be parenthesized, e.g. WHERE (City = "London" OR City = "Glasgow) AND ProductSupplied = "Widget" (all suppliers in London or Glasgow who supply widgets.

You'll find examples of how Boolean expressions can be used in queries, using Northwind data as an example, in Boolean.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

In your case it sounds like you want to divide the number of rows which match one expression by the number of rows which match another expression.  For this you might well need to use a query which counts the rows matching the first expression, and then divide that by the result of a subquery which counts the rows which match the other expression.  The following is a simple, albeit nonsensical, example of a query using Northwind data which returns the percentage of customers in Chicago as a ratio of those customers who are the owner of their company:

SELECT COUNT(*)/

    (SELECT COUNT(*)

     FROM Customers

     WHERE [Job Title] = "Owner") * 100 AS Percentage

FROM Customers

WHERE City = "Chicago";

The answer BTW is 33.33%, the ratio being 2:6.

Answer 2

have excel calculation That i need to put in access but dont know how any ideas would be great.

for the caculations i need to put this in access

(%) PM Compliance [>95%] – Staus (COMP or FCOMP)) And ActLAborHours(>0) And ActStartDate

                                                       Divided By

                                                          WorkType(PMINS0PMOR,PMPDM,PMPDM,PMRT) and Status(Not

                                                            "Canceled") and TargetStartDate.

I know i have to do this in query but not sure how to set it up.

Answer 3

Is this the query you are referring to?

SELECT MaximoReportT.Description, MaximoReportT.Location, MaximoReportT.WorkType, MaximoReportT.Status, MaximoReportT.LFC_assestDept, MaximoReportT.AssignedGroup, Count(MaximoReportT.WorkOrderID) AS Percentage

FROM MaximoReportT

GROUP BY MaximoReportT.Description, MaximoReportT.Location, MaximoReportT.WorkType, MaximoReportT.Status, MaximoReportT.LFC_assestDept, MaximoReportT.AssignedGroup

HAVING (((MaximoReportT.WorkType) In ("CM","MMNRO","MMROI","PMCP","PMINS","PMOR","PMPDM","PMREG","PMRT")));

If so, I assume that it's the Percentage column whose values you are trying to compute, in which case between what two values is the ratio which you are trying to express as a percentage?