I am trying to recreate an application in Access 97 that was originally written in Xbase. In particular, a report that summorizes fields that may or may not contain data.
Each year the user creates a new table that will contain that year's data. At report time the user is allowed to select which year they wish to report. I've got everything up to this point worked out.
The problem arises when during a given year one of the entries in the table spoke Chinese but no one spoke Russian, and another year, someone spoke Russian but no one spoke Chinese. However, the report must contain columns for both Chinese and Russian regardless of the existance of any data.
If I attempt to utilize a crosstab query to gather the data for the report, the crosstab won't contain a "Russian" field if there were no instances that particular year, and will error when the report expects a "Russian" field for the Russian column.
Can I somehow use in-line IIF's to avoid the potential errors?
Could I gather the data into an array (I know how to do that) to be used for the report's record source ( I DONT know how to do that!)?
Some detailed guidance would be greatly appreciated.
Thanks,
Dan
Each year the user creates a new table that will contain that year's data. At report time the user is allowed to select which year they wish to report. I've got everything up to this point worked out.
The problem arises when during a given year one of the entries in the table spoke Chinese but no one spoke Russian, and another year, someone spoke Russian but no one spoke Chinese. However, the report must contain columns for both Chinese and Russian regardless of the existance of any data.
If I attempt to utilize a crosstab query to gather the data for the report, the crosstab won't contain a "Russian" field if there were no instances that particular year, and will error when the report expects a "Russian" field for the Russian column.
Can I somehow use in-line IIF's to avoid the potential errors?
Could I gather the data into an array (I know how to do that) to be used for the report's record source ( I DONT know how to do that!)?
Some detailed guidance would be greatly appreciated.
Thanks,
Dan
