complicated query issue with many calculated fields (1 Viewer)

CJ_London said:

first - I presume that results for students who you are not annotating should also be ignored.

Yes, I just annotated one of the students who had two tests on the same day and no follow-up tests yet as an example.

>>second - not shown in your example but can you have the situation of same student, same testtype, but 3 records? and if so do you need to compare record 2 with record 1 and record 3 with record 2? Or just 1 and 3 and ignore record 2?

The database will certainly have students with 3 or more tests of the same testtype on different dates moving forward, but I only need to compare the most recent 2 tests. The older tests need to be kept in the database for reference but do not factor into the calculation.

>>Would also be helpful to show the original table and some example data plus the sql of your qryTwoLastTests query

(1)I did not realize that I had given the SQL to qryTwoLastTestsWORKING instead of qryTwoLastTests (oops!) Here is qryTwoLastTests:

Code:Copy to clipboard

SELECT qryTesting.StudentID, qryTesting.TestDate, qryTesting.TestTypeID, qryTesting.TestType, qryTesting.Form, qryTesting.SSNum, qryTesting.GENum, qryTesting.NextForm
FROM qryTesting INNER JOIN qryTesting AS qryTesting_1 ON qryTesting.StudentID=qryTesting_1.StudentID
WHERE (((qryTesting.TestDate) In (SELECT TOP 4 [TestDate] FROM qryTesting WHERE [StudentID]=[qryTesting_1].[StudentID] ORDER BY [TestDate] desc)))
GROUP BY qryTesting.StudentID, qryTesting.TestDate, qryTesting.TestTypeID, qryTesting.TestType, qryTesting.Form, qryTesting.SSNum, qryTesting.GENum, qryTesting.NextForm
HAVING (((Count(qryTesting.StudentID))>=2));

(2)I am attaching a pic of some example data from tblTesting, as well as an intermediate query qryTesting that converts the original SSs and GEs from text (because some of the scores have "+" in them and had to be converted to data type number). Here is the SQL for qryTesting as well:

Code:Copy to clipboard

SELECT tblStudents.StudentID, tblTesting.TestDate, tblTestType.TestTypeID, tblTestType.TestType, tblTesting.Form, tblTesting.SS, Val([SS] & "") AS SSNum, tblTesting.GE, Val([GE] & "") AS GENum, tblTesting.NextForm
FROM tblTestType INNER JOIN (tblStudents INNER JOIN tblTesting ON tblStudents.StudentID=tblTesting.StudentID) ON tblTestType.TestTypeID=tblTesting.TestTypeID
GROUP BY tblStudents.StudentID, tblTesting.TestDate, tblTestType.TestTypeID, tblTestType.TestType, tblTesting.Form, tblTesting.SS, tblTesting.GE, tblTesting.NextForm
ORDER BY tblStudents.StudentID, tblTesting.TestDate DESC;

>>With regards slow running queries, this is usually due to poor or non existent indexing and use of domain functions.
Indexes are a black box to me. Should I index any field called by the domain functions?

>>I don't understand why you are grouping I would have expected you query to be more like


SELECT a.StudentID, a.TestDate, a.TestTypeID, a.TestType, a.SSNum, a.GENum, a.NextForm
FROM qryTwoLastTests AS a INNER JOIN qryTwoLastTests AS b ON a.StudentID = b.StudentID AND a.TestTypeID=.[TestTypeID] and [a].TestDate<.[TestDate]

Click to expand...

 SELECT L.*, P.*
 FROM tblTesting L INNER JOIN tblTesting P 
     ON L.StudentID=P.StudentID AND L.TestTypeID=P.TestTypeID
 WHERE 
     L.TestDate=(SELECT TOP 1 TestDate FROM tblTesting WHERE StudentID=P.StudentID AND TestTypeID=P.TestTypeID ORDER BY TestDate Desc) 
        AND P.Testdate IN =(SELECT TOP 2 TestDate FROM tblTesting WHERE StudentID=P.StudentID AND TestTypeID=P.TestTypeID ORDER BY TestDate Desc) AND L.TestDate<>P.TestDate
 ORDER BY L.StudentID, L.TestTypeID

