I wonder if someone can help me with a problem I am currently facing. I am trying to create either a crosstab or select query (which ever works best) which uses fields from two tables: RDOTOWN and Crispmon. The fields I need to use are project, town, crisp or cers (CRISP_OR_C), city or region from the RDOTOWN table and total projected jobs for YEAR_NO=5 (TOTJOB_PR="5"), actual current jobs (TOTJOBS_AC), actual new jobs (NEWJOBS_AC) and actual new businesses (NEW_BUSIN2).
The tables are joined by PROJECT and PROJECT_NA.
The RDOTOWN table contains information on projects for which grants have been approved or applied for. The Crispmon table contains monitoring information for each project. Some of the projects may have only one year's data entered, others may have up to 10 years depending on what stage they are in. Some of the data also contains nulls.
What I am trying to achieve is a query which returns info grouped by crisp or cers, project and town showing the projected jobs for year 5 (TOTJOBS_PR="5"), the current jobs(TOTJOBS_AC), current new jobs (NEWJOBS_AC)and current new businesses(T0T_BUSIN2) for each city or region.
I am streamlining an old system which used to have the same data in 10 different tables called CRISPMY1-10. The query I am trying to recreate from the CRISPMON table produced the correct results for current jobs using the expression:
Current Jobs: IIf([CRISPMY10.TOTJOBS_AC]<>"",[CRISPMY10.TOTJOBS_AC], IIf([CRISPMY9.TOTJOBS_AC]<>"",[CRISPMY9.TOTJOBS_AC], and so on to........ IIf([CRISPMY1.TOTJOBS_AC]<>"",[CRISPMY1.TOTJOBS_AC],0))))))))))
and for current new jobs and current new businesses by substituting the appropriate fields in the expression.
If anyone can help I would greatly appreciate it, as I have been working on this for a few days. Please do not be too techie with the answer as I am only a beginner.
The tables are joined by PROJECT and PROJECT_NA.
The RDOTOWN table contains information on projects for which grants have been approved or applied for. The Crispmon table contains monitoring information for each project. Some of the projects may have only one year's data entered, others may have up to 10 years depending on what stage they are in. Some of the data also contains nulls.
What I am trying to achieve is a query which returns info grouped by crisp or cers, project and town showing the projected jobs for year 5 (TOTJOBS_PR="5"), the current jobs(TOTJOBS_AC), current new jobs (NEWJOBS_AC)and current new businesses(T0T_BUSIN2) for each city or region.
I am streamlining an old system which used to have the same data in 10 different tables called CRISPMY1-10. The query I am trying to recreate from the CRISPMON table produced the correct results for current jobs using the expression:
Current Jobs: IIf([CRISPMY10.TOTJOBS_AC]<>"",[CRISPMY10.TOTJOBS_AC], IIf([CRISPMY9.TOTJOBS_AC]<>"",[CRISPMY9.TOTJOBS_AC], and so on to........ IIf([CRISPMY1.TOTJOBS_AC]<>"",[CRISPMY1.TOTJOBS_AC],0))))))))))
and for current new jobs and current new businesses by substituting the appropriate fields in the expression.
If anyone can help I would greatly appreciate it, as I have been working on this for a few days. Please do not be too techie with the answer as I am only a beginner.
