UtterAccess Discussion Forums > Grouping in queries

UtterAccess Discussion Forums > Microsoft® Access > Access Queries

axsuzr123

Aug 7 2006, 10:11 PM

Hi,

My problem is that I have to create a lot of queries with different grouping. For example, I have to group the data in regard of product type or the region so that I have to build two different queries, but only one parameter changes. Is there a way avoid creating a lot of queries by passing the grouping argument to the query or with VBA ? Here's a look of two of my queries, the only difference is Test.Product vs Test.Region.

Thanks !

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SELECT Test.Product, Sum(Test.Prices) AS SumOfPrices
FROM Test
GROUP BY Test.Product;

SELECT Test.Region, Sum(Test.Prices) AS SumOfPrices
FROM Test
GROUP BY Test.Region;

.. etc

Cosmos75

Aug 7 2006, 10:21 PM

It is posssible to change a querydef's (i.e. stored query) SQL statement with VBA Code using DAO.

fahade

Aug 8 2006, 02:33 AM

The other way could be to create a query on the fly. You can create a temporary or a permanent Query and then execute it. The following shows a permanent query creation.

CODE

Public Function makeSumQuery(ByVal CriteriaField As String)
          Dim SumQry As QueryDef
          Dim db As DAO.Database
          Dim SQL As String
          Dim intX As Integer

          Set db = CurrentDb

          SQL = " SELECT Test.Product, Sum(Test." & Trim(CriteriaField) & " ) AS Sum " & _
                     " FROM Test " & _
                     " GROUP BY Test." & Trim(CriteriaField) & "; "

        For intX = 0 To db.QueryDefs.Count - 1
            If db.QueryDefs(intX).Name = "TempQuery" Then
                    db.QueryDefs.Delete "TempQuery"
             End If
        Next

        Set SumQry = db.CreateQueryDef("TempQuery", SQL)

        DoCmd.OpenQuery "TempQuery", acViewNormal

        SumQry.Close
        db.Close

End Function

You can also pass the table name as another variable to make it dynamic. Hope this helps.

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.