Indexes are a black box to me. Should I index any field called by the domain functions?

Click to expand...

CJ_London said:

Sorry - change the select statement to just SELECT * (both records are on the same line, but current select is only selecting one of them)

Again I see no reason for grouping qryTesting or qryTwoLastTest since you are not summing etc

Alternatively based on your original table try

Code:Copy to clipboard

 SELECT L.*, P.*
 FROM tblTesting L INNER JOIN tblTesting P 
     ON L.StudentID=P.StudentID AND L.TestTypeID=P.TestTypeID
 WHERE 
     L.TestDate=(SELECT TOP 1 TestDate FROM tblTesting WHERE StudentID=P.StudentID AND TestTypeID=P.TestTypeID ORDER BY TestDate Desc) 
        AND P.Testdate IN =(SELECT TOP 2 TestDate FROM tblTesting WHERE StudentID=P.StudentID AND TestTypeID=P.TestTypeID ORDER BY TestDate Desc) AND L.TestDate<>P.TestDate
 ORDER BY L.StudentID, L.TestTypeID

Ensure fields StudentID., TestTypeID and TestDate are indexed

Aliases L equates to Last and P to Previous

domain functions do not use indexes, one of the reasons they are slow. Also by grouping you lose the benefit of indexing

Click to expand...

tried to execute a query that does not include the specified expression 'TestDate' as part of an aggregate function

Click to expand...

CJ_London said:

sorry about the = was copy and pasting

with regards your error

its generated in group by queries when the field used in the criteria is not either aggregated in some way or grouped - The solution is to create another column (in this case for a.testdate) and put b.testdate in the criteria and also change the groupby in the dropdown to where

But as I said - there is no need for the group by at all since you aren't aggregating anything and from the definition of your data there should not be any duplicates (if there were, use SELECT DISTINCT instead - in the query properties change Unique Values to Yes)

Click to expand...

So if you have a query where you're aggregating a field (and therefore need the totals row in the Query Design Window), but you want to show other fields in the returned record, you have to do a work-around?

Click to expand...

CJ_London said:

Not saying that at all - just use group by for the other fields if you want to show them. I'm saying you can't apply a criteria to a grouped by column which references a field that has not been included

I wouldn't call it a workaround and no, it is not a flaw in access - just the way group by queries generally work in databases.

If you look at the sql of a group by query it will be something like

SELECT fld1, sum(fld2) as sumoffld2
FROM myTable
WHERE fld3=xxx
GROUP BY fld1
HAVING fld1=zzz

When you put a criteria in the group by or sum column, it appears in the HAVING part of the query - and is 'actioned' after everything has been grouped/aggregated - so if you haven't included that other field, it can't be referenced as a criteria.

However when you put a criteria in a WHERE column it appears in the WHERE part of the sql and is actioned before being grouped - which also means it is general faster because the grouping will then only be applied on the data selected from the where statement, rather than grouping the entire table before applying the criteria.

Further down the line you will find the same thing with aliases and calculated fields

a normal select query

SELECT fld1, fld2/10 as calcfield, calcfield*1.5 as calcfield2
FROM myTable

works

but change it to a group by query and calcfield2 cannot reference calcfield1, instead for calcfield2 you would use

fld2/10 *1.5 as calcfield2

nearest analogy I can think of at this time of night is think of the fields being pieces of paper (perhaps a report) and the group by a folder.

You put the pieces of paper in the folder and give it to your boss. If one of those pieces of paper references another that is not in the folder, the boss becomes confused....That's the HAVING part

Click to expand...

Simon_MT said:

There is another way. Use VBA to run a SQL statement after input by the user and if the count = 1 set a flag to PreTest if the count = 2 set a flag to Post or something along those lines. Basically this is triggered after a Save Record on the Form Close or Add another Record.

I do this to total up transaction in four subsystems into one analysis table. Everything stays in sync. There are thousands of records in these subsystems and yes I could use SQL but this is much more efficient.

Simon

Click to expand...