How to Populate ListBoxes Based on the Column Value of Another Listbox

[ssanfu]

@Micron
Yeah, it used to get tiresome but I'm used to it now. "Call me anything, just don't call me late for supper!"

It came about because, in college, I would watch every episode of Kung Fu. So I was given a nickname of Sanfu.
"Sanfu" was taken in other web sites, so I added my first name initial to get ssanfu.

[ssanfu]

RT (Mark?), sorry, it took a while to figure out what was going on. I have a fix, but it is not elegant.

So, one problem is that, in your inline queries, you have a line

"FROM TblSOPTmp, TblSOPTmp AS TblSOPTmp_1 " & _

Because there is no type of join between tables, this creates what is known as a "Cartesian Join" or "Cartesian product".


Another problem: you are using a totals query, but you have a date field included in the query. When you group by a date field, any date that does not match another date results in a separate record (row).


My work-a-round does not include dates - don't know if that is a problem, but ......

I first made a query for the YD (yesterday??) numbers/employees. Once I got the query (qryEmp_YD) returning correct values, I had to create the 2nd query.
The 2nd query (qryEmp_LW) is for LW (last week??) numbers/employees.

Then I created a union query (qryUnionQry_YD_LW) to get the records in one query. Ended up with a lot of rows because of the "CallDate" field - deleted the "CallDate" field from the 2 base queries (but included it as a criteria field).
The last query is a totals query (qryTotalCombine_YD_LW) to consolidate rows. But there were decimal place problems - modified the totals query bu adding the FORMAT function back into the SQL.

The last step was to copy the SQL of the totals query (qryTotalCombine_YD_LW) and add it to the VBA for the after update event of "CmboTeam" combo box.

BTW: the query "qryTotalCombine_YD_LW" was for testing. I could be deleted if you want (because the query SQL is in code).

Now the trick is going to be to get the values from the form and still get valid results..



What do you think? Close to what you want?

[RunTime91]

Kung Fu eh?

That was 'must see' TV when I was a kid - Loved that show!!

If you haven't already seen, try to find a copy of a movie titled: Billy Jack, from back in the early '70s. Well worth the ~90 minutes

I like Snafu as that all too often describes the world within which I live

I chose RunTime91 as when I first began coding - It seemed every time I clicked a button - That error would pop up - Never the results I needed - Just RunTime91

But moving forward - Steve it is - and yes, my name is Mark from Sacramento CA.

However, I have another name for you at the moment - Captain Awesome!!

That is not 'close' to what I needed - it is exactly what I was trying to do!! THANK YOU!!!

I've heard of Cartesian Joins, but I do not know how to recognize when I've coded them.

What's interesting about that one is, - It was the SQL rendering of what I built in the Access Grid.

I have much to learn - Sigh

But true to form from the experts on this site - this experience not only helped me - it taught me

And that is everything I could hope for

Thank you again Steve - so very much!

Mark

[June7]

So what should LW values be? Which LW - AHT-LW or ACW-LW or SC-LW?

QA field does show 0.00.

[RunTime91]

Hey June ~

LW stands for Last Week. The Accronyms AHT, ACW & SC are call center measurments and stand for: Average Handle Time; Average Work Time & Schedule Compliance
YD stands for Yesterday. As for the QA (Quality) it is ok that it is 0.00 for now.

Steve - you were right - Getting your masterpiece to work with form control variables - not possible....

So, I took the weekly stats and rolled them up into a separate table.

I thought this would make it easier - A simple LEFT JOIN and we're home - No such luck...

Every query I have created will not pull all of the records from the new table (ScoreGen...) with any join

Can you have a look to see what I'm missing

the first two commented queries (after the one you wrote) on the AfterUpdate event, individually pull the correct data

But any effort to combine them with a Join or Union fails

The active query pulls correct data but doesn't pull the full population of agents

Thanks Steve for you continued help with this nightmare

[June7]

You said "but not accurate for the LW (LastWeek)". I was asking for clarification on which LW field you were referencing, not to explain the acronyms.

Query pulls all records that meet filter criteria. If you still want to see all 498 agents then would have to join filtered query to a dataset of all agents. Db does not have a table of agents. Is there such a table in your full db?

[RunTime91]

Hey June sorry ~ I misunderstood

If you populate the list box by selecting a team name (any team name) you will see each of the LW fields have the same data.

The total list count should be 12 agents - the query is only pulling 9, it isn't pulling 3 agents from the ScorecardGenerator table -

If you want to see the complete list of agents comment all the queries then un-comment the first query after the With statement.

Of course the data showing with those agents is just the LW data

The YD data comes from the SOP table and the LW data comes from the Scorecard table

If I could have used form control variables with Steve's query - I'd be out Christmas shopping

So, to your point June - while the query is pulling correctly as it is configured

I'm trying to reconfigure it to pull all the data from one table and all of the matching records from the other

I thought an Outer Join would work - thus far not even close

Thanks June

[RunTime91]

If I change the JOIN 'ON' criteria to 'TeamName' the query will pull the 12 agents but it pulls the same data in the YD field (which comes from the SOPTmp table) I have to be close.

Here is the query that pulls the correct agents but the incorrect YD data.

