You are here:

Using MS Access/Altering a CrossTab Query


Hi Scott

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
SELECT TblMeters.ReadingTime
FROM TblMeters
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
FROM TblMeters
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

Using MS Access

All Answers

Answers by Expert:

Ask Experts




I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.


I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Brooklyn College BA

©2017 All rights reserved.