Export Single Query to 25 Tabs in workbook

[anoble1]

I have a query I would like to put into 25 tabs.

SELECT QS36F_TOPCUSTS.F1, 
       QS36F_CMASTR.CU_NME, 
       QS36F_TOPCUSTS.F2 
FROM QS36F_CMASTR 
INNER JOIN QS36F_TOPCUSTS 
ON QS36F_CMASTR.CU_CUS = QS36F_TOPCUSTS.F1;

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?

[ADezii]

It wouldn't be easy, but you should be able to accomplish this in a single step with Automation Code.

[ADezii]

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 CopyFromRecordset.
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.

[NeoPa]

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.

[ADezii]

@NeoPa:
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.

[twinnyfo]

ADezii,

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:

Dim strSQL as String
Dim db As DAO.Database
Dim rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset
 
strSQL = _
    "SELECT EmployeeID, EmployeeName " & _
    "FROM tblEmployees " & _
    "ORDER BY EmployeeName;"
Set db = CurrentDB()
Set rst1 = db.OpenRecordset(strSQL)
With rst1
    If Not (.BOF And .EOF) Then
        Call .MoveFirst
        Do While Not .EOF
            strSQL = _
                "SELECT CompanyName " & _
                "FROM tblCompanies " & _
                "WHERE EmployeeID = !EmployeeID " & _
                "ORDER BY CompanyName;
            Set rst2 = db.OpenRecordset(strSQL)
 
            [EXPORT THINGS TO EXCEL]
 
            Set rst2 = Nothing
            Call .MoveNext
        Loop
        Call .Close
    End If
End With
Call db.Close
Set rst1 = Nothing
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....

[NeoPa]

Twinny:
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 :

...
WHERE ([CustID]='%CI')

strSQL = Replace(strTemplate, "%CI", strCustID)

One technique I use, in order to store numeric references in an actual QueryDef, is to say :

...
WHERE ([NumField]='%NF')

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.

[NeoPa]

It may help if I simply take Twinny's code sample and convert it to use the Template idea :

Dim strSQL as String, strTemplate As String
Dim db As DAO.Database
 
strSQL = "SELECT   EmployeeID" _
       & "       , EmployeeName " _
       & "FROM     tblEmployees " _
       & "ORDER BY EmployeeName;"
strTemplate = "SELECT   CompanyName " & _
            & "FROM     tblCompanies " & _
            & "WHERE    (EmployeeID='%EI') " & _
            & "ORDER BY CompanyName;"
Set db = CurrentDB()
With db.OpenRecordset(strSQL)
    If Not (.BOF And .EOF) Then
        Call .MoveFirst
        Do While Not .EOF
            strSQL = Replace(strTemplate, "%EI", !EmployeeID)
            With db.OpenRecordset(strSQL)
 
                [EXPORT THINGS TO EXCEL]
 
                Call .Close
            End With
            Call .MoveNext
        Loop
    End If
    Call .Close
End With
Set db = Nothing

[twinnyfo]

NeoPa,

[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!

[ADezii]

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).

[NeoPa]

ADezii:
Sorry guys, but I honestly do not see the advantages of that approach.

It's OK ADezii. You don't have to give it to me straight. You're allowed to beat arout the bush if you like :-D

Seriously though, It's good to say it how you feel. No progress was ever made by holding on to any reservations.

First of all, bear in mind that offering was not my ultimate suggestion. It was an illustration of the concept by translating something you can already see & understand, to use the general concept. Had I been doing it from scratch the Template would have been in a QueryDef or, more likely in this case, a Const line at the top of the module.

The point about using Templates is that you separate out the bits into their relevant homes. The general Template information - in this case the fundamental SQL string - is not specific to the iterations of the loop. Logically it lives outside of it. The specific ID, on the other hand, absolutely lives there.

Many people approach code in an unstructured way. It works fine - to a certain extent. Clever people can stretch this further of course, but that approach comes with built-in limitations. Once you get to a certain level of complexity, and that varies per developer, it becomes increasingly difficult to maintain that code. The concept of Structured Programming was introduced to help with that, where procedures were designed to be stand-alone and interact as little as possible with, and therefore depend on the behaviour of, outside code. Obviously OOP came after that but even OOP extends these basic principles.

When you work with templates you have the visual clues (The approach I use is to have % characters to represent parts that change.) so you can easily distinguish between the static text and the text which changes. When you're dealing with larger & larger projects these little clues save you a great deal of following tangents when it comes to re-reading and understanding old code.

Speaking very generally, structure in your code allows you to develop & manage more of it.