Fix Circular Ref in Group By Query

[sxschech]

Was wondering if I can modify this into the existing query to avoid a circular ref. rather than having to run a separate query and join or refer to it some other way on a report.

SELECT count(*)
FROM qryAttritionTransfer 
WHERE FirstTerm<=(Select Max([FirstTerm]-2) From qryAttritionTransfer)

The purpose of this is to get a certain count of rows so that I don't have to hard code the number of rows into reports. The report uses this number to create an average in the footer. For example if there are 26 rows of data, the formula would result in 22 and the text box on the report would calculate ( txtCumGroup/22 ). If not in the query, what code/formula could be used on the report itself to figure out how many rows to divide by?

Here is the query (red is where I tried to add the subquery.)

SELECT "ALL" AS MainGroup, IIf(Right([incomingCohort],1)="S",Left([IncomingCohort],4)-1 & Right([incomingCohort],1),[IncomingCohort]) AS IncomingCohortSort, qryTransfer.IncomingCOHORT AS FallTerm, IIf(Right([incomingCohort],1)="S",Left([IncomingCohort],4)-1,Left([IncomingCohort],4)) AS FirstTerm, Sum(qryTransfer.RecordCount) AS BeginCohort, Sum([recordCount]-[FirstAttend]) AS Yr1Sem_Num, [Yr1Sem_Num]/[BeginCohort] AS Yr1Attrition, Sum([FirstAttend]-[SecondAttend]) AS Yr2Sem_Num, IIf(IsNull([Yr2Sem_Num]),0,1) AS Yr2Years, [Yr2Sem_Num]/[BeginCohort] AS Yr2Attrition, Sum([FirstYrDisqual]+[SecondYrDisqual]) AS Yr2CumDisq, Sum(qryTransfer.GradWIN2) AS GradWIN2, [red](SELECT count(*)
FROM qryAttritionTransfer 
WHERE FirstTerm<=(Select Max([FirstTerm]-2) From qryAttritionTransfer)) AS Expr1[/red]
FROM qryTransfer
GROUP BY "ALL", IIf(Right([incomingCohort],1)="S",Left([IncomingCohort],4)-1 & Right([incomingCohort],1),[IncomingCohort]), qryTransfer.IncomingCOHORT, IIf(Right([incomingCohort],1)="S",Left([IncomingCohort],4)-1,Left([IncomingCohort],4))
HAVING (((IIf(Right([incomingCohort],1)="S",Left([IncomingCohort],4)-1,Left([IncomingCohort],4))) Between 1993 And 2005));

 

[rac2]

Well that is daunting.

The many places where you have used

...
IIf(Right([incomingCohort],1)="S",Left([IncomingCohort],4)-1,Left([IncomingCohort],4))
...

leads me to suggest that you define a query which has that expression as a column and give it the name you have used as an alias, FirstTerm. Possibly there is another similar expression which needs a different name. Doing this will make it easier to read and create the query that you will use for the report.

Another thought is that possibly the HAVING clause should actually be a condition in the WHERE clause. There it will restrict the rows that are summarized. As it stands now, it restricts the final result after all rows have been summarized so that may be more computation than necessary.

GROUP BY "ALL" seems strange, since this is a constant, it has no effect on the result. Does it?

It is difficult to understand what FirstTerm means. Is that a year? And subtracting 2, does that mean, 2 years ago?

There is a function AVG which can be used in a GROUP BY query to obtain the average of a value over a group of rows. Could you use that instead of counting?

 

[sxschech]

Sorry for the confusion. Manipulating the data to produce the desired output was/is quite confusing to me too. However, the data we are reporting on are for semesters so 2006F and 2006S have to be sorted so that 2006S comes before 2006F and is grouped with 2005F (2005F and 2006S) to create the academic year. We are showing, on the report at this time 26 rows of data from 1993 thru 2006, however since 2006 hasn't completed yet, we were requested to only show averages for the years that are completed, thus averaging for the first 22 rows out of the 26 that display on the report. We also have a similar report that rather than display rows by semester, only display rows by year, so for that set of reports we calculate the avg on the first 11 rows of the visible data. Right now I have hard coded the number of rows to average with, but was hoping to make it more dynamic as its possible that when run in the future we may have more or less rows of data. The reason I have the "ALL" is that I have a number of queries all doing the same thing but for different groups and am using this field on the report to display what group is reporting. We have ALL, which is everything, then by College, then by Major, etc.

 

[rac2]

Suppose you create a new query named qryTransferB with a new column named AcademicYear using the IIf() statement to calculate it. That column has the value 2005 for rows with incomingCohort values of 2005F and 2006S.

This query

SELECT "ALL" AS MainGroup,
	 AcademicYear,
	 IncomingCohort,
	 Sum(RecordCount) AS BeginCohort,
	 Sum([recordCount]-[FirstAttend]) AS Yr1Sem_Num,
	 [Yr1Sem_Num]/[BeginCohort] AS Yr1Attrition,
	 Sum([FirstAttend]-[SecondAttend]) AS Yr2Sem_Num,
	 IIf(IsNull([Yr2Sem_Num]),0,1) AS Yr2Years,
	 [Yr2Sem_Num]/[BeginCohort] AS Yr2Attrition,
	 Sum([FirstYrDisqual]+[SecondYrDisqual]) AS Yr2CumDisq,
	 Sum(GradWIN2) AS GradWIN2,
FROM qryTransferB

WHERE AcademicYear Between 1993 And 2005;

GROUP BY "ALL",
	 AcademicYear,
	 IncomingCohort

ORDER BY "ALL",
	 AcademicYear DESC,
	 IncomingCohort DESC

should give you results like

MainGroup AcademicYear IncomingCohort BeginCohort
ALL 2005 2006S 505
ALL 2005 2005F 525
ALL 2004 2005S 437
ALL 2004 2004F 427
ALL 2003 2004S 303
ALL 2003 2003F 323


Another technique is to use a UNION query. This combines the rows resulting from two or more queries into a single set of records. The queries will have the same SELECT lists but they can have different GROUPing and WHERE clauses. This allows you to produce a breakdown such as the above with an grand total result.

For example

SELECT "1" AS "ReportingOrder",
       "ByYear" AS "MainGroup",
        AcademicYear,
        Sum(RecordCount) AS BeginCohort
FROM qryTransferB
GROUP BY "1",
         "ByYear",
         AcademicYear
WHERE AcademicYear Between 1993 And 2005;

UNION

SELECT "2",
       "GrandTotal",
       "_",
       Sum(RecordCount) AS BeginCohort
FROM qryTransferB
WHERE AcademicYear Between 1993 And 2005;

UNION

SELECT "3",
       "FirstSemester",
       "2006",
       Sum(RecordCount) AS BeginCohort
FROM qryTransferB
WHERE AcademicYear = 2006;


ORDER BY "ReportingOrder",
         "MainGroup",
         AcademicYear DESC

This illustrates the use of UNION with three differenct queries. All of them have four columns in the SELECT list. The "ReportingOrder" is a value to control the final sorting after the three queries are combined. My example would give semester-by-semester totals, followed by a grand total on one line, followed by a total for the first semester of the current year.

If you need some averages these can be produced in the query giving the breakdown. This eliminates the need to count anything. The average will be based on the right number of rows because the query is looking only at those.

HTH