Data Results by Quarter

[MarieD]

I have an Access DB query that returns the following results by using this formula,
Qtr: DatePart("q",[DateofProcedure]).
I need a report that will count how many of these records are in each quarter and by running quarter. Our quarter one is Oct, Nov, Dec.
Please show me how to do this.

Procedure Date Pacer or ICD New or Change Qtr
3 /2 /2009 Pacer New 1
2 /16/2009 Pacer New 1
5 /21/2009 Pacer New 2
5 /20/2009 Pacer New 2
6 /9 /2009 Pacer New 2
8 /18/2009 Pacer New 3
9 /4 /2009 Pacer New 3
11/2 /2009 Pacer New 4
11/17/2009 Pacer New 4

 

[jdraw]

I have an Access DB query that returns the following results by using this formula,
Qtr: DatePart("q",[DateofProcedure]).
I need a report that will count how many of these records are in each quarter and by running quarter. Our quarter one is Oct, Nov, Dec.
Please show me how to do this.

Procedure Date Pacer or ICD New or Change Qtr
3 /2 /2009 Pacer New 1
2 /16/2009 Pacer New 1
5 /21/2009 Pacer New 2
5 /20/2009 Pacer New 2
6 /9 /2009 Pacer New 2
8 /18/2009 Pacer New 3
9 /4 /2009 Pacer New 3
11/2 /2009 Pacer New 4
11/17/2009 Pacer New 4

Could you please give an example of what output you expect using the sample data as input?

 

[MarieD]

I exported a portion of the query to excel. Will this work?

 

[MarieD]

I exported some of the data to excel. Will this work?

 

[jdraw]

I exported some of the data to excel. Will this work?

Is this a homework assignment?

 

[MarieD]

I'm sorry, I do not understand the implication of the question. I work for AnMed Health Heart & Vascular. I am working on information that will help with our accreditations.

 

[jdraw]

I'm sorry, I do not understand the implication of the question. I work for AnMed Health Heart & Vascular. I am working on information that will help with our accreditations.

Not a problem, the intent is to assist with Access issues and problems. Quite often students are seeking answers to homework assignments.

One way to get what you need is to do an aggregate query to group your data, such as

SELECT ProcedurePacer.Pacer_ICD, Count(ProcedurePacer.New_Changed) AS CountOfNew_Changed, ProcedurePacer.Quarter
FROM ProcedurePacer
GROUP BY ProcedurePacer.Pacer_ICD, ProcedurePacer.Quarter;

I have used ProcedurePacer as the name of your Table. It has fields:
DateofProcedure
, Pacer_ICD
, New_Changed and
, Quarter

