473,884 Members | 2,313 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Export Single Query to 25 Tabs in workbook

245 New Member
I have a query I would like to put into 25 tabs.
Expand|Select|Wrap|Line Numbers
  2.        QS36F_CMASTR.CU_NME, 
  3.        QS36F_TOPCUSTS.F2 
QS36F_TOPCUSTS. F2 is the employee number. Each employee has about 75 companies assigned to them. I would like each (F2 "Employee") to have their own tab of companies. How can I perform that without making 25 different queries?
Dec 18 '20 #1
10 2820
8,834 Recognized Expert Expert
It wouldn't be easy, but you should be able to accomplish this in a single step with Automation Code.
Dec 18 '20 #2
8,834 Recognized Expert Expert
I literally threw together a simple Demo for you that should clearly indicated how this can be done in a single Code Block. It has minimally been tested and appears to be fully operational. It can be improved upon and is easily expandable. The Demo will:
  1. Create a New, Visible Instance of Excel. The Code uses Early Binding, so be sure to set a Reference to the Microsoft Excel XX.X Object Library.
  2. Create a Recordset based on the Employees Table and make sure that it has Records.
  3. Create a New Workbook.
  4. Loop thru all Employees while at the same time, create another Recordset consisting of all Companies belonging to the Employee.
  5. For each Employee, add a Worksheet and name it LastName, FirstName.
  6. For each newly created Worksheet, Copy all the Companies assigned to that Employee starting from Range("A1"), using CopyFromRecords et.
  7. Autofit Column A for each Worksheet.
  8. For the sake of simplicity and brevity, I did not include the single Column Header (Company). The Code can easily be revised to include this aspect.
  9. Check and see if the Workbook that will be saved exists, and if it does DELETE it in order to avoid the DELETE Prompt.
  10. Save the Workbook, naming it MyDemo.xls, and place it in the same Folder as the Database.
  11. Perform some Clean Up chores.
Attached Files
File Type: zip Employee_Tabs.zip (26.2 KB, 33 views)
Dec 18 '20 #3
32,584 Recognized Expert Moderator MVP
In direct answer to your question :
"How can I perform that without making 25 different queries?"
By creating and using a template of SQL that you would then amend where relevant and output each version separately - probably within a loop of your code.

It's not too simple, but for a requirement like that I wouldn't expect it to be TBF.

NB. A template can often be stored as SQL within a QueryDef that you then extract and modify. Alternatively it could be stored as a string constant in your code. I find using Replace() works well when dealing with changes to a template.
Dec 20 '20 #4
8,834 Recognized Expert Expert
Good to see you and hope is all well with you. I'm just a little confused, which is just about normal for me. How would your solution create 25 Employee Tabs in a Workbook, with each Tab displaying the associated Companies for that Employee? Am I reading the Post incorrectly? Thanks in advance.
Dec 20 '20 #5
3,653 Recognized Expert Moderator Specialist

I think I understand what NeoPa is getting at. You create a Template Query in which you save a key "changeable " element as a special set of Characters (e.g., "%W"). Then when you need to run this series of queries, you extract the SQL for that QueryDef, Run Replace(strSQL, "%W", "WHERE F2 = EmployeeID ") and then using the result to fill the spreadsheet.

I have not looked at your attached DB, ADezii, but my approach would have been to simply create a first recordset based on the Employees, including their name, and then nest a second recordset based upon a built SQL string based upon the Employee ID, adding a worksheet and exporting the Data:

Expand|Select|Wrap|Line Numbers
  1. Dim strSQL as String
  2. Dim db As DAO.Database
  3. Dim rst1 As DAO.Recordset
  4. Dim rst2 As DAO.Recordset
  6. strSQL = _
  7.     "SELECT EmployeeID, EmployeeName " & _
  8.     "FROM tblEmployees " & _
  9.     "ORDER BY EmployeeName;"
  10. Set db = CurrentDB()
  11. Set rst1 = db.OpenRecordset(strSQL)
  12. With rst1
  13.     If Not (.BOF And .EOF) Then
  14.         Call .MoveFirst
  15.         Do While Not .EOF
  16.             strSQL = _
  17.                 "SELECT CompanyName " & _
  18.                 "FROM tblCompanies " & _
  19.                 "WHERE EmployeeID = !EmployeeID " & _
  20.                 "ORDER BY CompanyName;
  21.             Set rst2 = db.OpenRecordset(strSQL)
  23.             [EXPORT THINGS TO EXCEL]
  25.             Set rst2 = Nothing
  26.             Call .MoveNext
  27.         Loop
  28.         Call .Close
  29.     End If
  30. End With
  31. Call db.Close
  32. Set rst1 = Nothing
  33. Set db = Nothing
This may be identical to what ADezii has provided--this is just my approach. I would like to know advantages of saving a QueryDef Template over this approach--Again, always willing to learn from one of the masters....
Dec 21 '20 #6
32,584 Recognized Expert Moderator MVP
I would like to know advantages of saving a QueryDef Template over this approach--Again, always willing to learn from one of the masters...
You're very kind.

In direct reply to that question - "What are the benefits?" - I would say that it keeps the main logic in both :
  1. One place.
  2. Somewhere which is often editable more easily.
