IFF function with Multi Criteria

Im trying to create a nested IFF function to turn this excel function in access. The basic excel formula is =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)
jeannie JonesAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
Can you explain, in words, what that 'basic' Excel formula is meant to do?:)

Also, in Access which fields would correspond to the Excel columns being used in the formula?
0
jeannie JonesAuthor Commented:
my access table fields are as follows:
2018-09-18_13-13-39.jpg
0
jeannie JonesAuthor Commented:
the formula that i have written on paper is this.
 status (COMP or FCOM) and ActualLaborhours(>0) and ActualStart date ( what ever dates the user puts in on the form{startDate and EndDate  [divided By\]

WorkType(PMINS,PMOR,PMPDM,PMREG,PMRT) and Status( not = CAN) And TargetStartDate(what ever dates the user puts in on the form{startDate and EndDate )
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

PatHartmanCommented:
When you have compound conditions, you must repeat the field name so:

IIf((Status = "COMP" OR Status = "FCOM") AND (ActualLaborHours >0 And ActualStartDate Between Forms!yourform!StartDate And Forms!yourform!EndDate), SomeField / Nz(SomeOtherField, IIf(.... next If, true action, false action)  As CalcResult

Your explanation is fuzzy so I'm not going to try to build the rest of the IIf()

Just remember 2 things:
1. repeat the column name if you want to compare to multiple values
2. Enclose the parts of the criteria in parentheses to ensure that the expression is evaluated as you intend.

Access follows standard order of operation rules.

 a and b or c or d  is evaluated as (a and b) or c or d

whereas you probably want  a and (b or c or d)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Hamed NasrRetired IT ProfessionalCommented:
Upload the excel worksheet with few rows of data.
0
jeannie JonesAuthor Commented:
i have uploaded  some sample data that is in my excel sheet
testdata.xlsx
0
Hamed NasrRetired IT ProfessionalCommented:
Im trying to create a nested IFF function to turn this excel function in access

Open in new window


So please upload the working excel worksheet showing a working function.
Fill the first row of the sheet with names for headings  as in the access table.
I assume you want to transfer the excel sheet to access table.

Also upload a demo of any database related to the issue.
0
jeannie JonesAuthor Commented:
can you show me a rough draft of what this would look like in access Query designer please
0
Hamed NasrRetired IT ProfessionalCommented:
To be able to do that, one should understand the problem.
My previous request will make it easy for me to help.
0
PatHartmanCommented:
I showed you how it would look in  a query:

Select .... my sample, ....
0
Hamed NasrRetired IT ProfessionalCommented:
look like in access Query designer
  1. Name first row in excel with suitable column names.
  2. Get external data from Excel, and link to specified excel sheet.
  3. Open query designer and use the linked table as record source.
  4. Select related fields to create the select statement.
0
jeannie JonesAuthor Commented:
I did that already and its not coming up with the right number that is on the excel sheet.
0
Hamed NasrRetired IT ProfessionalCommented:
I did that already and its not coming up with the right number that is on the excel sheet.

The question will bloat if we keep uncovering new information after few comments.

So speed up the solution to the issue, upload what you have done, in access and excel, to investigate.
Try that with a spreadsheet with few (3-5) columns.
0
jeannie JonesAuthor Commented:
SELECT Count(*) AS LEW
FROM MaximoReportT
WHERE (((MaximoReportT.Status)="COMP") AND ((MaximoReportT.ActualLaborHours)>"00:00") AND ((MaximoReportT.ActualStartDate) Between [Enter the Start Date] And [Enter the End Date])) OR (((MaximoReportT.Status)="FCOMP"));

UNION SELECT Count(*) AS LEW
FROM MaximoReportT
WHERE ((WorkType) In ("PMINS","PMOR","PMPDM","PMREG","PMRT")) AND ((Status)<>"CAN") AND (TargetStartDate) Between [Enter the Start Date] And [Enter the End Date];
 
Here the query i got so far and it returns a value off 133/608  now i need to get those numbers into a percentage. i have attached the database it self
MaximoAnylyzer11--Edit-.accdb
0
Hamed NasrRetired IT ProfessionalCommented:
Thanks for the uploaded file.
It has many objects.
I ran union query UnionPMCOMPQ, and got:
LEW
0
1950

 so what shall I look for and what to expect,
0
jeannie JonesAuthor Commented:
run the query that says Union
0
jeannie JonesAuthor Commented:
and now i trying to figure out from the union query how i can get it to calculate the percentage between those numbers
0
Hamed NasrRetired IT ProfessionalCommented:
I did.
What is the expected result?

Upload the excel sheet. Make sure the column titles are the same as the excel table.
0
jeannie JonesAuthor Commented:
all I am trying do Is get this in access to have the same calculations as the dash sheet in excel
DASH-Details---Simple.xlsm
0
Hamed NasrRetired IT ProfessionalCommented:
Thanks!
I was expecting to narrow down the scope.

Anyway I'll give it a try sometime later.

In the mean time other experts may join in.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.