Most Recent Entry

[betheball]

I have a table called Tests that contains 80+ tests that need to be performed on our system. A second table is Test_Data. It contains the test results. I need a query that will pull the most recent test data for each test in the Tests table. So supposing there are 83 tests, I should only get 83 results from my query. I am will need to join the tables as I need to get some of the descriptive information from the Tests table. Another problem I have is some tests have not been performed even once, so not every item in the Tests table has a corresponding entry in the Test_Data table.

Any thoughts on how to do this?

 

[TessB]

Create a query with TestID and Max[TestDate] (or whatever fields identify the particular test number or name, and how you know what is the most recent test date) Group the query on TestID so you only get one record per test.

Add this to your query and join TestID to your first table and TestDate to your Test Data table.

Good luck.
Tess

 

[betheball]

Thanks Tess. This works:

SELECT SERPTestInput.ID, Max(Results.testDate) AS maxDate
FROM Results RIGHT JOIN SERPTestInput ON Results.testID = SERPTestInput.ID
GROUP BY SERPTestInput.ID;

Now I have one more hangup. I need to add a couple of fields from the Results table, but because those fields differ from one another, they cause me to get the same ID more than once when I add them to the GROUP BY clause. However, if I don't add them to the GROUP BY clause, I get the aggregate function error. Is there a way around it?

 

[TessB]

Well, tell me what you want to do with this query so that I understand the purpose.
The results of the last run query, I thought, was the purpose...

What other fields are you trying to add and why?
And, these fields are from the results table, yes?

 

[betheball]

Let me tell you what I came up with and you tell me if there are any problems with it. It seems to work as I want, but the Results table only has a couple of test records in it so is hard to know for sure if this will continue to work. I used the Last function on all the fields I wanted to add to the query so the final result is this:

SELECT TestInput.ID, Last(testTitle) AS fldTitle, Last(testPriority) AS fldPriority, Last(testResults) AS fldResults, Last(testDescription) AS fldDescription, Last(Results.testStatus) AS fldStatus, Last(Results.testDate) AS maxDate FROM Results RIGHT JOIN TestInput ON Results.testID = TestInput.ID GROUP BY TestInput.ID;

As you can see, I needed a couple of fields from the TestInput table and a couple of fields from the Results table. The TestInput table simply contains generic information about each test that is scheduled to be performed, such as the name, description, system to be tested, etc. The results table is just as the name implies, the results of a test. For the report I am creating, the only thing that is relevant is the most recent test. However, the people who need the report wanted a status for every test we perform, even if it hasn't been run yet. Anyway, the above seems to work, but I would appreciate any input if someone sees a flaw in it.