X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Previous And Current Month Data, Access 2010    
post Sep 19 2017, 04:42 AM

Posts: 144
Joined: 10-August 17

Hi UA Nation

I have a cross tab query The SQL look like below:
TRANSFORM Nz(Sum(qryAccidentIncident.Cases))+0 AS SumOfCases
SELECT qryAccidentIncident.IncidentClassification, qryAccidentIncident.Year, Sum(qryAccidentIncident.Cases) AS [Total Cases]
FROM qryAccidentIncident
WHERE (((qryAccidentIncident.IncidentClassification)="Fatalities" Or (qryAccidentIncident.IncidentClassification)="Lost Work Day Cases" Or (qryAccidentIncident.IncidentClassification)="Medical Treatment Case" Or (qryAccidentIncident.IncidentClassification)="First Aid Case"))
GROUP BY qryAccidentIncident.IncidentClassification, qryAccidentIncident.Year
PIVOT Format([DateEntry],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

And the output is look like the attached image. But I need to select only the previous month and current month data only,

How Can I rearrange the SQL.

Thank you all
Attached File(s)
Attached File  Capture.JPG ( 30.72K )Number of downloads: 10
Go to the top of the page
post Sep 19 2017, 05:23 AM

Posts: 70
Joined: 5-July 16

Add criteria to your DateEntry >= DateSerial(Year(Date), Month(Date) - 1, 1) assuming you don't have data in the future this is all you need.
Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    23rd March 2018 - 11:31 AM