NB. It needn't be saved as a QueryDef if you prefer to work directly with SQL strings in your VBA. For many of my smaller tasks I simply create Constant definitions of the SQL code as a template. This has the advantage that you don't clutter up your QueryDefs with such stuff but can get a little messy when dealing with large or complicated queries.

Your explanation for ADezii was very close. Typically I would use something like a :
Expand|Select|Wrap|Line Numbers
  1. ...
  2. WHERE ([CustID]='%CI')
Expand|Select|Wrap|Line Numbers
  1. strSQL = Replace(strTemplate, "%CI", strCustID)
One technique I use, in order to store numeric references in an actual QueryDef, is to say :
Expand|Select|Wrap|Line Numbers
  1. ...
  2. WHERE ([NumField]='%NF')
Expand|Select|Wrap|Line Numbers
  1. strSQL = Replace(strTemplate, "'%NF'", lngNumField)
This fools Access into allowing %NF in the SQL as a numeric place-holder and also handles converting it into a numeric reference after the replacement.

Obviously, for you guys who know my work better, I use my MultiReplace() function, posted elsewhere, whenever there are multiple pairs of replacements to make within a SQL string.
Dec 23 '20 #7
32,584 Recognized Expert Moderator MVP
It may help if I simply take Twinny's code sample and convert it to use the Template idea :
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL as String, strTemplate As String
  2. Dim db As DAO.Database
  4. strSQL = "SELECT   EmployeeID" _
  5.        & "       , EmployeeName " _
  6.        & "FROM     tblEmployees " _
  7.        & "ORDER BY EmployeeName;"
  8. strTemplate = "SELECT   CompanyName " & _
  9.             & "FROM     tblCompanies " & _
  10.             & "WHERE    (EmployeeID='%EI') " & _
  11.             & "ORDER BY CompanyName;"
  12. Set db = CurrentDB()
  13. With db.OpenRecordset(strSQL)
  14.     If Not (.BOF And .EOF) Then
  15.         Call .MoveFirst
  16.         Do While Not .EOF
  17.             strSQL = Replace(strTemplate, "%EI", !EmployeeID)
  18.             With db.OpenRecordset(strSQL)
  20.                 [EXPORT THINGS TO EXCEL]
  22.                 Call .Close
  23.             End With
  24.             Call .MoveNext
  25.         Loop
  26.     End If
  27.     Call .Close
  28. End With
  29. Set db = Nothing
Dec 23 '20 #8
3,653 Recognized Expert Moderator Specialist

[As others have also commented, great to have you back!]

Ahhhhh.... I am assuming that by building your SQL string in lines 8-11 that it requires less processor/resources to modify/manipulate the string in line 17, rather than build a new string?

As usual, a novel and interesting approach. This is one I can probably use quite often, as I cycle through records for countless reasons.

Thank you for the explanation!
Dec 23 '20 #9
8,834 Recognized Expert Expert
Sorry guys, but I honestly do not see the advantages of that approach. To me it appears less intuitive and actually confusing to less experienced Users. Just my humble opinion. I am still glad to see you back, NeoPa.(LOL).
Dec 23 '20 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

by: DG | last post by:
Hi, Can anyone advise how to execute multiple statements in a single query batch. For example- update customers set customer_name = 'Smith' where customer_name = 'Smyth'; select * from customers; I can execute each statement individually but get the 'you have an error in
by: ats | last post by:
I have a table that contains the database names of external databases. Each one of these external databases contain a table (which has the same structure) that I would like to query together as one list. For example, say each external db has a table of names. tbNames (external db structure) ------- name as text - example db1.mdb
by: Daz | last post by:
Hi. I am trying to select data from two separate MySQL tables, where I cannot use join, but when I put the two select queries into a single query, I get an error telling me to check my syntax. Both of the queries work fine when I use them to query the MySQL server directly. My guess is that the MySQL extension only expects a single resource back from the database, but get's several, or that it just checks the statement first, and decides...
by: barrathi | last post by:
hai all, i have 10 database in one server. i need to take a backup daily iam using this type of query ex: mysqldump -a -u root -p dbname > dbname.sql(for each db) but i need
by: Simon | last post by:
Dear reader, With the export command you can export a query to Excel. By activate this command a form pop's up with the following text:
by: Remaniak | last post by:
Hi All, I use the code below in my form to filter data. But I also need to export the data to excel. So I'd like to create a temporary query based on the strWhere and export that query to excel. Could anyone modify the code for me? cheers Michiel
by: dreaken667 | last post by:
I have a MySQL database containing 16 tables of data. Each table has a different number of columns and there are few common field names accross tables. I do have one master table with which I connect the tables together with various queries using a unique id. My task is to generate reports from these tables dynamically. The user will select the data they want to see and the script will export a spreadsheet containing this data. I've got the...
by: kirthikasubramanian | last post by:
I have to retrieve two tables which are not having any relation in a single query itself for example select * from memberphoto;select * from restricconfig; if i execute this query only restricconfig table is viewed but i want both table to be retrieved simultaneously in a single query advance thanks kirthika
by: Darsin | last post by:
Hi all I need to perform a summation on a column of a table based on a criteria given in another column in the same table. The catch is i need to perform different sums according to the number of criterias in the criteria column, in a single query. the structure of the table is somethinmg like this (only relevant columns are shown) TABLE1 Value - numeric(20,6) Month - int
by: bharathi228 | last post by:
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.