I'm pretty new at this and have been given the task of creating a database to catalogue and create reports based on enquiries received from each state by the company, nature of the enquiry, date received, date solved, turnaround times, etc. T'was all going well till it got to automating reports from the crosstab queries.
I have 3 crosstab queries that look up from one main query.they all have the same type of data but run off different time intervals (ex: enquiries received during a given month {Opened between startdate and enddate}, enq's completed during a month {Closed between startdate and enddate}, enquiries unsolved at the end of the month {Not closed at Enddate})
Rows:Nature of enquiry (5 types), Columns:States
Since the company does not receive or complete enquiries from all states in any given month, the number of columns in the crosstab queries vary.
3 questions:
1) I have tried a custom form to get user input (startdate, enddate) for the crosstab queries but xtabqueries will not run with the usual expression [forms]![formname]![textboxname]. How can I get user input for these xtabqueries??
2) How can I either:
a)Fix the xtabqueries so that the same number and name of states always appear even if there is no value for them in any given month, so that the same report can be run every month?
b) Make the column headings in the reports "dynamic" so that they always reflect the number and name of the states in the crosstab queries?
3) Does anyone have any suggestion as to a better way of running this type of report, or even better, examples!!????
An early response would be appreciated since the due date for this project is in a week or so.
Thanks..
I have 3 crosstab queries that look up from one main query.they all have the same type of data but run off different time intervals (ex: enquiries received during a given month {Opened between startdate and enddate}, enq's completed during a month {Closed between startdate and enddate}, enquiries unsolved at the end of the month {Not closed at Enddate})
Rows:Nature of enquiry (5 types), Columns:States
Since the company does not receive or complete enquiries from all states in any given month, the number of columns in the crosstab queries vary.
3 questions:
1) I have tried a custom form to get user input (startdate, enddate) for the crosstab queries but xtabqueries will not run with the usual expression [forms]![formname]![textboxname]. How can I get user input for these xtabqueries??
2) How can I either:
a)Fix the xtabqueries so that the same number and name of states always appear even if there is no value for them in any given month, so that the same report can be run every month?
b) Make the column headings in the reports "dynamic" so that they always reflect the number and name of the states in the crosstab queries?
3) Does anyone have any suggestion as to a better way of running this type of report, or even better, examples!!????
An early response would be appreciated since the due date for this project is in a week or so.
Thanks..