.List23.RowSource = "SELECT TblScGen.AgentName, TblScGen.TeamName, " & _
                        "FORMAT(Sum([TblSOPTmp].[HandleTime])/Sum([TblSOPTmp].[CallsHandled]),'###0') AS [AHT-YD], " & _
                        "TblScGen.AHT AS [AHT-LW], " & _
                        "FORMAT(Sum([TblSOPTmp].[CallWorkTime])/Sum([TblSOPTmp].[CallsHandled]),'###0') AS [ACW-YD], " & _
                        "TblScGen.AWT AS [AWT-LW], " & _
                        "FORMAT(Sum([TblSOPTmp].[SchCompliance])*1,'Percent') AS [SC-YD], " & _
                        "TblScGen.SchCompliance AS [SC-LW], " & _
                        "TblScGen.Quality " & _
                        "FROM TblScGen " & _
                        "LEFT JOIN " & _
                        "TblSOPTmp ON TblSOPTmp.TeamName = TblScGen.TeamName " & _
                        "WHERE TblScGen.TeamName = 'TC_MillerR' " & _
                        "AND TblSOPTmp.CallDate = #12/10/2019# AND TblScGen.WeekStarting = #12/2/2019# " & _
                        "GROUP BY TblScGen.AgentName, TblScGen.TeamName, TblScGen.AHT, " & _
                        "TblScGen.AWT, TblScGen.SchCompliance, TblScGen.Quality;"

Couple things - I changed the table name of the ScorecardGenerator to ScGen
Steve if you're still out there - I am going to adopt your method of setting up the query with a SQL string
But for now I just want to get this thing to work..

[June7]

Test query with query builder then when it provides desired output, replicate in VBA. Start simple. The following returns all 12 agents.

SELECT DISTINCT TblScorecardGenerator.WeekStarting, TblScorecardGenerator.AgentName, TblScorecardGenerator.TeamName
FROM TblSOPTmp RIGHT JOIN TblScorecardGenerator ON TblSOPTmp.EmpID = TblScorecardGenerator.ACD_ID
WHERE (((TblScorecardGenerator.WeekStarting)=#12/2/2019#) AND ((TblScorecardGenerator.TeamName)="TC_MillerR"));

Including CallDate criteria reduces output to 9 agents.

This returns all 12 agents.

SELECT TblScorecardGenerator.*, Q1.* FROM TblScorecardGenerator LEFT JOIN (
SELECT EmpID, EmpName, TeamName, Format(Sum([HandleTime])/Sum([CallsHandled]),'0') AS [AHT-YD], Format(Sum([CallWorkTime])/Sum([CallsHandled]),'0') AS [ACW-YD], Format(Sum([SchCompliance])*1,'Percent') AS [SC-YD], TblSOPTmp.CallDate
FROM TblSOPTmp
GROUP BY EMPID, TblSOPTmp.EmpName, TblSOPTmp.TeamName, TblSOPTmp.CallDate
HAVING (((TblSOPTmp.CallDate)=#12/10/2019#))) AS Q1 ON TblScorecardGenerator.ACD_ID=Q1.EmpID
WHERE TblScorecardGenerator.TeamName='TC_MillerR' AND WeekStarting=#12/2/2019#;

However, I just realized you are filtering by TblSOPTmp.TeamName, not TblScorecardGenerator.TeamName. So three agents do not meet that criteria. Rats!

[virgilio]

deleted. *Sorry, I thought I was starting a new thread, not commenting on this one. It has been a long day.

[June7]

Access has DateDiff function.

You should start your own thread with this issue, not hijack someone else's.

[RunTime91]

June... Absolutely Perfect...

I taylored it to fit - it produced perfect results with the hard coded criteria

So I inserted the form control variables and its throwing a Method or Data Member not Found error

Not sure it this has something to do with the sub query? When I put back the hard coded criteria once again it works perfectly

Here is what the query looks like in VBA

With Me
                    "SELECT TblScGen.AgentName, TblScGen.TeamName, Q1.[AHT-YD], TblScGen.AHT AS [AHT-LW], " & _
                    "Q1.[ACW-YD], TblScGen.AWT AS [ACW-LW], Q1.[SC-YD], TblScGen.SchCompliance AS [SC-LW], TblScGen.Quality " & _
                    "FROM TblScGen LEFT JOIN (SELECT EmpID, EmpName, TeamName, " & _
                    "FORMAT(Sum([HandleTime])/Sum([CallsHandled]),'0') AS [AHT-YD], " & _
                    "FORMAT(Sum([CallWorkTime])/Sum([CallsHandled]),'0') AS [ACW-YD], " & _
                    "FORMAT(Sum([SchCompliance])*1,'Percent') AS [SC-YD], TblSOPTmp.CallDate " & _
                    "FROM TblSOPTmp " & _
                    "GROUP BY EMPID, TblSOPTmp.EmpName, TblSOPTmp.TeamName, TblSOPTmp.CallDate " & _
                    "HAVING (((TblSOPTmp.CallDate)= #" & .TxtHuddleDate - 1 & "# )))  AS Q1 ON TblScGen.ACD_ID = Q1.EmpID " & _
                    "WHERE (((TblScGen.TeamName)= '" & .ComboTeam.Value & "') AND ((TblScGen.[WeekStarting])= #" & .TxtLastWeek & "# )) " & _
                    "ORDER BY TblScGen.AgentName;"
End With

The Form Control names are accurate - So I know it is not that... Let me know if you see something... Thank You

[June7]

The error is really on ComboTeam because actual name is CmboTeam.

Notice .ComboTeam is not in intellisense tips.

Fix the control name or the code. I would use cboTeam or cbxTeam.

Don't need .Value because that is default property.

[RunTime91]

Solved ~ Yep June - you saw it

Steve & June - I cannot Thank You both enough for sticking with me on this one.

Steve - I know you don't think your query is very 'elegent' but as a rookie - I think it is fascinating

Prior to this thread I didn't think what you did was even possible -

June - You saw immediately what I was doing wrong and, like everyone here it seems, you not only helped but you explained it

I learned a great deal from just this one thread - I hope others will as well

Ranman - You started this whole thing off and I thank you for always being out there...

Thanks again everyone - 'Til the next time

RT91