Query Results by Quarter of Year

[mddaniels]

I need to be able to pull data by quarters of year. Quarter 1 should be months 10, 11, 12, quarter 2 should be 1, 2, 3, quarter 3 should be 4, 5, 6 and quarter 4, 7 , 8, 9, then I need to be able to count how many Pacer upgrades are in each quarter, how many Pacer new are in each quarter and how many Pacer change are in each quarter. Will you please show me the criteria in the query or the criteria in a report (whichever is the easiest to maintain) to retrieve this data. I would like for the results to appear with column headings PacerorICD, New or Change, Qtr 1, Qtr 2, Qrt 3, Qrt 4.

Here is the SQL that I have so far. How do I get the quarters to show correctly for Qrt 1,which should be months 9, 10, 11, etc. I believe that if I could get this query to pull the quarters correctly, the report would be simple to tell it to count.

SELECT [Pacer QA tbl].DateofProcedure, [Pacer QA tbl].PacerorICD, [Pacer QA tbl].ImplantingPhysician, DatePart("q",[DateofProcedure]) AS Qtr
FROM [Pacer QA tbl];

 

[dhookom]

I would start by calculating your quarter correctly

SELECT DateofProcedure, PacerorICD, ImplantingPhysician, DatePart("q",DateAdd("m",3,[DateofProcedure])) AS Qtr
FROM [Pacer QA tbl];

Then create a crosstab from the query using a Column Heading of "Qtr" & Qtr. You haven't mentioned anything about how changes, news, upgrades, etc are determined so you need to figure it out.

Duane
Hook'D on Access
MS Access MVP

 

[mddaniels]

You are a God send. I knew it had to be simple and not complicated.

Thank you so very much.

Have a Blessed weekend.