Using MS Access/Altering a CrossTab Query
Using A2007, CrossTab queries ain wot they used to be! In A2003, I could pull the values for the last seven days or for dates between two criteria. In A2007 this is what I get in SQL View:
TRANSFORM Sum(TblMeters.Elec1) AS SumOfElec1
GROUP BY TblMeters.ReadingTime
PIVOT Format([ReadingDate],"Short Date");
I would like to alter this crosstab so that it picks data between two dates, namely
Forms!FrmRptDialog!StartDate and Forms!FrmRptDialog!EndDate
At the moment, as I have built that query, it only picks up the data for dates presently in the table. It will not pick up any subsequent data for dates added after the crosstab query was built - for instance, tomorrow's readings??? grrr!
Can you advise how to alter the code please?
thanks in anticipation
Actually the code and the crosstab wizard have not changed from 2003.
You could still use either a WHERE clause (before the Group By) or a Having clause Before the Pivot to set criteria.
However I've always found it better to set criteria in an interim query and leave the crosstab alone.
So create a query
SELECT Elec1, ReadingTime, ReadingDate
WHERE ReadingDate BETWEEN Forms!FrmRptDialog!StartDate and Forms!FrmRptDialog!EndDate;
Call it qryReadingsBetween
Then use that query as the source of your Crosstab instead of TBlMeters.
Hope this helps,
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA