Query Too Complex

E

execsys

I have a query that works just fine until I attempt to set a filter on
it.

Here is the SQL (sorry, this is quite ugly :)

SELECT SkillList.Character, SkillList.Skill, SkillsTable.Ability,
SkillsTable.Untrained, (SELECT [Modifier] FROM [AbilityTable] WHERE
[CharacterTable].[Str] = [AbilityTable].[Ability]) AS StrMod, (SELECT
[Modifier] FROM [AbilityTable] WHERE [CharacterTable].[Dex] =
[AbilityTable].[Ability]) AS DexMod, (SELECT [Modifier] FROM
[AbilityTable] WHERE [CharacterTable].[Con] = [AbilityTable].[Ability])
AS ConMod, (SELECT [Modifier] FROM [AbilityTable] WHERE
[CharacterTable].[Int] = [AbilityTable].[Ability]) AS IntMod, (SELECT
[Modifier] FROM [AbilityTable] WHERE [CharacterTable].[Wis] =
[AbilityTable].[Ability]) AS WisMod, (SELECT [Modifier] FROM
[AbilityTable] WHERE [CharacterTable].[Cha] = [AbilityTable].[Ability])
AS ChaMod,
IIf([Ability]="STR",[StrMod],IIf([ability]="DEX",[DexMod],IIf([Ability]="CON",[ConMod],IIf([Ability]="INT",[IntMod],IIf([Ability]="WIS",[WisMod],IIf([ability]="CHA",[ChaMod],0))))))
AS TotalMod, (SELECT [Ranks] FROM [CharacterSkills] WHERE
[CharacterTable].[Character] = [CharacterSkills].[LookupCharacter] AND
[SkillList].[Skill] = [CharacterSkills].[LookupSkill]) AS SkillRanks,
IIf([SkillRanks]>0,[SkillRanks]+[TotalMod],IIf([Untrained]="Yes",[TotalMod],0))
AS TotSkillRanks FROM (SkillList INNER JOIN SkillsTable ON
SkillList.Skill=SkillsTable.Skill) INNER JOIN CharacterTable ON
SkillList.Character=CharacterTable.Character;

Now basically this returns a table of records that does some
calculations and sometimes the calculation results in 0. When the
TotSkillRanks is calculated to be 0, I want the record to disappear
from the query results. However, when I add ">0" to the criteria of the
query in the TotSkillRanks column or when I do it via Filter - the
calculated fields all blank out after a moment of displaying what
appears to be the correct results.


Can anyone help?
 
G

Guest

The root problem is normalization. It's obvious by the nexted IIf statements,
you have columns where there should be another table. Or two.

As for the immediate problem, you can see the data for a little while then
it goes blank or something. That is an indication that Access is hitting
something that it can't evaluate properly. For example your IIf statement
might be returning a Null value. Also there can be problems with the timing
and execution plan of the SQL statement. Try the SQL below to see it it might
work.

Even if it does, please contenplate redesigning your tables. What happens if
someone adds another Ability? You'll need to do a lot of rewriting I'm sure.

SELECT SkillList.Character,
SkillList.Skill,
SkillsTable.Ability,
SkillsTable.Untrained,
(
SELECT [Modifier]
FROM [AbilityTable]
WHERE [CharacterTable].[Str] = [AbilityTable].[Ability]) AS StrMod,
(
SELECT [Modifier]
FROM [AbilityTable]
WHERE [CharacterTable].[Dex] = [AbilityTable].[Ability]) AS DexMod,
(
SELECT [Modifier]
FROM [AbilityTable]
WHERE [CharacterTable].[Con] = [AbilityTable].[Ability]) AS ConMod,
(
SELECT [Modifier]
FROM [AbilityTable]
WHERE [CharacterTable].[Int] = [AbilityTable].[Ability]) AS IntMod,
(
SELECT [Modifier]
FROM [AbilityTable]
WHERE [CharacterTable].[Wis] = [AbilityTable].[Ability]) AS WisMod,
(
SELECT [Modifier]
FROM [AbilityTable]
WHERE [CharacterTable].[Cha] = [AbilityTable].[Ability]) AS ChaMod,
IIf([Ability]="STR",
[StrMod],
IIf([ability]="DEX",
[DexMod],
IIf([Ability]="CON",
[ConMod],
IIf([Ability]="INT",
[IntMod],
IIf([Ability]="WIS",
[WisMod],
IIf([ability]="CHA",
[ChaMod],
0)))))) AS TotalMod,
(
SELECT [Ranks]
FROM [CharacterSkills]
WHERE [CharacterTable].[Character] = [CharacterSkills].[LookupCharacter]
and [SkillList].[Skill] = [CharacterSkills].[LookupSkill]) AS SkillRanks,
IIf([SkillRanks]>0,
[SkillRanks]+[TotalMod],
IIf([Untrained]="Yes",
[TotalMod],
0)) AS TotSkillRanks
FROM (SkillList INNER JOIN SkillsTable
ON SkillList.Skill=SkillsTable.Skill) INNER JOIN CharacterTable
ON SkillList.Character=CharacterTable.Character
WHERE
IIf([Ability]="STR",
[StrMod],
IIf([ability]="DEX",
[DexMod],
IIf([Ability]="CON",
[ConMod],
IIf([Ability]="INT",
[IntMod],
IIf([Ability]="WIS",
[WisMod],
IIf([ability]="CHA",
[ChaMod],
0)))))) > 0 ;

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


