Limits in queries 1

[jpl458]

Ihave the following expression in the QBE grid:

Facility: Switch(Val([PUB_PSHISTOR]![PSH-TC]) Between 1770 And 1776 Or Val([PUB_PSHISTOR]![PSH-TC]) Between 1500 And 1519 Or Val([PSH-TC])=1583,"Range",Val([PSH-TC]) Between 1001 And 1301,"WPGC",Val([PSH-TC]) Between 1520 And 1524,"Cages",Val([PSH-TC]) Between 1531 And 1536,"MiniGolf",Val([PSH-TC])=1537 Or Val([PSH-TC]) Between 1580 And 1582 Or Val([PSH-TC]) Between 1700 And 1710 Or Val([PSH-TC]) Between 1901 And 1922,"Misc",Val([PSH-TC]) Between 1600 And 1615,"lessons",Val([PUB_PSCODES]![PSC-TC])=1598,"SSP Cash Over",Val([PUB_PSCODES]![PSC-TC])=1599,"SSP Cash Short",Val([PUB_PSCODES]![PSC-TC])=1298,"Golf Cash Over",Val([PUB_PSCODES]![PSC-TC])=1299,"Golf Cash Short")

There are 10 "trancodes" that I am trying to give names for use in a pivot table. Initially I was using IIF but learned that there is a limit of 7 in an expression. I changed to Switch because there is no limit. With IIF I never got values for the last few elements. However I get the same result using Switch. Running Switch with just the missing elements gives me a good answer, so I'm thinking there is another limit. I can solve the problem by building a table but that has a whole new set of obstacles.

Thanks for the help in advance

jpl

 

[dhookom]

I would never use a nested IIF() of Switch() statement like this. It would be horrible to maintain this.

Consider creating a small PSH-TC range lookup table like:

[b][COLOR=#A40000]tblPSHTCRanges[/color][/b]
[COLOR=#204A87][b][RangeName]   [MinCode]     [MaxCode][/b]
Range          1770          1776
Range          1500          1519
Range          1583          1583
WPGC           1001          1301
------         ----          ----
[/color]

You can then add this table to your query and drop the RangeName into the grid. Set the criteria under Val([PSH-TC]) to
BETWEEN [MinCode] and [MaxCode]

WHEN your codes change, just edit your data, not an expression.


Duane
Hook'D on Access
MS Access MVP

 

[jpl458]

Had to make a change so I used your suggestion. But, there are two other tables that are joined on Trancode, so I can't join the Facility table (Your suggestion). I tried using it without the join and the answers are 99% right. Can you use a table that is not joined?

Thanks

jpl

 

[dhookom]

What is the Facility table? If it is tblPSHTCRanges then there are no joins, just a criteria.

Duane
Hook'D on Access
MS Access MVP

 

[jpl458]

Duane,the main transaction table has 750 entries in it, bit I only need 120 of those entries, so I created my own transaction table adding the facility name to each line. It works well.

Thanks for the tip.

jpl

 

[jpl458]

I tries adding the table you suggested and used it without a join and the results were not accurate. I was under the impression that all tables had to be joined in some way in a relational database. If this is not true I would be interested in knowing about that since I have had a need before.

The Facility table is the table you suggested, I just called it that.
Thanks

jpl

 

[dhookom]

You will need to cover every range of values in your from and to table.

You could also use a subquery to pull the RangeName field. I would need to have your SQL view to be able to assist with this as well as your new table and field names.

Duane
Hook'D on Access
MS Access MVP