Query/Report Parameter Prompt Functionality

[MTADS]

I have a couple of questions which are sort of related. They both revolve around Parameters with a Query/Report.

Here is a bit of background. The source Table is formatted in a way similar to this:

Code--Account#--Expense Description--Jan--Feb...

When running the Query, and associated Report, the user is prompted to input an Account# via a Parameter that I set up. The dialog box pops up with the word "Account#" and a box for the user to enter by what account that they want to restrict the Report. [Question 1] - Is there a way to make this dialog box contain a drop-down box with a list of predetermined accounts instead of the aforementioned entering of an account number? If not via this dialog box, is there another way?

Also, multiple accounts roll up into a single P&L line item. For example, let's say that accounts 1234 and 5678 both make up "Equipment". [Question 2] - Is there a way to run the Report in such a way to return all accounts which roll into a line item? I want to have the ability to run by either an individual account or the P&L line in total.



Thanks!

[June7]

No. I never use queries with input parameter prompts. Too hard to validate input, which is what you want a combobox for.

Build a form for user input of criteria and refer to comboboxes on form as input parameters. Review: http://datapigtechnologies.com/flash...mtoreport.html

Yes, the P&L can be an input parameter.

[MTADS]

Thank you very much June! That video was very helpful. This resolves the issue of creating the drop-down/combo box. I am still unsure how I would go about including the P&L lines as options in this combo box, though. The line names do not appear in the source data/table. I am able to get a listing of which accounts roll up into which lines. Would I need to create a separate table for this and then somehow incorporate that into the combo box?

Also, my report appears to be set at a maximum limit of 22" in width. However, to give enough room for all the columns of data to display properly, I need more room (the last three columns are too small). How would/can I do this?

Thank you.

[June7]

I assumed you already had a table that associates accounts with P&L and it was the data source for combobox.

22 inches is maximum width and length of reports, period. I had one report with this issue so I made the records 'wrap' on two lines, as well as their labels. I staggered the fields and labels.

If you want to provide db for analysis, follow instructions at bottom of my post.

[MTADS]

Thanks for all of the help and knowledge. I just played around with the column widths of some of the non-critical (do not need to be displayed on a single line) columns and got everything to essentially fit. Might need some tweaking later.

I also was able to create the Table which associates the accounts with the respective P&L line. I then was able to create another Combo Box on the Form to allow for selection. Works great!

Is there a way to restrict what accounts are available in the Account Combo Box once a P&L Line is selected? Right now, when a P&L line is selected, all the accounts still appear as selectable. I was curious as to if there is a way to make it so that only the accounts which roll into that particular line are available for selection. If this cannot (easily) be done I do not think it is a big deal. It would just be a "clean" way to create it.

[June7]

Yes, if a lot of the fields are of text nature, can allow the textboxes to grow vertically. This does of course impact the number of records displayed on each page of report but if that is not a concern, should serve.

Use dependent (cascading) comboboxes. Review: http://datapigtechnologies.com/flash...combobox2.html

[MTADS]

Things are coming along great. Thanks again for all of your help! I have been able to add a couple of more cool features which have gone over well. But, I have hit a wall with this latest request which I fear may not be possible without going back to the drawing board. Here is the scenario:

As I mentioned before, the source Table is formatted as such:

Code--Account#--Expense Description--Jan--Feb--Mar--Apr...

The Query and Report which are based off of this are formatted in the same way. Now, I am being asked if it is possible to put something on the Form which I have built which would allow one to choose a specific month and have the Report return only that month when it runs. Obviously as it is currently constructed the Report returns all months at all times. Is there a way to return just a specific month as the Report is currently constructed? Some functionality or operation that can be added? Or, would it require a completely new Report (and even new Table and/or Query) to accomplish this?

Thanks.

[June7]

When I read your first post and saw that data structure, I wondered if you would get to this issue but I avoided addressing. That is not a normalized data structure and will cause you issues, as you are now finding out.

There is a way to provide a filter functionality you describe but is a little more complicated. And the report would still need a textbox for each month field. It's just that all but one will be blank when filtered for one month. The alternative is to build 12 identical reports, each filtered for one month (e.g. WHERE Not [Jan] Is Null) and has textbox only for that month. User selects month on form and code opens the appropriate report.

[MTADS]

The whole idea of this project was to combine several analysts' forecasts into one file which one could look at to find variances vs. actuals, etc. So, the Table, et al. were designed in such a way to mirror the analysts' standardized forecast templates. The analyst pulls in the current month's actuals and then forecasts the remaining months accordingly. Thus, the multi-month view structure. Each analyst will upload this tab into a folder on a shared drive from which this database pulls all of the data.

My hands were tied in this regard. I will see which course of action, if any, my project supervisor wishes to follow to resolve my issue.

[June7]

The data could be input in normalized structure and still be output in the forecast template design. That would probably be a CROSSTAB query.

[MTADS]

When I read your first post and saw that data structure, I wondered if you would get to this issue but I avoided addressing. That is not a normalized data structure and will cause you issues, as you are now finding out.

There is a way to provide a filter functionality you describe but is a little more complicated. And the report would still need a textbox for each month field. It's just that all but one will be blank when filtered for one month. The alternative is to build 12 identical reports, each filtered for one month (e.g. WHERE Not [Jan] Is Null) and has textbox only for that month. User selects month on form and code opens the appropriate report.

It has been decided to go with the second route that you mentioned, i.e. 12 individual reports. I hope that this involves just building one Report and then going back and changing the month being filtered? I hope that it would involve just putting the correct Null statement in the month field. Is my previous Report of any use, or do these 12 need to be built from scratch? I imagine that a full-year view would still be desired as well as the ability to pull just a single month.

Thank you.

[June7]

Build 1, copy/paste 11, modify each for the filter criteria. I suppose you could copy/paste the report you now have and delete 11 month textboxes, then copy/paste that new report 11 times.

[MTADS]

So, I would build these 12 new Reports and then I would need a new Command Button and Combo Box on the Form which links to each and determines which one to run?

I spoke with a friend who is familiar with Access (much more so than I ) but has not worked with it for quite some time. He mentioned something about building one Report and several Queries (one for each month) and having the "month" on the Report be dynamic so that the Report runs the appropriate Query based on what month is selected on the Form. Does any of that make sense?

I am open to whatever will get me to my end goal.

I will begin creating the 12 different Reports per your suggestion.

[MTADS]

I may have found a potential solution.


I created the 12 new Reports, one for each month. They all follow the same naming convention "[month name]Detail". I then created a new Command Button and Combo Box (named Month) in the Form. I used the Command Button Wizard to make the function Open Report. I then went into the Properties of the Command Button>Event>On Click and clicked on the ellipsis (...) of the Embedded Macro that the Wizard created. I changed "Report Name" to read: =[Forms]![ReportOptions].[Month] & "Detail". Now, the Report of the month selected in the Combo Box runs.


Do you foresee any problems? Preventative troubleshooting?

[June7]

That is exactly what I was getting at.

What your friend described would require code to set the report RecordSource property. I don't think macro can do that but VBA can, and it is something I do for one of my report objects.

If data was normalized, there would be only one query and one report and a month criteria filter would be applied when report opens.