[email protected] said:
I have a query that works just fine until I attempt to set a filter on
it.

Here is the SQL (sorry, this is quite ugly :)

SELECT SkillList.Character, SkillList.Skill, SkillsTable.Ability,
SkillsTable.Untrained, (SELECT [Modifier] FROM [AbilityTable] WHERE
[CharacterTable].[Str] = [AbilityTable].[Ability]) AS StrMod, (SELECT
[Modifier] FROM [AbilityTable] WHERE [CharacterTable].[Dex] =
[AbilityTable].[Ability]) AS DexMod, (SELECT [Modifier] FROM
[AbilityTable] WHERE [CharacterTable].[Con] = [AbilityTable].[Ability])
AS ConMod, (SELECT [Modifier] FROM [AbilityTable] WHERE
[CharacterTable].[Int] = [AbilityTable].[Ability]) AS IntMod, (SELECT
[Modifier] FROM [AbilityTable] WHERE [CharacterTable].[Wis] =
[AbilityTable].[Ability]) AS WisMod, (SELECT [Modifier] FROM
[AbilityTable] WHERE [CharacterTable].[Cha] = [AbilityTable].[Ability])
AS ChaMod,
IIf([Ability]="STR",[StrMod],IIf([ability]="DEX",[DexMod],IIf([Ability]="CON",[ConMod],IIf([Ability]="INT",[IntMod],IIf([Ability]="WIS",[WisMod],IIf([ability]="CHA",[ChaMod],0))))))
AS TotalMod, (SELECT [Ranks] FROM [CharacterSkills] WHERE
[CharacterTable].[Character] = [CharacterSkills].[LookupCharacter] AND
[SkillList].[Skill] = [CharacterSkills].[LookupSkill]) AS SkillRanks,
IIf([SkillRanks]>0,[SkillRanks]+[TotalMod],IIf([Untrained]="Yes",[TotalMod],0))
AS TotSkillRanks FROM (SkillList INNER JOIN SkillsTable ON
SkillList.Skill=SkillsTable.Skill) INNER JOIN CharacterTable ON
SkillList.Character=CharacterTable.Character;

Now basically this returns a table of records that does some
calculations and sometimes the calculation results in 0. When the
TotSkillRanks is calculated to be 0, I want the record to disappear
from the query results. However, when I add ">0" to the criteria of the
query in the TotSkillRanks column or when I do it via Filter - the
calculated fields all blank out after a moment of displaying what
appears to be the correct results.


Can anyone help?
Click to expand...
 
D

David Cox

can it be that you are eliminating all of the records where totalskillranks
=0 on input, which just might be all of them?
 
D

David Cox

It looks like an imbalance between courage and wisdom to me. :->

Jerry Whittle said:
The root problem is normalization. It's obvious by the nexted IIf
statements,
you have columns where there should be another table. Or two.

As for the immediate problem, you can see the data for a little while then
it goes blank or something. That is an indication that Access is hitting
something that it can't evaluate properly. For example your IIf statement
might be returning a Null value. Also there can be problems with the
timing
and execution plan of the SQL statement. Try the SQL below to see it it
might
work.

Even if it does, please contenplate redesigning your tables. What happens
if
someone adds another Ability? You'll need to do a lot of rewriting I'm
sure.

