Selecting "variable" columns

[StevieRayB]

Hello,

Please see the attached WORD file. I need a query that selects data from "variable columns", and what I mean by that is in the attachment. I've placed the table, with sample data and the 2 desired query results in the attachment as they wouldn't display correctly in the preview.

Please help, I've tried all day and haven't even come close. Thank you.

 

[CameronM]

Is there any logic behind which columns a particular Rep has the values stored in?

For instance, why does Rep 1 have values in Qual-02 & Qual-04 but Rep 2, has values in Qual-02 &+ Qual-03? Yet no records use Qual-01 or Qual-05?

If there is no particular logic, you might have to utilise IIF statements to select from columns that have values greater than 0.

save the following as qryTableA

SELECT TableA.Rep, TableA.Transaction, IIf([Qual-01]=0,[Qual-02],[Qual-01]) AS QualParamA, IIf([Qual-03]=0,[Qual-04],[Qual-03]) AS QualParamB
FROM TableA
ORDER BY TableA.Rep, TableA.Transaction;

then create the grouped query

SELECT qyTablea.Rep, Count(qyTablea.Transaction) AS CountOfTransaction, Avg(qyTablea.QualParamA) AS AvgOfQualParamA, Avg(qyTablea.QualParamB) AS AvgOfQualParamB
FROM qyTablea
GROUP BY qyTablea.Rep;

 

[StevieRayB]

CameronM, THANKS!!!! This works perfectly!

We're actually preparing for a study. The logic for the columns are example quality scores, assigned to different call representatives for targeted times to complete a customer call for the study. For the example data, Rep 1 will have target times of 2 and 4 minutes, while Rep 2 will have target times of 2 and 3 minutes after starting the conversation. The scores will be manually entered by some poor soul who has the duty of listening to and scoring the recorded calls, and I'd hate to be that person! The actual table has Qual_01 through Qual_10 for 1 through 10 minutes and will have more Reps and targeted time data in it. In the end, this report will be used to determine the min and max time a representative will be expected to be on the phone with a customer. Again, thank you for your time.

 

[CameronM]

Glad I could help.

 

[StevieRayB]

The final result for all 10 quality columns. I made a "Time" master table with 2 columns, TimeA and TimeB joined by "Rep" to "TableA". Thanks again for guiding me.


SELECT [TableA].ID, [TableA].REP, [Time].TimeA, [Time].TimeB, IIf([TimeA]=1,[Qual_01],(IIf([TimeA]=2,[Qual_02],IIf([TimeA]=3,[Qual_03],IIf([TimeA]=4,[Qual_04],IIf([TimeA]=5,[Qual_05],IIf([TimeA]=6,[Qual_06],IIf([TimeA]=7,[Qual_07],IIf([TimeA]=8,[Qual_08],IIf([TimeA]=9,[Qual_09],IIf([TimeA]=10,[Qual_10]))))))))))) AS QualA,
IIf([TimeB]=1,[Qual_01],(IIf([TimeB]=2,[Qual_02],IIf([TimeB]=3,[Qual_03],IIf([TimeB]=4,[Qual_04],IIf([TimeB]=5,[Qual_05],IIf([TimeB]=6,[Qual_06],IIf([TimeB]=7,[Qual_07],IIf([TimeB]=8,[Qual_08],IIf([TimeB]=9,[Qual_09],IIf([TimeB]=10,[Qual_10]))))))))))) AS QualB
FROM [TableA] INNER JOIN [Time] ON [TableA].Rep = [Time].Rep;