You should use the Report Wizard in Access ( I'm using Access 2003) and base your report on your query.

Here is a link to SQL Aggregate functions
http://www.w3schools.com/SQl/sql_functions.asp

Good luck.

 

[MarieD]

If I group the New or Change column by count, the results is incorrect information.
SQL from what I think you suggested:
SELECT [Pacer QA tbl].DateofProcedure, [Pacer QA tbl].PacerorICD, Count([Pacer QA tbl].NewImplantorGeneratorChange) AS CountOfNewImplantorGeneratorChange
FROM [Pacer QA tbl]
GROUP BY [Pacer QA tbl].DateofProcedure, [Pacer QA tbl].PacerorICD;

I've attached a sample of the results (Test 3 Quarter New Or Change)
~~~~
I've been working on this for quite sometime and someone else had me going down this path:

SELECT [Pacer QA tbl].DateofProcedure, [Pacer QA tbl].PacerorICD, [Pacer QA tbl].NewImplantorGeneratorChange, IIf(Month([ProcedureDate]) In (10,11,12),"Quarter 1",IIf(Month([ProcedureDate]) In (1,2,3),"Quarter 2",IIf(Month([ProcedureDate]) In (4,5,6),"Quarter 3","Quarter 4"))) AS Qtr
FROM [Pacer QA tbl]
GROUP BY [Pacer QA tbl].DateofProcedure, [Pacer QA tbl].PacerorICD, [Pacer QA tbl].NewImplantorGeneratorChange, IIf(([ProcedureDate]) In (9,10,11),"Quarter 1",IIf(([ProcedureDate]) In (12,1,2),"Quarter 2",IIf(([ProcedureDate]) In (3,4,5),"Quarter 3","Quarter 4")))
HAVING ((([Pacer QA tbl].PacerorICD)="Pacer"))
ORDER BY [Pacer QA tbl].PacerorICD;

There are 2 problems with this query that I cannot resolve.
1. All the data is classified as Quarter 4.
2. The query is asking for a parameter value ProcedureDate which I do not want to key in a date.

I attached the results, Test 2 Quarter New or Change.

 

[jdraw]

If I group the New or Change column by count, the results is incorrect information.
SQL from what I think you suggested:
SELECT [Pacer QA tbl].DateofProcedure, [Pacer QA tbl].PacerorICD, Count([Pacer QA tbl].NewImplantorGeneratorChange) AS CountOfNewImplantorGeneratorChange
FROM [Pacer QA tbl]
GROUP BY [Pacer QA tbl].DateofProcedure, [Pacer QA tbl].PacerorICD;

I've attached a sample of the results (Test 3 Quarter New Or Change)
~~~~
I've been working on this for quite sometime and someone else had me going down this path:

SELECT [Pacer QA tbl].DateofProcedure, [Pacer QA tbl].PacerorICD, [Pacer QA tbl].NewImplantorGeneratorChange, IIf(Month([ProcedureDate]) In (10,11,12),"Quarter 1",IIf(Month([ProcedureDate]) In (1,2,3),"Quarter 2",IIf(Month([ProcedureDate]) In (4,5,6),"Quarter 3","Quarter 4"))) AS Qtr
FROM [Pacer QA tbl]
GROUP BY [Pacer QA tbl].DateofProcedure, [Pacer QA tbl].PacerorICD, [Pacer QA tbl].NewImplantorGeneratorChange, IIf(([ProcedureDate]) In (9,10,11),"Quarter 1",IIf(([ProcedureDate]) In (12,1,2),"Quarter 2",IIf(([ProcedureDate]) In (3,4,5),"Quarter 3","Quarter 4")))
HAVING ((([Pacer QA tbl].PacerorICD)="Pacer"))
ORDER BY [Pacer QA tbl].PacerorICD;

There are 2 problems with this query that I cannot resolve.
1. All the data is classified as Quarter 4.
2. The query is asking for a parameter value ProcedureDate which I do not want to key in a date.

I attached the results, Test 2 Quarter New or Change.

A couple of things. I was using quarter the way you used it in the dateDiff function. That gives the Calendar quarter. Since your quarter, which I will call OurQuarter starts in Oct, you will need to use:

OurQuarter = (5-Quarter) to get the correct quarter for you.
I have made my column names the same as yours - I think.

DateofProcedure
PacerorICD
NewImplantOrGeneratorChange
Quarter ( representing the calendar quarter where calendar starts in Jan)

and I am going to add
OurQuarter to represent your "fiscal quarter or operating quarter" starts Oct

The problem with the other query asking for a parameter is because of a spelling mistake. Your field is DateofProcedure whereas someone else has used ProcedureDate. If you substitute DateofProcedure for ProcedureDate, the query may well have worked. At least it wouldn't be asking for a parameter.

I only had the test data you gave in first post, and I was not calculating the "Quarter" you use properly.
I will look into the other test data and get back.

 

[jdraw]

Further to last post, here are a few comments/recommendations.

Remove the spaces from your Table and field names. This is a common Access recommendation. Suggest your table is renamed PacerQAtbl.

I would use 2 queries to get the Procedures by OurQuarter. I think the 2 queries will be easier to understand

First, a query to get the count of procedures;

SELECT PacerQAtbl.DateofProcedure, PacerQAtbl.PacerorICD
, PacerQAtbl.NewImplantOrGeneratorChange
, Count(PacerQAtbl.NewImplantOrGeneratorChange) AS CountOfNewImplantOrGeneratorChange, PacerQAtbl.OurQuarter
FROM PacerQAtbl
GROUP BY PacerQAtbl.DateofProcedure, PacerQAtbl.PacerorICD
, PacerQAtbl.NewImplantOrGeneratorChange, PacerQAtbl.OurQuarter;

Second, a query against the first query, to get the appropriate Sums for Procedures by OurQuarter

SELECT ProcedurePacerWithCounts_qry1.PacerorICD, ProcedurePacerWithCounts_qry1.NewImplantOrGeneratorChange, Sum(ProcedurePacerWithCounts_qry1.CountOfNewImplantOrGeneratorChange) AS SumOfCountOfNewImplantOrGeneratorChange, ProcedurePacerWithCounts_qry1.OurQuarter
FROM ProcedurePacerWithCounts_qry1
GROUP BY ProcedurePacerWithCounts_qry1.PacerorICD, ProcedurePacerWithCounts_qry1.NewImplantOrGeneratorChange
, ProcedurePacerWithCounts_qry1.OurQuarter
ORDER BY ProcedurePacerWithCounts_qry1.OurQuarter;

These 2 queries could be done in one, but I think it would be more confusing than 2 queries.

SELECT [%$##@_Alias].PacerorICD, [%$##@_Alias].NewImplantOrGeneratorChange, [%$##@_Alias].OurQuarter
, Sum([%$##@_Alias].CountOfNewImplantOrGeneratorChange) AS NumberOfProcedures
FROM [SELECT PacerorICD, NewImplantOrGeneratorChange
, Count(NewImplantOrGeneratorChange) AS CountOfNewImplantOrGeneratorChange
, OurQuarter
FROM PacerQAtbl
GROUP BY PacerorICD, NewImplantOrGeneratorChange,OurQuarter]. AS [%$##@_Alias]
GROUP BY [%$##@_Alias].PacerorICD, [%$##@_Alias].NewImplantOrGeneratorChange
, [%$##@_Alias].OurQuarter;

I can't test your other test data since the excel sheets do not contain all fields.

If you want to attach data from your table, I can test that against these queries.

Why do you use Excel for reports?

 

[MarieD]

I do use Access reports. I just figured out to export the report in rich text. Is that what you are looking for?

The query is working fine in that it is selecting the data and identifying the quarters correctly. Two things:How do I get a report to list the procedure name as the row heading and the 4 quarters as column headings? And I now need to separate the totals by quarter for 'Change' and for 'New'. How do I separate this? Attached is an example of the expected report outcome.

Query:
SELECT [Pacer QA tbl].DateofProcedure, [Pacer QA tbl].PacerorICD, [Pacer QA tbl].NewImplantorGeneratorChange, IIf(Month([DateofProcedure]) In (10,11,12),"Quarter 1",IIf(Month([DateofProcedure]) In (1,2,3),"Quarter 2",IIf(Month([DateofProcedure]) In (4,5,6),"Quarter 3","Quarter 4"))) AS Qtr
FROM [Pacer QA tbl]
GROUP BY [Pacer QA tbl].DateofProcedure, [Pacer QA tbl].PacerorICD, [Pacer QA tbl].NewImplantorGeneratorChange, IIf(([DateofProcedure]) In (9,10,11),"Quarter 1",IIf(([DateofProcedure]) In (12,1,2),"Quarter 2",IIf(([DateofProcedure]) In (3,4,5),"Quarter 3","Quarter 4")))
HAVING ((([Pacer QA tbl].PacerorICD)="Pacer"))
ORDER BY [Pacer QA tbl].PacerorICD;

The report needs to look like this:
Column Headings Q1 Qt Q3 Q4

Row Heading
Pacer New
Pacer Change

 

[jdraw]

I tried to use your query and I modified one of the data records to have 1 Change record (5/20/2009). Your sample report doesn't show what you want in the value cell, so I stuck in the DateofProcedure.

I have this code

TRANSFORM PacerQAtbl.DateofProcedure
SELECT PacerQAtbl.NewImplantOrGeneratorChange
FROM PacerQAtbl
WHERE (((PacerQAtbl.PacerorICD)="Pacer"))
GROUP BY PacerQAtbl.NewImplantOrGeneratorChange, PacerQAtbl.PacerorICD, PacerQAtbl.NewImplantOrGeneratorChange, PacerQAtbl.DateofProcedure
ORDER BY PacerQAtbl.PacerorICD, PacerQAtbl.NewImplantOrGeneratorChange
PIVOT IIf(Month([DateofProcedure]) In (10,11,12),"Quarter 1",IIf(Month([DateofProcedure]) In (1,2,3),"Quarter 2",IIf(Month([DateofProcedure]) In (4,5,6),"Quarter 3","Quarter 4")));

Hope this is enough to give some ideas.


You may want to look up some Access CrossTab query samples on Google.
Good luck.

 

[MarieD]

Where you have placed the [dateofprocedure] in the quarter value fields, I need the 'count' of procedures placed under each quarter, ie. how many 'pacer new' procedures were done in Q1, Q2, etc and how many 'pacer change' procedures were done in Q1, Q2.

Is this possible?

 

[jdraw]

Where you have placed the [dateofprocedure] in the quarter value fields, I need the 'count' of procedures placed under each quarter, ie. how many 'pacer new' procedures were done in Q1, Q2, etc and how many 'pacer change' procedures were done in Q1, Q2.

Is this possible?

I went back to my original posts, and started with the
single query( the one I recommended against initially)

SELECT [%$##@_Alias].PacerorICD, [%$##@_Alias].NewImplantOrGeneratorChange, [%$##@_Alias].OurQuarter
, Sum([%$##@_Alias].CountOfNewImplantOrGeneratorChange) AS NumberOfProcedures
FROM [SELECT PacerorICD, NewImplantOrGeneratorChange
, Count(NewImplantOrGeneratorChange) AS CountOfNewImplantOrGeneratorChange
, OurQuarter
FROM PacerQAtbl
GROUP BY PacerorICD, NewImplantOrGeneratorChange,OurQuarter]. AS [%$##@_Alias]
GROUP BY [%$##@_Alias].PacerorICD, [%$##@_Alias].NewImplantOrGeneratorChange
, [%$##@_Alias].OurQuarter;

adjusted it to make a Crosstab query with counts

to get

TRANSFORM Sum([%$##@_Alias].CountOfNewImplantOrGeneratorChange) AS NumberOfProcedures
SELECT [PacerorICD] & [NewImplantOrGeneratorChange] AS [Procedure]
FROM [SELECT PacerorICD, NewImplantOrGeneratorChange
, Count(NewImplantOrGeneratorChange) AS CountOfNewImplantOrGeneratorChange
, OurQuarter
FROM PacerQAtbl
GROUP BY PacerorICD, NewImplantOrGeneratorChange,OurQuarter]. AS [%$##@_Alias]
GROUP BY [PacerorICD] & [NewImplantOrGeneratorChange], [%$##@_Alias].NewImplantOrGeneratorChange
PIVOT "Q" & [OurQuarter];

which makes the output as shown in the attached image.

 

[MarieD]

I'm not experienced enough to understand what I should replace the %$##@_Alias with, or do I?

Access is stating that there is an invalid backeting of name 'SELECT PacrorICD, New Implant

TRANSFORM Sum([%$##@_Alias].CountOfNewImplantOrGeneratorChange) AS NumberOfProcedures
SELECT [PacerorICD] & [NewImplantOrGeneratorChange] AS [Procedure]
FROM [SELECT PacerorICD, NewImplantOrGeneratorChange
, Count(NewImplantOrGeneratorChange) AS CountOfNewImplantOrGeneratorChange
, OurQuarter
FROM PacerQAtbl
GROUP BY PacerorICD, NewImplantOrGeneratorChange,OurQuarter]. AS [%$##@_Alias]
GROUP BY [PacerorICD] & [NewImplantOrGeneratorChange], [%$##@_Alias].NewImplantOrGeneratorChange
PIVOT "Q" & [OurQuarter];

 

[MarieD]

Can you tell me why this SQL does not return dates that are of the same date, ie. two Pacer New procedures done on 1/12/2009. The SQL only returns one of the procedures.

I have attached the output from this query and the out put from a query that just pulls the data not involving quarters. There should be 2 attachments.

SELECT [Pacer QA tbl].DateofProcedure, [Pacer QA tbl].PacerorICD, [Pacer QA tbl].NewImplantorGeneratorChange, IIf(Month([DateofProcedure]) In (10,11,12),"Quarter 1",IIf(Month([DateofProcedure]) In (1,2,3),"Quarter 2",IIf(Month([DateofProcedure]) In (4,5,6),"Quarter 3","Quarter 4"))) AS Qtr
FROM [Pacer QA tbl]
GROUP BY [Pacer QA tbl].DateofProcedure, [Pacer QA tbl].PacerorICD, [Pacer QA tbl].NewImplantorGeneratorChange, IIf(([DateofProcedure]) In (9,10,11),"Quarter 1",IIf(([DateofProcedure]) In (12,1,2),"Quarter 2",IIf(([DateofProcedure]) In (3,4,5),"Quarter 3","Quarter 4")))
HAVING ((([Pacer QA tbl].PacerorICD)="Pacer") AND (([Pacer QA tbl].NewImplantorGeneratorChange)="New"))
ORDER BY [Pacer QA tbl].PacerorICD;

 

[namliam]

Because your grouping it...
GROUP BY [Pacer QA tbl].DateofProcedure, [Pacer QA tbl].PacerorICD, [Pacer QA tbl].NewImplantorGeneratorChange, IIf(([DateofProcedure]) In (9,10,11),"Quarter 1",IIf(([DateofProcedure]) In (12,1,2),"Quarter 2",IIf(([DateofProcedure]) In (3,4,5),"Quarter 3","Quarter 4")))

Which will guarantee you only get one unique row