Counting records in query (1 Viewer)

[MattioMatt]

Counting records in query but returning a defined score rather than count value

I'm in need of some guidance..

I'm currently constructing a query. At the moment I'm pulling AppID and App Name from the 'tbl_App_Inventory' table - I'm wanting to link pull a field [MissedSLA] which is a text field for simply 'y' or 'n' from another table, tbl_App_Issues.

What I'd like to do is to have an expression that looks to count the number of 'Y's per app, if it is higher than zero to then give me a score of '9'. If there are no 'Y's then to look at 'N's, again counting to see if there is any, if the count is higher than Zero to then return '2' and for for a false to return '0'.

I've tried a number of ways to acheive this without success so far. I've tried importing the table tbl_App_Issues into my query with tbl_App_Inventory and linking via AppID but what I'm finding is, it will then list duplicates of the app name due to there being multiple issues per app. This is not the desired outcome, I've like to have one listing of my app name and then the column to be 'SLAScore' and just return 9/2/0 based on the above.

I've also tried to remove the tbl_App_Issues from my query and use an expression with IIF statements but I find it returns '9' no matter what I do.

I've tried this expression whilst linking the AppID for both tables in my query:

IIf(DCount("[tbl_App_Issues].[MissedSLA]", "tbl_App_Issues", "[tbl_App_Issues].[MissedSLA]='Y'")>0,9,IIf(DCount("[tbl_App_Issues].[MissedSLA]", "tbl_App_Issues", "[tbl_App_Issues].[MissedSLA]='N'")>0,2,0))

The result here is, it simply just returns 9 for everything even if there is no issues which have missed SLA.

I then created a seperate query to use the to count the 'y' 'n' by using the Count under Total. I used the AppID, App Name and MissedSLA field twice, one to display and one to do the CountofMissedSLA - thinking I could then use the expression to look up that why

I then tried to get my output this way:

IIf(Dlookup("[MissedSLA]","Qry_Count_Missed_SLA","[Qry_Count_Missed_SLA].[AppID] = [tbl_App_Inventory].[AppID]")="Y",9,IIf(Dlookup("[MissedSLA]","Qry_Count_Missed_SLA","[Qry_Count_Missed_SLA].[AppID] = [tbl_App_Inventory].[AppID]")="N",2,0))

This however just returns '0' for everything.

I've attached an example of the data I'm using and the desired output to my query.

 

[isladogs]

You do like making things unnecessarily complicated

First of all the syntax for domain function like DCount is
DCount("field name", "table name", "criteria")

First get the count for that AppID.
As you've already made a query, may as well use that

Dim I As Integer
I = DLookup("MissedSLA","[Qry_Count_Missed_SLA]","AppID=" & [tbl_App_Inventory].[AppID])

Personally I don't understand why its not better to store the actual count
But if you really want the 9/2/0 approach....

IIf(I>1,[tbl_App_Issues].SLAScore=9,IIf(I=1,[tbl_App_Issues].SLAScore=2,0))

NOTE; You don't strictly need the [] if you have underscores.
However you would find life much easier if you scrapped underscores and just use CamelCase e.g. tblAppIssues, qryCountMissedSLA

 

[MattioMatt]

Hi ridders!

Thank so much for your feedback - making things complicated is often the feedback I get! I don't intentionally go down that route it just seems to just happen.

For your first code snippet, I'm unsure where that would go. It seems to be VBA? I was trynig to do all this within a query - I hadn't got to a form page. Apologies if I misunderstanding..

Thank you for your your feedback RE table/query names, I'll add the adjustments to my task list.

 

[MattioMatt]

Additionally, (Forgive me, could just be me misunderstanding) is the code going to return the results? At the moment my Query is returning AppID, App Name, Missed SLA & Count if MissedSLA - however there is a row for each app and each Y/N if there is one.
I've updated my spreadsheet with a tab to show what the query is outputting at the moment.

 

[isladogs]

Hi

Sorry I've been offline most of the day.
My original code was assuming the use of VBA but easy enough to do using queries

It was quicker for me to produce a sample db. See attached

2 tables with sample data & 2 queries.
As requested no VBA
I've included your 9/2/0 method though I still think it's unhelpful.
I would suggest omitting that and just use the count.

Study what I've done & if it isn't clear get back to me

 

[Mark_]

Question, as it seems relevant.

WHY do you need to convert your results to "9", "2", or "0"? What is the business rule or purpose?

 

[MattioMatt]

Thanks so much for your help ridders, trully appreciate all the support so far!

The attachment looks fantastic! Although I'm not sure it's caculating correctly.
For example, AppID has two App Issues at 'N' so I would like it to be scoring a '2' but is returning a zero.
App ID 3 & 4 is returning a '2' when I would like it to return a '9' as there is a MissedSLA issue.

I sincerely apologise if I've not explained my issued well enough.
I'd like for any app with any app issue that has missed the SLA to score a 9 (even if there are issues that have not missed the SLA - missing the SLA will be prioritised in the scoring.
If an app has app issues and none of them have missed SLA then it returns a score of '2'
If an app has no app issues then it scores a zero.

I've had a look at the queries you've created to see if I could tweak them by removing the criteria in qryCountMissedSLA, that doesn't change the output. I then moved to the logic that it's counting if it's Y or N.
At this point I'm struggling to think as it's late so tiredness is coming into it.

 

[MattioMatt]

Question, as it seems relevant.

WHY do you need to convert your results to "9", "2", or "0"? What is the business rule or purpose?

I'm creating a risk map and need the scores per app to use as part of a calculation to plot. It's a little complicated to explain how I'm using the scores, but this scoring system helps me to plot on the map.

 

[Mark_]

Is this what you are looking for?

I made one query to return ANY app that has a Y. One for ANY app that has a N. I then used a third to put them together. Remember to keep track of which way you join them (include the app but also include Y or N). Then it was a simple IIF to return the 9,2, or 0.

 

[isladogs]

Just read this again ....

What I'd like to do is to have an expression that looks to count the number of 'Y's per app, if it is higher than zero to then give me a score of '9'. If there are no 'Y's then to look at 'N's, again counting to see if there is any, if the count is higher than Zero to then return '2' and for for a false to return '0'.

Sorry I'd misunderstood & done something different.
I believe Mark has done exactly what you asked for

 

[MattioMatt]

Wow thank you so much Ridders & Mark_! This has worked perfectly!

This is exactly what I was looking for! Thank you so much!!! I truly appreciate all your efforts!