Drive Aggregate by records table

[AWithers]

I am trying to create a fleixble (table driven) aggregation query and would appreciate your assistance. I have a table fo revenue values which I need to be able to simply group and change the groupings (without having to change the underlying SQL) any ideas?

e.g

I have a table contaning fields
company
producta
productb
productc
productd
producte

I want to be able to sum the revenues of say products a, b and c and seperately product d and e, but the key thing is that the mapping is stored in a table so all I have to do is change the mapping in a table that looks like

product name group
producta group1
productb group1
productc group1
productd group2
producte group2

Any ideas

Thanks

Andy

 

[Golom]

Generically
[tt]
Select Company, Group, Sum(Revenues) As [TotalRevenue]

From Companies INNER JOIN Groups
ON Companies.Product = Groups.Product

Group By Company, Group
[/tt]

 

[hneal98]

You can store your queries in a table then that would make it easy to access them, secure them and change them as you need.

Set up a tblQueryDefs table like this:

Field Name Data Type Description
QueryName Text 50 Name of the query
Description Text 65 A description of the query
SQLText Memo The SQL Representation of the qery
CreatedBy Text 35 Who Created it
LastModifiedBy Text 35 Who Updated it
DateCreated Date/Time When Created
DateModified Date/Time Last Modified Date

Then create a function to call the query like this:

Function GetSavedQuerySQL(strName As String) As String
'
' Returns a SQL string from a saved query
' In : strName - name of query to retreive
' Out : SQL string
'
Dim dbCurrent As Database
Dim rstQueries As Recordset
Set dbCurrent = DBEngine(0)(0)
Set rstQueries = dbCurrent.OpenRecordset("tblQueryDefs")
rstQueries.Index = "PrimaryKey"
rstQueries.Seek "=", strName
If Not rstQueries.NoMatch Then
GetSavedQuerySQL = rstQueries![SQLText]
End If
rstQueries.Close
dbCurrent.Close
End Function

When you need to call the query, just do this:

strSQL = GetSavedQyerySQL "QueryName"

You can use your existing code, but execute the strSQL instead of whatever you use now.

Disclaimer:
I got this out of a book I bought way back in the days of Access 2.0. The book is called Microsoft Access 2.0 How-To CD by Ken Getz, Helen Feddema, Mike Gunderloy, and Dan Haught.

 

[AWithers]

Dont think that will work. in the company table the products are different columns, in the grouping table the products are represented as rows of data

 

[Golom]

Sorry ... didn't read your post with sufficient attention. You have an un-normalized table (i.e. repeating fields Productn). That makes the whole thing more difficult. You need first to build a query that produces a normalized version of the table
[tt]
(Select CompanyID, "ProductA" As Product, Producta As ProdValue From tbl)
UNION ALL
(Select CompanyID, "ProductB", Productb From tbl)
UNION ALL
(Select CompanyID, "ProductC", Productc From tbl)
UNION ALL
(Select CompanyID, "ProductD", Productd From tbl)
UNION ALL

... etc
[/tt]
and save that as a Query ...

Then you can use code like that I provided initially to compute the grouped sums, substituting the query for the Company base table.