jeannie Jones
asked on
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('Imp ort Report'!E:E,{"CM","MMNRO", "MMROI","P MCM","PMIN S","PMOR", "PMPDM","P MREG","PMR T"},'Impor t Report'!K:K,">="&(A2-1/24) ,'Import Report'!K:K,"<"&(C2+23/24) ,'Import Report'!F:F,"*COMP*",'Impo rt Report'!O:O,">="&(A2-1/24) ,'Import Report'!O:O,"<"&(C2+23/24) ,'Import Report'!X:X,"LEWMXTCHE"))/ SUM(COUNTI FS('Import Report'!E:E,{"CM","MMNRO", "MMROI","P MCM","PMIN S","PMOR", "PMPDM","P MREG","PMR T"},'Impor t Report'!K:K,">="&(A2-1/24) ,'Import Report'!K:K,"<"&(C2+23/24) ,'Import Report'!X:X,"LEWMXTCHE")), 0)
ASKER
my access table fields are as follows:
2018-09-18_13-13-39.jpg
2018-09-18_13-13-39.jpg
ASKER
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 )
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,
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Upload the excel worksheet with few rows of data.
ASKER
i have uploaded some sample data that is in my excel sheet
testdata.xlsx
testdata.xlsx
Im trying to create a nested IFF function to turn this excel function in access
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.
ASKER
can you show me a rough draft of what this would look like in access Query designer please
To be able to do that, one should understand the problem.
My previous request will make it easy for me to help.
My previous request will make it easy for me to help.
I showed you how it would look in a query:
Select .... my sample, ....
Select .... my sample, ....
look like in access Query designer
- Name first row in excel with suitable column names.
- Get external data from Excel, and link to specified excel sheet.
- Open query designer and use the linked table as record source.
- Select related fields to create the select statement.
ASKER
I did that already and its not coming up with the right number that is on the excel sheet.
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.
ASKER
SELECT Count(*) AS LEW
FROM MaximoReportT
WHERE (((MaximoReportT.Status)=" COMP") AND ((MaximoReportT.ActualLabo rHours)>"0 0:00") AND ((MaximoReportT.ActualStar tDate) 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","P MREG","PMR T")) 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
FROM MaximoReportT
WHERE (((MaximoReportT.Status)="
UNION SELECT Count(*) AS LEW
FROM MaximoReportT
WHERE ((WorkType) In ("PMINS","PMOR","PMPDM","P
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
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,
It has many objects.
I ran union query UnionPMCOMPQ, and got:
LEW
0
1950
so what shall I look for and what to expect,
ASKER
run the query that says Union
ASKER
and now i trying to figure out from the union query how i can get it to calculate the percentage between those numbers
I did.
What is the expected result?
Upload the excel sheet. Make sure the column titles are the same as the excel table.
What is the expected result?
Upload the excel sheet. Make sure the column titles are the same as the excel table.
ASKER
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
DASH-Details---Simple.xlsm
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.
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.
Also, in Access which fields would correspond to the Excel columns being used in the formula?