Wondering if someone could help with this.

(I'm working in Access 2003.)

I have a table with the following info:

Hitdate | Tone
------------------
Jan 29 | B
Jan 30 | A
Feb 5 | B
Mar 8 | C
Mar 10 | D

And I need to build a query that converts this into...

Hitdate | A | B | C | D
Jan | 1 | 1 | 0 | 0
Feb | 0 | 1 | 0 | 0
Mar | 0 | 0 | 1 | 1

I'd like to build 1 query that generates this for the last 6 months, and one that generates it for the last 26 weeks.

The aim is to use this data for a pivot chart.

Can anyone help out with the easiest way of building a query that will accomplish this?

Many thanks,

Bradley F

BradF

Paste this sql statement into the DBE and give it a try:


SELECT Left([HitDate],3) AS HitMo, Sum(IIf(tblHits!Tone="A",1,0)) AS A, Sum(IIf(tblHits!Tone="B",1,0)) AS B, Sum(IIf(tblHits!Tone="C",1,0)) AS C, Sum(IIf(tblHits!Tone="D",1,0)) AS D
FROM tblHits
GROUP BY Left([HitDate],3);


HTH