R
Ray
I have following nest crosstab query and failed to link other query to show
more related fields. I appreciate any suggestion how I can accomplish it.
PARAMETERS [Forms]![frmSwitchboard]![Combo26] Long,
[Forms]![frmSwitchboard]![Combo28] Long;
SELECT qryFactory.Factory, TqryScore1_Crosstab.[1], TqryScore1_Crosstab.[2],
TqryScore1_Crosstab.[3], TqryScore1_Crosstab.[4], IIf([1] And [2] And [3]
And [4] Is Not
Null,Format(([1]*20/100)+([2]*30/100)+([3]*20/100)+([4]*30/100),"0"),IIf([1]
And [2] Is Not Null,Format(([1]*55/100)+([2]*45/100),"0"),IIf([2] And [3] Is
Not Null,Format(([2]*45/100)+([3]*55/100),"0")))) AS [Average Score],
IIf(Nz([Average Score])="","U",IIf([Average Score] Between 90 And
100,"A",IIf([Average Score] Between 80 And 89,"B",IIf([Average Score]
Between 65 And 79,"C",IIf([Average Score] Between 50 And 64,"D",IIf([Average
Score] Between 1 And 49,"E")))))) AS Rating1, IIf([1] And [2] Is Not Null
And [3] Is Null,"P",IIf([2] And [3] Is Not Null And [1] Is Null,"P")) &
[Rating1] AS Rating,
IIf([Rating]="A","Excellent",IIf([Rating]="B","Good",IIf([Rating]="C","Moderate",IIf([Rating]="D","Poor",IIf([Rating]="E","Unsatisfactory",IIf([Rating]="U","Unclassified",IIf([Rating]="PA","Provisionally
Excellent",IIf([Rating]="PB","Provisionally
Good",IIf([Rating]="PC","Provisionally
Moderate",IIf([Rating]="PD","Provisionally
Poor",IIf([Rating]="PE","Provisionally Unsatisfactory"))))))))))) AS
Implication, TqryScore1_Crosstab.FactoryID
FROM TqryScore1_Crosstab INNER JOIN qryFactory ON
TqryScore1_Crosstab.FactoryID = qryFactory.FactoryID
ORDER BY qryFactory.Factory;
Thanks,
Ray
more related fields. I appreciate any suggestion how I can accomplish it.
PARAMETERS [Forms]![frmSwitchboard]![Combo26] Long,
[Forms]![frmSwitchboard]![Combo28] Long;
SELECT qryFactory.Factory, TqryScore1_Crosstab.[1], TqryScore1_Crosstab.[2],
TqryScore1_Crosstab.[3], TqryScore1_Crosstab.[4], IIf([1] And [2] And [3]
And [4] Is Not
Null,Format(([1]*20/100)+([2]*30/100)+([3]*20/100)+([4]*30/100),"0"),IIf([1]
And [2] Is Not Null,Format(([1]*55/100)+([2]*45/100),"0"),IIf([2] And [3] Is
Not Null,Format(([2]*45/100)+([3]*55/100),"0")))) AS [Average Score],
IIf(Nz([Average Score])="","U",IIf([Average Score] Between 90 And
100,"A",IIf([Average Score] Between 80 And 89,"B",IIf([Average Score]
Between 65 And 79,"C",IIf([Average Score] Between 50 And 64,"D",IIf([Average
Score] Between 1 And 49,"E")))))) AS Rating1, IIf([1] And [2] Is Not Null
And [3] Is Null,"P",IIf([2] And [3] Is Not Null And [1] Is Null,"P")) &
[Rating1] AS Rating,
IIf([Rating]="A","Excellent",IIf([Rating]="B","Good",IIf([Rating]="C","Moderate",IIf([Rating]="D","Poor",IIf([Rating]="E","Unsatisfactory",IIf([Rating]="U","Unclassified",IIf([Rating]="PA","Provisionally
Excellent",IIf([Rating]="PB","Provisionally
Good",IIf([Rating]="PC","Provisionally
Moderate",IIf([Rating]="PD","Provisionally
Poor",IIf([Rating]="PE","Provisionally Unsatisfactory"))))))))))) AS
Implication, TqryScore1_Crosstab.FactoryID
FROM TqryScore1_Crosstab INNER JOIN qryFactory ON
TqryScore1_Crosstab.FactoryID = qryFactory.FactoryID
ORDER BY qryFactory.Factory;
Thanks,
Ray