pwdpwd
02-02-2010, 08:27 AM
Hi
I'd be really grateful for any pointers for a problem that is driving me nuts! I am trying to change a 'static' report into a 'dynamic' one.
I have a scheduling application, originally written in Access 97 which generates service dates for trucks - usually around 8-10 'Service Dates' per year. One of the features of the app is that 1 report shows on one page (A4 landscape) all the trucks, grouped by Owner, that have a Service Date in each week - 53 columns wide!
I managed that by creating a select query and then into a CrossTab Query, where the Owner & Vehicle Reg No are the row headings and a Count of the Value of the Service Date gives me the 53 columns (there can only ever be 1 Service Date in any 1 week) with a '1' in each of the 53 columns where a service date is scheduled. Because of the limitations of space with 53 columns, I used an If statement in the Control Source of fields for each of the 53 columns on the report, replacing an 'X' in a Non-Blank field from the query.
I couldnt make the report dynamic, as Access 97 didnt allow Vertical text, so I made the select query 'Static' by using the year as criteria for a formatted expression of the Service Date so as to include the correct dates. Those queries and that report works fine.
I then needed to add an indication of when an MOT was to take place (always done on a service date); I did this by having a select query that shows the dates when an MOT is due (indicated by the user via an option button against each service date), then to a CrossTab (using the same principles as described above) and a Subreport which works fine on its own. I include this subreport invisibly on the main report, again for each year. I use the Contol source of the fields in the main report to check whether the equivalent field in the subreport is Non Blank, in which case I show a 'T' - else an 'X' or nothing. This report also works fine.
I now am bringing the app up to date and I want to make it dynamic, by having the user input a year, and then have the report produce the relevant data automatically. I now have a 'generic' report fed by select and CrossTab queries where the select query criteria requires an input from the user in the format for the Year; on its own, this report works fine..
The problem is that when I include the subreport, fed by equivalent generic queries requiring a similar input, the report will not work. On running the report I have to input the year from the 'main' query, but instead of being required to input the year for the subreport once, I am prompted to enter the year dozens (or even hundreds) of times. When I replace the criteria in the subreport select query with a number for a year (and hence make it 'non-generic' then the report works fine.
I know that I could create another 10 years worth of queries and reports, but know that there must be a better way.
I have enclosed 2 Pages of a 'Static' report for 1 owner so that you can see what it should look like.
Thanks for any help.
I'd be really grateful for any pointers for a problem that is driving me nuts! I am trying to change a 'static' report into a 'dynamic' one.
I have a scheduling application, originally written in Access 97 which generates service dates for trucks - usually around 8-10 'Service Dates' per year. One of the features of the app is that 1 report shows on one page (A4 landscape) all the trucks, grouped by Owner, that have a Service Date in each week - 53 columns wide!
I managed that by creating a select query and then into a CrossTab Query, where the Owner & Vehicle Reg No are the row headings and a Count of the Value of the Service Date gives me the 53 columns (there can only ever be 1 Service Date in any 1 week) with a '1' in each of the 53 columns where a service date is scheduled. Because of the limitations of space with 53 columns, I used an If statement in the Control Source of fields for each of the 53 columns on the report, replacing an 'X' in a Non-Blank field from the query.
I couldnt make the report dynamic, as Access 97 didnt allow Vertical text, so I made the select query 'Static' by using the year as criteria for a formatted expression of the Service Date so as to include the correct dates. Those queries and that report works fine.
I then needed to add an indication of when an MOT was to take place (always done on a service date); I did this by having a select query that shows the dates when an MOT is due (indicated by the user via an option button against each service date), then to a CrossTab (using the same principles as described above) and a Subreport which works fine on its own. I include this subreport invisibly on the main report, again for each year. I use the Contol source of the fields in the main report to check whether the equivalent field in the subreport is Non Blank, in which case I show a 'T' - else an 'X' or nothing. This report also works fine.
I now am bringing the app up to date and I want to make it dynamic, by having the user input a year, and then have the report produce the relevant data automatically. I now have a 'generic' report fed by select and CrossTab queries where the select query criteria requires an input from the user in the format for the Year; on its own, this report works fine..
The problem is that when I include the subreport, fed by equivalent generic queries requiring a similar input, the report will not work. On running the report I have to input the year from the 'main' query, but instead of being required to input the year for the subreport once, I am prompted to enter the year dozens (or even hundreds) of times. When I replace the criteria in the subreport select query with a number for a year (and hence make it 'non-generic' then the report works fine.
I know that I could create another 10 years worth of queries and reports, but know that there must be a better way.
I have enclosed 2 Pages of a 'Static' report for 1 owner so that you can see what it should look like.
Thanks for any help.