By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,831 Members | 1,031 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,831 IT Pros & Developers. It's quick & easy.

Show more relevant fields on report

P: n/a
Ray
I have following nest crosstab query for a report and need more relevant
fields on the each record. However, this query 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
Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
"Ray" <No****************@GMail.com> wrote in message news:<3b*************@individual.net>...
I have following nest crosstab query for a report and need more relevant
fields on the each record. However, this query failed to link other query
to show more related fields. I appreciate any suggestion how I can
accomplish it.

PARAMETERS [Forms]![frmSwitchboard]![Combo26] Long, 8<-------------
Severe parse Error. Please simplify your queries to the essential
when posting. This is the best approach to gain insight in what's wrong
------------->8
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,
I'd prefer to have another table with columns (Rating, Implication).
TqryScore1_Crosstab.FactoryID
FROM TqryScore1_Crosstab INNER JOIN qryFactory ON
TqryScore1_Crosstab.FactoryID = qryFactory.FactoryID
ORDER BY qryFactory.Factory;

What solutions did you try? Generally speaking, joining more than 2 columns
goes like FROM (a INNER JOIN b) INNER JOIN c. I.e. using parentheses.
Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.