SELECT SkillList.Character,
SkillList.Skill,
SkillsTable.Ability,
SkillsTable.Untrained,
(
SELECT [Modifier]
FROM [AbilityTable]
WHERE [CharacterTable].[Str] = [AbilityTable].[Ability]) AS StrMod,
(
SELECT [Modifier]
FROM [AbilityTable]
WHERE [CharacterTable].[Dex] = [AbilityTable].[Ability]) AS DexMod,
(
SELECT [Modifier]
FROM [AbilityTable]
WHERE [CharacterTable].[Con] = [AbilityTable].[Ability]) AS ConMod,
(
SELECT [Modifier]
FROM [AbilityTable]
WHERE [CharacterTable].[Int] = [AbilityTable].[Ability]) AS IntMod,
(
SELECT [Modifier]
FROM [AbilityTable]
WHERE [CharacterTable].[Wis] = [AbilityTable].[Ability]) AS WisMod,
(
SELECT [Modifier]
FROM [AbilityTable]
WHERE [CharacterTable].[Cha] = [AbilityTable].[Ability]) AS ChaMod,
IIf([Ability]="STR",
[StrMod],
IIf([ability]="DEX",
[DexMod],
IIf([Ability]="CON",
[ConMod],
IIf([Ability]="INT",
[IntMod],
IIf([Ability]="WIS",
[WisMod],
IIf([ability]="CHA",
[ChaMod],
0)))))) AS TotalMod,
(
SELECT [Ranks]
FROM [CharacterSkills]
WHERE [CharacterTable].[Character] = [CharacterSkills].[LookupCharacter]
and [SkillList].[Skill] = [CharacterSkills].[LookupSkill]) AS SkillRanks,
IIf([SkillRanks]>0,
[SkillRanks]+[TotalMod],
IIf([Untrained]="Yes",
[TotalMod],
0)) AS TotSkillRanks
FROM (SkillList INNER JOIN SkillsTable
ON SkillList.Skill=SkillsTable.Skill) INNER JOIN CharacterTable
ON SkillList.Character=CharacterTable.Character
WHERE
IIf([Ability]="STR",
[StrMod],
IIf([ability]="DEX",
[DexMod],
IIf([Ability]="CON",
[ConMod],
IIf([Ability]="INT",
[IntMod],
IIf([Ability]="WIS",
[WisMod],
IIf([ability]="CHA",
[ChaMod],
0)))))) > 0 ;

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


[email protected] said:
I have a query that works just fine until I attempt to set a filter on
it.

Here is the SQL (sorry, this is quite ugly :)

SELECT SkillList.Character, SkillList.Skill, SkillsTable.Ability,
SkillsTable.Untrained, (SELECT [Modifier] FROM [AbilityTable] WHERE
[CharacterTable].[Str] = [AbilityTable].[Ability]) AS StrMod, (SELECT
[Modifier] FROM [AbilityTable] WHERE [CharacterTable].[Dex] =
[AbilityTable].[Ability]) AS DexMod, (SELECT [Modifier] FROM
[AbilityTable] WHERE [CharacterTable].[Con] = [AbilityTable].[Ability])
AS ConMod, (SELECT [Modifier] FROM [AbilityTable] WHERE
[CharacterTable].[Int] = [AbilityTable].[Ability]) AS IntMod, (SELECT
[Modifier] FROM [AbilityTable] WHERE [CharacterTable].[Wis] =
[AbilityTable].[Ability]) AS WisMod, (SELECT [Modifier] FROM
[AbilityTable] WHERE [CharacterTable].[Cha] = [AbilityTable].[Ability])
AS ChaMod,
IIf([Ability]="STR",[StrMod],IIf([ability]="DEX",[DexMod],IIf([Ability]="CON",[ConMod],IIf([Ability]="INT",[IntMod],IIf([Ability]="WIS",[WisMod],IIf([ability]="CHA",[ChaMod],0))))))
AS TotalMod, (SELECT [Ranks] FROM [CharacterSkills] WHERE
[CharacterTable].[Character] = [CharacterSkills].[LookupCharacter] AND
[SkillList].[Skill] = [CharacterSkills].[LookupSkill]) AS SkillRanks,
IIf([SkillRanks]>0,[SkillRanks]+[TotalMod],IIf([Untrained]="Yes",[TotalMod],0))
AS TotSkillRanks FROM (SkillList INNER JOIN SkillsTable ON
SkillList.Skill=SkillsTable.Skill) INNER JOIN CharacterTable ON
SkillList.Character=CharacterTable.Character;

Now basically this returns a table of records that does some
calculations and sometimes the calculation results in 0. When the
TotSkillRanks is calculated to be 0, I want the record to disappear
from the query results. However, when I add ">0" to the criteria of the
query in the TotSkillRanks column or when I do it via Filter - the
calculated fields all blank out after a moment of displaying what
appears to be the correct results.


Can anyone help?
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Complex Query not working on report 2
System Resource Exceeded/Query Too Complex 4
Query Too Complex with Total line 8
Another query too complex problem 3
Query Too Complex, Too many Characters? 18
query too slow 1
Query is too complex for Access 2007 ? 2
Slow Query 3

Top