Group on Month in query

[Thompyt]

Hi all,
I am trying to minimize the amount of queries and fountcrosstab queries quite helpful. What do I need to do to group dates on currentmonth and back for 6 months total? I would like the months to start on the15th. Remember this is for a crosstab query.
Thanks


T

[ranman256]

before you cross tab, create a query and add a new calc field: Month: Month([date]) (or however you want to group it) , to give you a 'caption' for the month you want to xtab.
THEN crosstab the above query and you have your caption.

[Thompyt]

There isn't a way to do it in the crosstab itself?

TRANSFORM Count(Inquires.Inq_ID) AS CountOfInq_ID
SELECT Inquires.Section_ID
FROM Inquires
GROUP BY Inquires.Section_ID
PIVOT Format([Date Sent],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"," Sep","Oct","Nov","Dec");

I was hoping you could change it in the PIVOT line Between DateSerial(Year(Date()),Month(Date())+1,-15) or something like that.

Thanks

[June7]

If you want your months to be 15th thru 14th, this complicates because the period crosses months and years. Never seen this.

How should Access know "the record dated April 1 really belongs with month Mar".

Yes, an expression to calculate the 'month' each record falls in maybe could be built into the CROSSTAB and that calculated field serve as the PIVOT field (just as is now being done with the Format() expression), but I expect it won't be a simple expression. I am still pondering.

[Thompyt]

Thanks June7,

For simplicities sake, lets stay in calendar months instead of on the 15th.
Month 1: Between DateSerial(Year(Date()),Month(Date())-1,0) And now()
Month 2: Between DateSerial(Year(Date()),Month(Date())-2,0) And DateSerial(Year(Date()),Month(Date())-1,0)
Month 3: Between DateSerial(Year(Date()),Month(Date())-3,0) And DateSerial(Year(Date()),Month(Date())-2,0)
Month 4: Between DateSerial(Year(Date()),Month(Date())-4,0) And DateSerial(Year(Date()),Month(Date())-3,0)
Month 5: Between DateSerial(Year(Date()),Month(Date())-5,0) And DateSerial(Year(Date()),Month(Date())-4,0)
Month 6: Between DateSerial(Year(Date()),Month(Date())-6,0) And DateSerial(Year(Date()),Month(Date())-5,0)

That's what I have been using in separate Queries. Since I can cut out 6 queries with crosstab queries, how would I incorporate these into one?

What about some type of 30 day interval?

Malcom,
Since I am a novice at this, I don't understand what you mean? I can already format the month off of this for each month on the report:
=Format(DateAdd("m",-5,Date()),"dd mmm yyyy")

Thanks

[Thompyt]

I changed the SQL a little and got it down to only the months where the month has data and the data is consolidated into the month it is in as a total for that month..

TRANSFORM Count(Inquires.Inq_ID) AS CountOfInq_ID
SELECT Inquires.Inq_Type
FROM Inquires
WHERE (((Inquires.[Date Sent]) Between DateSerial(Year(Date()),Month(Date())-6,0) And Now()))
GROUP BY Inquires.Inq_Type
PIVOT Format([Date Sent],"mmm yyyy");

That's a step forward, It takes the last 6 months, but where there is no data the month isn't shown. But that's another issue.