Advanced Crosstab query with SUM-function

[ino_mart]

All

I have next table

Location|Type|Name|Value|Date
New York|User|Tim|20|1-jan-2012
New York|User|Tim|10|2-jan-2012
New York|User|Tim|40|3-feb-2012
New York|User|John|5|1-jan-2012
New York|User|John|15|2-jan-2012
New York|User|John|25|3-feb-2012
New York|User|Marc|6|1-jan-2012
New York|User|Marc|26|2-jan-2012
New York|User|Marc|86|3-feb-2012
New York|Admin|Linda|40|1-jan-2012
New York|Admin|Linda|40|2-jan-2012
New York|Admin|Linda|60|3-feb-2012
New York|Admin|Peter|10|1-jan-2012
New York|Admin|Peter|20|2-jan-2012
New York|Admin|Peter|30|3-feb-2012
Seattle|User|John|40|1-jan-2012
Seattle|User|John|40|4-feb-2012
Seatlle|Admin|Flynn|80|1-jan-2012

I wrote a crosstab-query which calculates the SUM for each month based upon Location, Type and Name. However, if the SUM is more as 60, the crosstab must display 60

 
TRANSFORM IIf((Sum([value]))>60,60,Sum([value])) AS Expr1
SELECT Table1.Location, Table1.Type, Table1.Name
FROM Table1
GROUP BY Table1.Location, Table1.Type, Table1.Name
PIVOT Format([Date],"mmm")

This gives me next result

Location|Type|Name|feb|jan
New York|Admin|Linda|60|60
New York|Admin|Peter|30|30
New York|User|John|25|20
New York|User|Marc|60|32
New York|User|Tim|40|30
Seatlle|Admin|Flynn| |60
Seattle|User|John|40|40

Based upon this Crosstab-query, I must calculate the numbers without the NAME-field. Therefore I wrote a second query based upon the Crosstab:

 
SELECT Query1.Location, Query1.Type, Sum(Query1.feb) AS SumOffeb, Sum(Query1.jan) AS SumOfjan
FROM Query1
GROUP BY Query1.Location, Query1.Type;

This does give me the correct result:

Location| Type| SumOffeb| SumOfjan
New York| Admin| 90| 90
New York| User| 125| 82
Seatlle| Admin| | 60
Seattle| User| 40| 40

Now, what's the problem?


The amount of columns in the Crosstab-table is unknown and depends of the distinct Month-values in the table. If the table contains 5 different months, the crosstab will also have numbers for those 5 different months.

The second query is based upon the Crosstab-table, but during creation in Access you have to specify which fields you want to retrieve. So with example above, you manually have to select the fields "Sumofffeb" and "SumOfjan".

if I extend the table with dates from March, April, ... the second
query will not show these months as they are not selected

So, can I combine both queries in such way I do get the wanted result in one time?

Regards
Ino

[June7]

Don't think so. Requires modifying the query.

Would need first query to always include a record for every month, even if the value is 0, just so you can be certain to always have 12 fields in the crosstab. This stabilizes the crosstab output structure.

So either have a dummy record in the table for every month or join to a table or query that has 12 records, one for each month. Extract the month from each record date and join to table that has corresponding month values. Gets more complicated if you have multi-year data.

Your data sample has a misspelled Seattle, maybe just a typo in the post, not really in table?

[ino_mart]

With some logical thinking, I found a solution using VBA

My first crosstab-query stays, the second one is generated with VBA and stored in a second query.

 
Dim rs As Recordset
Dim qdf As QueryDef
Dim f As Field
Dim strSQL As String
Dim tbl As QueryDef
strSQL = "select * from [Query1]"
Set rs = CurrentDb.OpenRecordset(strSQL)
strSQL = "SELECT Location, Type "
For Each f In rs.Fields
    If f.Name <> "Location" And f.Name <> "Type" And f.Name <> "Name" Then
        strSQL = strSQL & ",sum([" & f.Name & "]) as [Sum" & f.Name & "]"
    End If
Next f
strSQL = strSQL & " FROM [Query1] GROUP BY Location, Type"
For Each tbl In CurrentDb.QueryDefs
    If tbl.Name = "tmpConsolidatedQuery1" Then
        CurrentDb.Execute ("drop table tmpConsolidatedQuery1")
    End If
Next
Set qdf = CurrentDb.CreateQueryDef("tmpConsolidatedQuery1", strSQL)
DoCmd.OpenQuery "tmpConsolidatedQuery1"

[June7]

Well, you need to upgrade your class from Novice! I am always hesitant to throw VBA suggestion at beginner users. I am impressed, especially by the loop logic and use of QueryDefs. Congratulations on solution.