Saving Queries in an Organized Manner

[Russellh]

Can you create folders and save queries in those? For instance I am about to create a query that pulls reoccurring monthly charges for the month of January. I have to enter a date range for the month of January.
Instead of retyping the dates every month, I want to create 12 querries (1 for each month of the year). This way they can be scheduled to run and not manually ran. If I start doing this for several of my querries I am going to have one hell of a bar to scroll through as I'm using queries to make new ones and so on.



Can you not create a folder like you do for files to categorize them in? Sanitation Reports in a Sanitation folder for example?

Just a thought....If affected the way I link queries such as now having to type the folder name in programming that could get messy.

[John_G]

No, that can't be done.

But if you want all dates in January, you don't need to enter the explicit start and end dates - why not just ask for the month number, and have the query use month(datefield) as the criteria?

[June7]

What do you mean by 'run' queries? Are you opening SELECT queries? What do you do with the query once opened - just view, print? Why not a form or report based on query?

Don't create 12 queries. Apply filter, as John_G suggests. Options are:

1. dynamic parameter query as RecordSource for form or report

2. VBA to set filter of form or report

[Russellh]

The ultimate goal is to have these queries 1) Run 2) export in excel format 3) save the export as xxxxxxxjan2015 in a BOX Folder that is shared with others and do all of this on a scheduled routine where no one has to enter any parameters.
Is that possible? And where would I look for good instructions on how to learn how to do that?

[June7]

Every aspect of the process you describe has been discussed in some thread in this forum as well as elsewhere on the web, except maybe saving to BOX folder (Box.com?).

It doesn't matter what approach you take, parameter has to come from somewhere. If you do 12 queries, they will have to be edited each year to change the year parameter. What do you mean by 'scheduled routine'? Some event must be triggered to run code. It is possible to have a VBScript open Access db and run a procedure. Windows Task Manager can be scheduled to run the VBScript at specified times.

Need a good understanding of Access functionality, programming concepts, database principles, and VBA language/syntax.

[Russellh]

Ok. We are willing to pay for services. Going to scan the web for a local access database programmer. Thanks for the help!

[orange]

You really haven't said much about why you want to do this.
As John and June have said, folders per se can not be done. However, if you have "standardardized/recognized" naming conventions and some organizational procedures, you could have stored queries. Also, as was stated, you could have some forms with instructions/buttons etc and some vba code (and functions). Such forms could be designed to match your needs and when controls were selected/filled and buttons were clicked, they could pass parameters to functions and execute queries.
You would need documentation, operating procedures and training, but it has been done many times by many organizations.

Analyzing your requirements, understanding your organization, establishing standards and practices and designing user friendly and maintainable interfaces is not necessarily best handled by "a local access database programmer".

Do you have specifications for what you want the programmer to do?

[Russellh]

Yea I want to set this up to run without inputs. I want it to run monthly at the beginning of the month and put the spreadsheet in the folder so the person(s) who wants it can download it at will. Reports I can do but in these instances they want an excel spreadsheet so that they can manipulate and search/sort data. I asked about folders to just try and reduce the # of queries that I have in my database. But it sounds like that if I want it to run monthly on its own without inputs that I will need to create either 12 different queries or 1 query that can look at the current date (date at which it runs) pull the month out and then enter a specific date range based on the month it is currently being ran in. The bill dates is what it is pulling so a range has to be entered.

[June7]

Yes, the query can be parameterized for the current date. Just be sure when you run report on first of the month you don't really want previous month data.