Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038

    Automate Report Output via VBA

    Experts:

    I'd would like to get some assistance with streamlining a report output routine. Please find attached database with the following content:

    DB Contents:
    1. Table -- generic source data containing US states and dates
    2. Query -- the criteria is updated based on value selection in the form's listbox
    3. Form -- contains listbox with source data based on the distinct states included in the table
    4. Report -- report is linked to an export routine (with routine's file path = "C:\Users\dellc\Report.pdf"). FOR DEMO PURPOSES, PLEASE UPDATE THE FILE PATH IN THE STORED ROUTINE.

    Existing Process:
    - User open form and selects **one** value from the listbox.
    - Once "Submit Query" is clicked the report is being outputted to the specified path "C:\Users\dellc\
    - If user clicks "Submit Query" again, the existing report is overwritten **without prompt**.
    - Overall this process works ok, but requires improvement.

    Improvement #1:
    a. How can I append the selected listbox value to the report's filename? So, rather than storing "Report.pdf", I'd like to store "Report_Virginia.pdf" if "Virginia was selected".
    b. While this database example includes state values, the actual data set includes organizational values. Sometimes, ab org value may include a slash ("/") in the org name. Since Windows won't allow me to, e.g., store "Report_DC/VA.pdf", how could a slash be converted to, e.g., a "-"? In such case, the report would be saved as "Report_DC-VA.pdf".


    c. If possible, I'd rather use VBA (vs. the Export routine) to specify file ("C:\Users\dellc\Report.pdf"). How can this be done via VBA?

    If improvement #1 addresses all three steps, that would be great! Ideally though, I'd like to even further enhance the process.

    Improvement #2:
    - Although the generic database uses states (with potentially 50 values), the actual database uses roughly ~30 different org values.
    - That said, I would have to click on each of the, e.g., 30 potential values in the listbox in order to generate 30 distinct reports.
    - As I need to have 30 separate reports, I do **NOT** want a "multi-select" using the **IN(Texas, Virginia)** clause since it would merge these two entities into a single report.
    - Maybe a secondary "Select All" command button could be included, which scans through the listbox items one by one and then generates all 30 individuals reports within a few seconds.
    - How can improvement #2 be accomplished?

    Thank you for your help in advance. As I'm still coming up to speed w/ Access, it would be greatly appreciate if an example of the proposed could be included in the database.

    AGAIN, THANKS!!!!

    EEH
    Attached Thumbnails Attached Thumbnails ExportRoutine.JPG  
    Attached Files Attached Files

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Concatenate listbox value to literal text to create whatever name you want.

    Use Replace() function to replace / character with something else.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    June7... appreciate the feedback. For a person who's still learning more about VBA and database, your solution(s) is "riddled".

    I don't know exactly how to implement your recommendation nor do I understand if it in reference to concatenating the listbox value to the filename. If yes to the latter, how specifically is that done?

    Could you please provide an example?

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Maybe this will help. I did not check for a valid path - you could use a folder picker to allow the user to pick the save path.
    To select more than one state, you must set the list box "Multi Select" to "Simple" or "Extended"

    "report is linked to an export routine"
    the export routine is not used!!
    Attached Files Attached Files

  5. #5
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Steve -- your solution is absolutely PERFECT!!!!!

    Thousand thanks for providing a solution that exactly meets the requirements. Again, thanks!!!!

    Tom

  6. #6
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Steve -- again, your solution works great. As mentioned, the sample database was only for demo purposes. At this time, I am trying to integrate your solution into my actual database. Doing so, I'm using a step-by-step approach.

    That said, before I integrate your loop, I need to get the listbox process working with a single value being passed into my query. Unfortunately, I am still having trouble with a single item. The VBA below throws an error the line **qdf.SQL = strSQL**.

    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub cmdExportReport_Click()
    
            'Declare variables
            Dim db As DAO.Database
            Dim qdf As DAO.QueryDef
            Dim varItem As Variant
            Dim strCriteria As String
            Dim strSQL As String
            
            'Get the database and stored query
            Set db = CurrentDb()
            Set qdf = db.QueryDefs("Q201_CFT_Ownership_Report_Ncode_Gonzales_RPT")
            
                
            'Loop through the selected items in the list box and build a text string
            If Me!lstStates.ItemsSelected.Count > 0 Then
                For Each varItem In Me!lstStates.ItemsSelected
                    strCriteria = "T11_CrossFunctionalTeam.CFT_Owner = '" & Me!lstStates.ItemData(varItem) & "'"
                
                    'Build the new SQL statement incorporating the string
                    strSQL = "SELECT T11_CrossFunctionalTeam.CFT_CategorySortOrder, T11_CrossFunctionalTeam.CFT_Owner, T11_CrossFunctionalTeam.CFT_Category, " & _
                             "T11_CrossFunctionalTeam.CFT, T11_CrossFunctionalTeam.CFT_Description, Count(T00_JunctionTable_BCFT.BilletIDfk) AS NoParticipants, " & _
                             "T99_Lookup_RankTitle.SortIDGroupOther, T00_JunctionTable_BCFT.BilletIDfk, T01_Billets.Ra_Billet_Title, T01_Billets.Ra_BIN, " & _
                             "T00_JunctionTable_OBS.StaffMemberIDfk, T01_StaffMembers.All_LastName, T01_StaffMembers.All_RankTitle, T01_StaffMembers.Mil_PRD, " & _
                             "NCode_Group([N_Code]) AS NCode_Group FROM (T01_StaffMembers LEFT JOIN T99_Lookup_RankTitle ON T01_StaffMembers.All_RankTitle = T99_Lookup_RankTitle.RankTitle) " & _
                             "RIGHT JOIN ((T99_SortingCFTOwner INNER JOIN T11_CrossFunctionalTeam ON T99_SortingCFTOwner.CFTOwner = T11_CrossFunctionalTeam.CFT_Owner) INNER JOIN (T01_Organization " & _
                             "RIGHT JOIN ((T01_Billets LEFT JOIN T00_JunctionTable_OBS ON T01_Billets.BilletIDpk = T00_JunctionTable_OBS.BilletIDfk) INNER JOIN T00_JunctionTable_BCFT " & _
                             "ON T01_Billets.BilletIDpk = T00_JunctionTable_BCFT.BilletIDfk) ON T01_Organization.OrganizationIDpk = T00_JunctionTable_OBS.OrganizationIDfk) " & _
                             "ON T11_CrossFunctionalTeam.CFTIDpk = T00_JunctionTable_BCFT.CFTIDfk) ON T01_StaffMembers.StaffMemberIDpk = T00_JunctionTable_OBS.StaffMemberIDfk " & _
                             "GROUP BY T11_CrossFunctionalTeam.CFT_CategorySortOrder, T11_CrossFunctionalTeam.CFT_Owner, T11_CrossFunctionalTeam.CFT_Category, " & _
                             "T11_CrossFunctionalTeam.CFT, T11_CrossFunctionalTeam.CFT_Description, T99_Lookup_RankTitle.SortIDGroupOther, T00_JunctionTable_BCFT.BilletIDfk, " & _
                             "T01_Billets.Ra_Billet_Title, T01_Billets.Ra_BIN, T00_JunctionTable_OBS.StaffMemberIDfk, T01_StaffMembers.All_LastName, T01_StaffMembers.All_RankTitle, " & _
                             "T01_StaffMembers.Mil_PRD, NCode_Group([N_Code]) " & _
                             "WHERE " & strCriteria & " " & _
                             "ORDER BY T11_CrossFunctionalTeam.CFT_CategorySortOrder;"
                      
                             qdf.SQL = strSQL
                                
                    Next varItem
                   
            Else
                MsgBox "Please selete one or more States"
            End If
                  
           
            'Execute export routine (onto C:drive)
            DoCmd.RunSavedImportExport "Export_Report"
            MsgBox "The report was stored at the following location: C:\Users\dellc\", vbInformation, "Information"
            
            'Empty the memory
            Set db = Nothing
            Set qdf = Nothing
          
    End Sub
    Do you have any thoughts as to why the VBA stops at the qdf.SQL = strSQL giving me a run-time error?

    Thank you for the continued help!

    Tom

  7. #7
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Steve...

    Please see previous posting first... i.e., the step-by-step approach. I now went further and replace the simple SQL w/ the actual SQL.

    I'm still getting the run-time error on the line: qdf.SQL = strSQL

    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub cmdExportReport_Click()
    
        'Declare variables
        Dim db As DAO.Database
        Dim qdf As DAO.QueryDef
        Dim varItem As Variant
        Dim strCriteria As String
        Dim strSQL As String
    
        Dim ReportPath As String
        Dim ReportFileName As String
        Dim OutputPathFileName As String
    
        'Get the database and stored query
        Set db = CurrentDb()
        Set qdf = db.QueryDefs("Q201_CFT_Ownership_Report_Ncode_Gonzales_RPT")
    
        ReportPath = "C:\Users\dellc\Report - "   '<<--Save To Path
    
        'Loop through the selected items in the list box and build a text string
        If Me!lstStates.ItemsSelected.Count > 0 Then
            For Each varItem In Me!lstStates.ItemsSelected
                strCriteria = "T11_CrossFunctionalTeam.CFT_Owner = '" & Me!lstStates.ItemData(varItem) & "'"
    
                'Build the new SQL statement incorporating the string
                    strSQL = "SELECT T11_CrossFunctionalTeam.CFT_CategorySortOrder, T11_CrossFunctionalTeam.CFT_Owner, T11_CrossFunctionalTeam.CFT_Category, " & _
                             "T11_CrossFunctionalTeam.CFT, T11_CrossFunctionalTeam.CFT_Description, Count(T00_JunctionTable_BCFT.BilletIDfk) AS NoParticipants, " & _
                             "T99_Lookup_RankTitle.SortIDGroupOther, T00_JunctionTable_BCFT.BilletIDfk, T01_Billets.Ra_Billet_Title, T01_Billets.Ra_BIN, " & _
                             "T00_JunctionTable_OBS.StaffMemberIDfk, T01_StaffMembers.All_LastName, T01_StaffMembers.All_RankTitle, T01_StaffMembers.Mil_PRD, " & _
                             "NCode_Group([N_Code]) AS NCode_Group FROM (T01_StaffMembers LEFT JOIN T99_Lookup_RankTitle ON T01_StaffMembers.All_RankTitle = T99_Lookup_RankTitle.RankTitle) " & _
                             "RIGHT JOIN ((T99_SortingCFTOwner INNER JOIN T11_CrossFunctionalTeam ON T99_SortingCFTOwner.CFTOwner = T11_CrossFunctionalTeam.CFT_Owner) INNER JOIN (T01_Organization " & _
                             "RIGHT JOIN ((T01_Billets LEFT JOIN T00_JunctionTable_OBS ON T01_Billets.BilletIDpk = T00_JunctionTable_OBS.BilletIDfk) INNER JOIN T00_JunctionTable_BCFT " & _
                             "ON T01_Billets.BilletIDpk = T00_JunctionTable_BCFT.BilletIDfk) ON T01_Organization.OrganizationIDpk = T00_JunctionTable_OBS.OrganizationIDfk) " & _
                             "ON T11_CrossFunctionalTeam.CFTIDpk = T00_JunctionTable_BCFT.CFTIDfk) ON T01_StaffMembers.StaffMemberIDpk = T00_JunctionTable_OBS.StaffMemberIDfk " & _
                             "GROUP BY T11_CrossFunctionalTeam.CFT_CategorySortOrder, T11_CrossFunctionalTeam.CFT_Owner, T11_CrossFunctionalTeam.CFT_Category, " & _
                             "T11_CrossFunctionalTeam.CFT, T11_CrossFunctionalTeam.CFT_Description, T99_Lookup_RankTitle.SortIDGroupOther, T00_JunctionTable_BCFT.BilletIDfk, " & _
                             "T01_Billets.Ra_Billet_Title, T01_Billets.Ra_BIN, T00_JunctionTable_OBS.StaffMemberIDfk, T01_StaffMembers.All_LastName, T01_StaffMembers.All_RankTitle, " & _
                             "T01_StaffMembers.Mil_PRD, NCode_Group([N_Code]) " & _
                             "" & "WHERE " & strCriteria & ";"
    
                ' "ORDER BY T11_CrossFunctionalTeam.CFT_CategorySortOrder;"
    
    
    
                'Apply the new SQL statement to the query
                qdf.SQL = strSQL
    
                ReportFileName = Replace(Me!lstStates.ItemData(varItem), "/", "_") & ".pdf"
                OutputPathFileName = ReportPath & ReportFileName
    
                DoCmd.OutputTo acOutputReport, "R_CFT_Ownership_Report_Gonzalez", acFormatPDF, OutputPathFileName, False
    
            Next varItem
    
        Else
            MsgBox "Please selete one or more States"
        End If
    
    
       MsgBox "The report was stored at the following location: " & ReportPath, vbInformation, "Information"
    
    
        'Empty the memory
        Set qdf = Nothing
        Set db = Nothing
    
    
    End Sub
    What did I overlook/missed in the integration from sample db to actual database?
    Attached Thumbnails Attached Thumbnails RunTimeError.JPG  

  8. #8
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    SUCCESS!!!!

    I went back to the original demo example and added more fields (e.g, firstname). I then added "grouping" by [Firstname]. I then realized that "grouping" didn't not use a WHERE clause. Instead, the grouping requires "Having".

    Once I replaced "WHERE" with "HAVING" (in the original where clause), the reports are generated and output to the appropriate location!! Yeah!!!!

    Thank you Steve for providing a great example.

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    for future reference sql queries have a defined order

    for a select query it is

    SELECT..
    FROM..
    WHERE..
    GROUP BY..
    HAVING..
    ORDER BY...


    In your case, the WHERE was after GROUP BY, not before

    Good practice is to use WHERE when you need to apply criteria to a field you don't need to display or are just grouping on. Use HAVING when you need to apply the criteria to a field after the grouping activity - i.e. after summing, counting whatever. If every field is grouped, and you are not summing/counting etc then use SELECT DISTINCT.

    The argument is that WHERE will result in a faster query because you are filtering out elements before grouping - so the grouping activity will be faster as there are fewer records to group on. In practice, the query engine seems to resolve this, however it is something of which you should be aware.

  10. #10
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Ajax -- your recommendation makes sense. If you think the SQL below could therefore be further streamlined, I'd certainly welcome you input.

    Code:
                 strSQL = "SELECT T11_CrossFunctionalTeam.CFT_CategorySortOrder, T11_CrossFunctionalTeam.CFT_Owner, T11_CrossFunctionalTeam.CFT_Category, " & _
                          "T11_CrossFunctionalTeam.CFT, T11_CrossFunctionalTeam.CFT_Description, Count(T00_JunctionTable_BCFT.BilletIDfk) AS NoParticipants, " & _
                          "T99_Lookup_RankTitle.SortIDGroupOther, T00_JunctionTable_BCFT.BilletIDfk, T01_Billets.Ra_Billet_Title, T01_Billets.Ra_BIN, " & _
                          "T00_JunctionTable_OBS.StaffMemberIDfk, T01_StaffMembers.All_LastName, T01_StaffMembers.All_RankTitle, T01_StaffMembers.Mil_PRD, " & _
                          "NCode_Group([N_Code]) AS NCode_Group FROM (T01_StaffMembers LEFT JOIN T99_Lookup_RankTitle ON T01_StaffMembers.All_RankTitle = T99_Lookup_RankTitle.RankTitle) " & _
                          "RIGHT JOIN ((T99_SortingCFTOwner INNER JOIN T11_CrossFunctionalTeam ON T99_SortingCFTOwner.CFTOwner = T11_CrossFunctionalTeam.CFT_Owner) INNER JOIN (T01_Organization " & _
                          "RIGHT JOIN ((T01_Billets LEFT JOIN T00_JunctionTable_OBS ON T01_Billets.BilletIDpk = T00_JunctionTable_OBS.BilletIDfk) INNER JOIN T00_JunctionTable_BCFT " & _
                          "ON T01_Billets.BilletIDpk = T00_JunctionTable_BCFT.BilletIDfk) ON T01_Organization.OrganizationIDpk = T00_JunctionTable_OBS.OrganizationIDfk) " & _
                          "ON T11_CrossFunctionalTeam.CFTIDpk = T00_JunctionTable_BCFT.CFTIDfk) ON T01_StaffMembers.StaffMemberIDpk = T00_JunctionTable_OBS.StaffMemberIDfk " & _
                          "GROUP BY T11_CrossFunctionalTeam.CFT_CategorySortOrder, T11_CrossFunctionalTeam.CFT_Owner, T11_CrossFunctionalTeam.CFT_Category, " & _
                          "T11_CrossFunctionalTeam.CFT, T11_CrossFunctionalTeam.CFT_Description, T99_Lookup_RankTitle.SortIDGroupOther, T00_JunctionTable_BCFT.BilletIDfk, " & _
                          "T01_Billets.Ra_Billet_Title, T01_Billets.Ra_BIN, T00_JunctionTable_OBS.StaffMemberIDfk, T01_StaffMembers.All_LastName, T01_StaffMembers.All_RankTitle, " & _
                          "T01_StaffMembers.Mil_PRD, NCode_Group([N_Code]) " & _
                          "" & "HAVING " & strCriteria & " " & _
                          "ORDER BY T11_CrossFunctionalTeam.CFT_CategorySortOrder;"

  11. #11
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I did say for future reference. So thanks for the offer, but I'll pass

    There is a lot more to writing efficient queries than just getting the order right. I would need to see your structure, understand what the output is supposed to be etc. You were reluctant to take my advice way back when about table/relationship design and I've no idea whether your current design is appropriate or not.

    For readability I would recommend aliasing your tables to a short but meaningful name (just 1,2 or 3 letters). Will also reduce significantly the amount of typing and risk of typo's

  12. #12
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Ajax -- thank you for the additional feedback. I certainly do NOT ignore someone's recommendation. If I overlooked one of yours, I probably was overwhelmed w/ the design.

    Now, I had to mimic the existing/working process for a similar scenario. Here how, I'm not querying on a field that needs to obtain a value from the listbox. Instead, I need to add the listbox value onto a field with an alias.

    Right now, this process results in another eye-brow raiser.


    Below is the correct SQL (before VBA rewrote it):
    Code:
    SELECT [CFT] & " | " & [CFT_Description] AS CFTConc, T99_Lookup_RankTitle.SortIDGroupOther, NCode_Group([N_Code]) AS NCode_Group, T01_Billets.RA_BIN, T01_Billets.RA_Billet_Title, T01_StaffMembers.All_LastName, T01_StaffMembers.All_RankTitle, T01_StaffMembers.Mil_PRD
    FROM (T01_StaffMembers LEFT JOIN T99_Lookup_RankTitle ON T01_StaffMembers.All_RankTitle = T99_Lookup_RankTitle.RankTitle) RIGHT JOIN (T11_CrossFunctionalTeam INNER JOIN (T01_Organization RIGHT JOIN ((T01_Billets LEFT JOIN T00_JunctionTable_OBS ON T01_Billets.BilletIDpk = T00_JunctionTable_OBS.BilletIDfk) INNER JOIN T00_JunctionTable_BCFT ON T01_Billets.BilletIDpk = T00_JunctionTable_BCFT.BilletIDfk) ON T01_Organization.OrganizationIDpk = T00_JunctionTable_OBS.OrganizationIDfk) ON T11_CrossFunctionalTeam.CFTIDpk = T00_JunctionTable_BCFT.CFTIDfk) ON T01_StaffMembers.StaffMemberIDpk = T00_JunctionTable_OBS.StaffMemberIDfk
    GROUP BY [CFT] & " | " & [CFT_Description], T99_Lookup_RankTitle.SortIDGroupOther, NCode_Group([N_Code]), T01_Billets.RA_BIN, T01_Billets.RA_Billet_Title, T01_StaffMembers.All_LastName, T01_StaffMembers.All_RankTitle, T01_StaffMembers.Mil_PRD
    HAVING (((NCode_Group([N_Code]))="N1"))
    ORDER BY [CFT] & " | " & [CFT_Description], T99_Lookup_RankTitle.SortIDGroupOther, T01_StaffMembers.All_LastName;

    The SQL in the VBA looks like this:
    Code:
                'Build the new SQL statement incorporating the string
                strSQL = "SELECT [CFT] & ' | ' & [CFT_Description] AS CFTConc, T99_Lookup_RankTitle.SortIDGroupOther, NCode_Group([N_Code]) AS NCode_Group, " & _
                         "T01_Billets.RA_BIN, T01_Billets.RA_Billet_Title, T01_StaffMembers.All_LastName, T01_StaffMembers.All_RankTitle, T01_StaffMembers.Mil_PRD " & _
                         "FROM (T01_StaffMembers LEFT JOIN T99_Lookup_RankTitle ON T01_StaffMembers.All_RankTitle = T99_Lookup_RankTitle.RankTitle) " & _
                         "RIGHT JOIN (T11_CrossFunctionalTeam INNER JOIN (T01_Organization RIGHT JOIN ((T01_Billets LEFT JOIN T00_JunctionTable_OBS " & _
                         "ON T01_Billets.BilletIDpk = T00_JunctionTable_OBS.BilletIDfk) INNER JOIN T00_JunctionTable_BCFT ON T01_Billets.BilletIDpk = T00_JunctionTable_BCFT.BilletIDfk) " & _
                         "ON T01_Organization.OrganizationIDpk = T00_JunctionTable_OBS.OrganizationIDfk) ON T11_CrossFunctionalTeam.CFTIDpk = T00_JunctionTable_BCFT.CFTIDfk) " & _
                         "ON T01_StaffMembers.StaffMemberIDpk = T00_JunctionTable_OBS.StaffMemberIDfk GROUP BY [CFT] & ' | ' & [CFT_Description], T99_Lookup_RankTitle.SortIDGroupOther, " & _
                         "NCode_Group([N_Code]), T01_Billets.RA_BIN, T01_Billets.RA_Billet_Title, T01_StaffMembers.All_LastName, T01_StaffMembers.All_RankTitle, T01_StaffMembers.Mil_PRD " & _
                         "" & "HAVING " & strCriteria & " " & _
                         "ORDER BY [CFT] & ' | ' & [CFT_Description], T99_Lookup_RankTitle.SortIDGroupOther, T01_StaffMembers.All_LastName;"

    And it results in the following output:

    Code:
    SELECT [CFT] & ' | ' & [CFT_Description] AS CFTConc, T99_Lookup_RankTitle.SortIDGroupOther, NCode_Group([N_Code]) AS NCode_Group, T01_Billets.RA_BIN, T01_Billets.RA_Billet_Title, T01_StaffMembers.All_LastName, T01_StaffMembers.All_RankTitle, T01_StaffMembers.Mil_PRD
    FROM (T01_StaffMembers LEFT JOIN T99_Lookup_RankTitle ON T01_StaffMembers.All_RankTitle = T99_Lookup_RankTitle.RankTitle) RIGHT JOIN (T11_CrossFunctionalTeam INNER JOIN (T01_Organization RIGHT JOIN ((T01_Billets LEFT JOIN T00_JunctionTable_OBS ON T01_Billets.BilletIDpk = T00_JunctionTable_OBS.BilletIDfk) INNER JOIN T00_JunctionTable_BCFT ON T01_Billets.BilletIDpk = T00_JunctionTable_BCFT.BilletIDfk) ON T01_Organization.OrganizationIDpk = T00_JunctionTable_OBS.OrganizationIDfk) ON T11_CrossFunctionalTeam.CFTIDpk = T00_JunctionTable_BCFT.CFTIDfk) ON T01_StaffMembers.StaffMemberIDpk = T00_JunctionTable_OBS.StaffMemberIDfk
    GROUP BY [CFT] & ' | ' & [CFT_Description], T99_Lookup_RankTitle.SortIDGroupOther, NCode_Group([N_Code]), T01_Billets.RA_BIN, T01_Billets.RA_Billet_Title, T01_StaffMembers.All_LastName, T01_StaffMembers.All_RankTitle, T01_StaffMembers.Mil_PRD
    HAVING ((([Q201_CFT_Participation_Report_Ncode_Gonzales_RPT].[NCode_Group])='N01'))
    ORDER BY [CFT] & ' | ' & [CFT_Description], T99_Lookup_RankTitle.SortIDGroupOther, T01_StaffMembers.All_LastName;

    Please see attached JPG for details. How should the strSQL be rewritten so that the output yields the original SQL (top image)?
    Attached Thumbnails Attached Thumbnails Expression.JPG  

  13. #13
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    when you use the query builder, it does all sorts of things to generate the sql which generally makes for a longer string. It will also add back table names, even if not actually required. The query builder also has limitations - you can't create unequal joins for example, it also can't show passthough queries, union queries and the like.

    So no need to get hung up on how the query is displayed in the builder. If you did as I advised and used WHERE (in the right place in the SQL), the sql would have looked pretty much the same (just more brackets and table names). In the builder - just change 'expression' to 'where'. The reason expression has been used is probably down to the fact you have brackets in your field name - using illegal characters in field and table names can lead to confusion and misleading error messages

    Since this query is not doing any aggregation, there is no need to use a group by query, as previously advised, use select distinct.

  14. #14
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Ajax:

    I replaced modified SQL with the original SQL. Without grouping, I obtain more records than I need. Thus, grouping is required!

    I think the problem lies in the strCriteria (see below):

    strCriteria = "Q201_CFT_Participation_Report_Ncode_Gonzales_ RPT.NCode_Group = '" & Me!lstCFTParticipants.ItemData(varItem) & "'"

    How should be strCriteria be rewritten to accommodate for an alias field which does value conversion of a fieldname.

    Regular field name = N_Code
    Alias conversion to a higher class org code uses the following in the query: NCode_Group: NCode_Group([N_Code])

    How should the strCriteria be modified for the alias?

    Thank you,
    EEH

  15. #15
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Can't. WHERE clause cannot use alias names created in same query. Must use the calculation - same as in GROUP BY.

    Did you try SELECT DISTINCT instead of GROUP BY?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 10-31-2014, 06:51 AM
  2. Replies: 4
    Last Post: 04-23-2014, 04:35 PM
  3. Replies: 0
    Last Post: 03-07-2012, 08:10 AM
  4. Automate report on ACCESS
    By Schoolo in forum Database Design
    Replies: 1
    Last Post: 12-18-2011, 02:11 AM
  5. Automate Printing Report
    By Kagbodo in forum Reports
    Replies: 5
    Last Post: 07-01-2011, 02:19 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums