Query Data by Quarters in a Year

[MarieD]

I have an Access db that contains

DateofProcedure_________Procedure Type
1/2/2009_______________ ICD
3/2/2009_______________ Pacer
5/2/2009_______________ ICD
8/2/2009_______________ Pacer

Please show me how the criteria would look to query a total of procedures by each quarter of the year.

 

[Galaxiom]

Use the DatePart function to get the quarters.
http://office.microsoft.com/en-us/access/HA012288121033.aspx

Make a query with the quarter derived field and ProcedureType fields only.
Right click the design grid and turn on Totals.

Group By the quarter and ProcedureType fields.
Add another ProcedureType field and set to Count.

 

[MarieD]

I am a little confused with the Microsoft explanations of DatePart. Would you mind typing out the formula for an example? I've tried the DatePart("q",[DateofProcedure])=1 in the criteria of the Qtr One column. When I saved the query and opened it again, access threw the DatePart("q",[DateofProcedure])= into a blank column field heading and the =1 in the criteria as "1".

 

[raskew]

Hi Marie -

Here's a working example based on Northwind's Orders table:

SELECT
    DatePart("q",[OrderDate]) AS TheQtr
  , Orders.CustomerID
  , Count(Orders.CustomerID) AS CountOfCustomerID
FROM
   Orders
GROUP BY
   DatePart("q",[OrderDate])
  , Orders.CustomerID
HAVING
   ((Not (Orders.CustomerID) Is Null));

You can test it out by replacing the table and field names with your own.

A hint: As a general rule, you should not include spaces in table and field names, e.g. Procedure Type should be ProcedureType. Failure to do so means the name must be surrounded by brackets [].

HTH - Bob