Phil4tektips
Technical User
As I am relatively new to MS Access I didnt know how to solve this problem, but I came up with a solution. I just wanted to know if it is crazy and will cause problems in the future or if it is perfectly feasible.
By the way it is working perfect for me atm.
I have a main data table with many fields to keep records of risks. Lets say Joe Bloggs can report into the DB, he reports for a certain directorate, on certain projects. He may want to see a report on all the risks that affect his project, but only from his directorate. (That set the scene!)
I have a button that links to another form. This form has 7 combo boxes on it that are set to "All" upon opening. Joe Bloggs therefore would select the project combo box and select his project, and select the directorate combo box and select his directorate. These fields populate a separate table (always one row).
When the user clicks on the 'enter' button a query is run and the combo boxes are set back to 'All'. The data is filtered in the query by using IIF statements in the criteria boxes. If the answer was 'All' it doesnt filter on anything. IFF the answer was a directorate/project then it filters the main table by that answer. This query then populates a general report which looks like a table. Joe Bloggs now has a list of everything affecting his project from his function at the current time.
The advantage of this is that the answers to users queries dont have to be thought of before the questions are asked. The setup of the combo boxes menu means I have one generic query and one generic form.
PS. If the no. of directorates expand for instance I have a button that allows the user to enter a new directorate. This is written to a separate table, from which the combo boxes do a SELECTDISTINCT ROW from to source their data. Therefore if the drop down menus dont have an adequate field name to suit them they can add something to the combo box relatively painlessly.
What do you think?
By the way it is working perfect for me atm.
I have a main data table with many fields to keep records of risks. Lets say Joe Bloggs can report into the DB, he reports for a certain directorate, on certain projects. He may want to see a report on all the risks that affect his project, but only from his directorate. (That set the scene!)
I have a button that links to another form. This form has 7 combo boxes on it that are set to "All" upon opening. Joe Bloggs therefore would select the project combo box and select his project, and select the directorate combo box and select his directorate. These fields populate a separate table (always one row).
When the user clicks on the 'enter' button a query is run and the combo boxes are set back to 'All'. The data is filtered in the query by using IIF statements in the criteria boxes. If the answer was 'All' it doesnt filter on anything. IFF the answer was a directorate/project then it filters the main table by that answer. This query then populates a general report which looks like a table. Joe Bloggs now has a list of everything affecting his project from his function at the current time.
The advantage of this is that the answers to users queries dont have to be thought of before the questions are asked. The setup of the combo boxes menu means I have one generic query and one generic form.
PS. If the no. of directorates expand for instance I have a button that allows the user to enter a new directorate. This is written to a separate table, from which the combo boxes do a SELECTDISTINCT ROW from to source their data. Therefore if the drop down menus dont have an adequate field name to suit them they can add something to the combo box relatively painlessly.
What do you think?