Is It Possible To Use A Single Query To Export Multiple Outputs Based On Different Filter Criteria?, Access 2013
Jan 11 2017, 12:21 PM
Joined: 11-January 17
This seems like a simple request in theory, but I haven't been yet been able to find the solution.
I have a query set up in my database which includes details of store deliveries made by multiple depots, I need to export this query's data to an Excel file to be used in depot specific reports, for each report it needs to be filtered by delivery depot (one output file per depot based around the same overall dataset/query)
Up until now, I would make copies of the original query and save each one with a different depot filter applied. I then have a macro set up (assigned to a button on a form) with multiple "Export with Formatting" one for each depot (each referencing that depot's saved version of the query) and creating a corresponding .xlsx output file.
My question therefore, is can this be done with a single query (as they are all the same anyway) and then apply the depot filter at the macro stage? (avoiding having to have a high number of duplicate queries saved each time) - this applies to a number of different outputs (each one then multiplied by the number of depots!)
E.G. export 'Query A' filtered on 'Depot A' as "DepotA.xlsx", then export the same 'Query A', this time filtered on 'Depot B' instead as "DepotB.xlsx", etc etc.
Thanks in advance for any tips and apologies if I've missed this covered elsewhere!
Jan 11 2017, 12:27 PM
Access Wiki and Forums Moderator
Joined: 19-June 07
From: SoCal, USA
Welcome to UtterAccess!
If you're considering using a button, then you could possibly use a form reference parameter in your query where you'll select the depot on the form and the query uses it as a criteria. You might give it a try.
Just a thought...
Jan 11 2017, 01:32 PM
Joined: 29-March 05
Along the same lines as theDBGuy, I have a saved query that is filtered to a fake Location of 9999 along these lines at the end of its SQL statement:
WHERE lngLocation = 9999;
When my users want to export multiple locations to separate Excel files, I have a button on a form that has VBA that reads the .SQL value of that saved query, uses the Replace() function to replace "9999" with the actual location number within the loop, and save the new SQL as a new (albeit temporary) query object, using a name like "qryTempQueryDeleteMeIfYouSeeMe" - something I obviously wouldn't use normally. I then export the data using this query object within my loop. After the last export, I delete the temporary query object.
I like this method because once in a while, the export query's list of fields gets altered, and this allows to me simply update the saved query (the one with the 9999 at the end), and the code still works properly.
Hope this helps,
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Jan 11 2017, 05:12 PM
Joined: 1-July 09
From: England - UK A small island north of France
I use a process similar to Doctor9 in a VBA loop
basically query 1 is written without the depot filter in the where clause to select all the depots for the parameter
then i have query 2 with a default depot in the where clause that is replaced in a loop based on the depot in the result set of query 1
this captures only depots that should be reported
if you would like the VBA code let me know as i will post when back at work
I am no wizard but I can spell - Sum Thymes
All the knowledge in the world is of no use to fools :- Eagles
Jan 12 2017, 12:06 PM
Joined: 11-January 17
Thanks for the welcome and the suggestions so far.
I should've added that the outputs will be the same everyday and will include all depot options (currently 9 for this particular query, again another 9 times for the next query that I would apply this to, etc) therefore a solution that requires any manual input (such as via a form) would not really suit in this situation I don't think as it would add time to the current daily process.
The database is just for a single user (myself) or occasionally someone else within our team, we import raw delivery data, perform various queries, calculations and functions before exporting an output for each site which is used in an excel report.
As mentioned, the current set up of copying the query 9 times works perfectly fine for day to day use, I'm just rebuilding from scratch and thought it might be easier for the future if there was an easy option that would remove the need for all this duplication and clutter in the shutter bar! (beyond simply hiding the duplicate queries!)
The Parameter and replace sounds like it may work, (I've used pop up text input boxes with parameters before but really imagined using am automated variation of this if possible) - however, I'm still fairly new to Access and create my queries, macros etc in design view rather than getting into the SQL/VBA side of things too much just yet!
|Search Top Lo-Fi||19th January 2017 - 02:39 PM|