Link a query to show more fields

[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

 

[Duane Hookom]

I see a number of issues that should be fixed:
- do yourself a favor and change the names Combo26 and Combo28
- what are you attempting to do with:
IIf([1] And [2] And [3] And [4] Is Not Null...
Do you expect this expression to check each of these fields for Null?
- I expect Average Score is numeric or Null so don't compare it like
Nz([Average Score])="",...
Try
Nz([Average Score], -1)=-1,...
- you are calculating "Rating" in the query with an unmanageable expression.
Either create public function or use a "Rate Lookup Table" with ranges
and "letters"
- I never use a calculated column name as part of an expression in another
column

 

[Ray]

Duane,

Thanks for your response. My answers to your questions are as below.

I see a number of issues that should be fixed:
- do yourself a favor and change the names Combo26 and Combo28

Would you mind advising me why I need to change the combo box names.

- what are you attempting to do with:
IIf([1] And [2] And [3] And [4] Is Not Null...
Do you expect this expression to check each of these fields for Null?

I need to do some different calculations under pre-defined conditions by
using nest IIf statement.

- I expect Average Score is numeric or Null so don't compare it like
Nz([Average Score])="",...
Try
Nz([Average Score], -1)=-1,...

It may be an empty string.

- you are calculating "Rating" in the query with an unmanageable
expression.
Either create public function or use a "Rate Lookup Table" with ranges
and "letters"

This is due to my limited knowledge. If you are willing to shed some light
on how to accomplish it, I deeply appreciate it.

- I never use a calculated column name as part of an expression in another
column

It works to my task so far and unsure if it causes me further development of
more features demanded by the users.

--
Duane Hookom
MS Access MVP

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

 

[Duane Hookom]

I would use descriptive names of controls because I would not want anyone
else to be put in a position of maintaining an application that doesn't
follow some type of naming convention. I would not like to be perceived as
either inexperienced or lazy.

IIf([1] And [2] And [3] And [4] Is Not Null... only checks to see if [4] is
not null. The other fields are checked for either 0 or some other number.
The "Is Not Null" only applies to [4], not the others.

Are you suggesting that [Average Score] is not numeric? If it is numeric, it
will not contain an empty string. It might be Null.

I'll get back later on the lookup table unless someone else has the time to
respond before.

--
Duane Hookom
MS Access MVP

Duane,

Thanks for your response. My answers to your questions are as below.

I see a number of issues that should be fixed:
- do yourself a favor and change the names Combo26 and Combo28

Would you mind advising me why I need to change the combo box names.

- what are you attempting to do with:
IIf([1] And [2] And [3] And [4] Is Not Null...
Do you expect this expression to check each of these fields for Null?

I need to do some different calculations under pre-defined conditions by
using nest IIf statement.

- I expect Average Score is numeric or Null so don't compare it like
Nz([Average Score])="",...
Try
Nz([Average Score], -1)=-1,...

It may be an empty string.

- you are calculating "Rating" in the query with an unmanageable
expression.
Either create public function or use a "Rate Lookup Table" with ranges
and "letters"

This is due to my limited knowledge. If you are willing to shed some
light on how to accomplish it, I deeply appreciate it.

- I never use a calculated column name as part of an expression in
another column

It works to my task so far and unsure if it causes me further development
of more features demanded by the users.

--
Duane Hookom
MS Access MVP

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

 

[Ray]

Duane,

Thanks for your further guidance.

I would use descriptive names of controls because I would not want anyone
else to be put in a position of maintaining an application that doesn't
follow some type of naming convention. I would not like to be perceived as
either inexperienced or lazy.

Point taken. Will take it into account for future programming.

IIf([1] And [2] And [3] And [4] Is Not Null... only checks to see if [4]
is not null. The other fields are checked for either 0 or some other
number. The "Is Not Null" only applies to [4], not the others.

You are right. Thanks for your guidance. I have amended it and others.

Are you suggesting that [Average Score] is not numeric? If it is numeric,
it will not contain an empty string. It might be Null.

I will examine all possibilities with your suggestion in mind.

I'll get back later on the lookup table unless someone else has the time
to respond before.

Appreciate your time and helping and look forward to hearing from you.

Ray

--
Duane Hookom
MS Access MVP

Duane,

Thanks for your response. My answers to your questions are as below.

I see a number of issues that should be fixed:
- do yourself a favor and change the names Combo26 and Combo28

Would you mind advising me why I need to change the combo box names.

- what are you attempting to do with:
IIf([1] And [2] And [3] And [4] Is Not Null...
Do you expect this expression to check each of these fields for Null?

I need to do some different calculations under pre-defined conditions by
using nest IIf statement.

- I expect Average Score is numeric or Null so don't compare it like
Nz([Average Score])="",...
Try
Nz([Average Score], -1)=-1,...

It may be an empty string.

- you are calculating "Rating" in the query with an unmanageable
expression.
Either create public function or use a "Rate Lookup Table" with ranges
and "letters"

This is due to my limited knowledge. If you are willing to shed some
light on how to accomplish it, I deeply appreciate it.

- I never use a calculated column name as part of an expression in
another column

It works to my task so far and unsure if it causes me further development
of more features demanded by the users.

--
Duane Hookom
MS Access MVP


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

 

[Duane Hookom]

I would probably create a lookup table that provides the rating based on
your test (I assume that is what is creating your columns) and min and max
values. Your Title, min and max field values in the table would be like:
Excellent 1 49
Good 50 64
Moderate 65 79
.....
Beyond that, it is very difficult to
--
Duane Hookom
MS Access MVP

Duane,

Thanks for your further guidance.

I would use descriptive names of controls because I would not want anyone
else to be put in a position of maintaining an application that doesn't
follow some type of naming convention. I would not like to be perceived as
either inexperienced or lazy.

Point taken. Will take it into account for future programming.

IIf([1] And [2] And [3] And [4] Is Not Null... only checks to see if [4]
is not null. The other fields are checked for either 0 or some other
number. The "Is Not Null" only applies to [4], not the others.

You are right. Thanks for your guidance. I have amended it and others.

Are you suggesting that [Average Score] is not numeric? If it is numeric,
it will not contain an empty string. It might be Null.

I will examine all possibilities with your suggestion in mind.

I'll get back later on the lookup table unless someone else has the time
to respond before.

Appreciate your time and helping and look forward to hearing from you.

Ray

--
Duane Hookom
MS Access MVP

Duane,

Thanks for your response. My answers to your questions are as below.


I see a number of issues that should be fixed:
- do yourself a favor and change the names Combo26 and Combo28

Would you mind advising me why I need to change the combo box names.

- what are you attempting to do with:
IIf([1] And [2] And [3] And [4] Is Not Null...
Do you expect this expression to check each of these fields for Null?

I need to do some different calculations under pre-defined conditions by
using nest IIf statement.

- I expect Average Score is numeric or Null so don't compare it like
Nz([Average Score])="",...
Try
Nz([Average Score], -1)=-1,...

It may be an empty string.

- you are calculating "Rating" in the query with an unmanageable
expression.
Either create public function or use a "Rate Lookup Table" with
ranges
and "letters"

This is due to my limited knowledge. If you are willing to shed some
light on how to accomplish it, I deeply appreciate it.

- I never use a calculated column name as part of an expression in
another column

It works to my task so far and unsure if it causes me further
development of more features demanded by the users.


--
Duane Hookom
MS Access MVP


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