Solved Criteria fields not working (1 Viewer)

[TKaisierBHG]

Okay, I am going to do my best to try and explain this,

So I have built a crosstab Query. The Row headings being ModelID and FindingLevel, the Column heading being FindingCategory. I have the cross tab counting the number of each finding category at the specified level, and then a total for each ModelID. That all works fine and does what I need it to. What I am attempting to do now is attach a weight to the FindingCategorys. To achieve this I have tried to use iif statements.
IIf([FindingCategory]="ModelSpecification",2, iif([FindingCategory]="ModelAssumptionLimitation",1, iif([FindingCategory]="ModelTesting",3, iif([FindingCategory]="ModelImplementation",5, iif([FindingCategory]="OngoingMonitoring",4, iif([FindingCategory]="RegulatoryCompliancePolicy",5, iif([FindingCategory]="ModelControls",4)))))))
but when I add it to the criteria field I get a "Data type mismatch error". I am very new to Access so any advice or help is greatly appreciated.

 

[Gasman]

Do you have table lookups for FindingCategory?

 

[plog]

1. A Cross tab should be the last step of query building. Get everything else to work, then crosstab it. So my suggestion is to revert it to a regular query and debug it that way.

2. Make a Weights table. Instead of a hugely nested IIf statement to determine what Category goes to what weight, just throw that data in a table, bring that table into the query, JOIN it appropriately and just bring in that weight field.

3. Do you have any FindingCategory values not accounted for? I think a NULL might screw up your crosstabbing.

 

[TKaisierBHG]

1. A Cross tab should be the last step of query building. Get everything else to work, then crosstab it. So my suggestion is to revert it to a regular query and debug it that way.

2. Make a Weights table. Instead of a hugely nested IIf statement to determine what Category goes to what weight, just throw that data in a table, bring that table into the query, JOIN it appropriately and just bring in that weight field.

3. Do you have any FindingCategory values not accounted for? I think a NULL might screw up your crosstabbing.

The null thing might be true. I set up weights table and then joined them. I am not quite sure where to go from here

again, very much so an amateur so anything helps.

 

[mike60smart]

It might be easier if you can upload a copy of the database?

 

[TKaisierBHG]

It might be easier if you can upload a copy of the database?

I cant unfortunately, but I figured out a work around, did the separate table of the weights method. Thanks for everyone jumping in.