Query finding earlist date among common numbers

I have a query set up to pull all data from a table that has a "PSAT" result in the STATUS column. The results or columns I am pulling are the STATUS, TP_NO, TESTDATE and TWR_NO. The TP_NO's are often duplicated many times and what I am trying to find with the query is only the earliest dates that recieved a PSAT result for each test number.

Here is the code I currently have:

Please Login or Register  to view this content.

Attached is an example of the result I get & the results I want

ProcedureLog.xlsx

Originally Posted by Arkadi

I would think:

Please Login or Register  to view this content.

I get an error message stating:

You tried to execute a query that does not include the specified expression as part of an aggregate function or grouping.

Possible cause:


You did not enter an aggregate function in the TRANSFORM statement.

If it helps, I posted the question on another forum and got the following example to try, but ultimately I end up getting the same error as posted above.

Please Login or Register  to view this content.

Originally Posted by Arkadi

Please Login or Register  to view this content.

Still returning all the data.

Originally Posted by Arkadi

Yeah made a mistake sorry... try this one though, it should actually work:

Please Login or Register  to view this content.

Sorry about the earlier misleading attempts, didn't think it through and sometimes things seem simpler than they are. :/

Ahh... So VERY close! It's now paring it down like I'm looking for, however it's excluding a handful of the TP numbers. All the criteria is there in the selected fields, it's just not returning the results for them.

Ok, I pulled the WHERE line and the results it gave me made me realize that what it's doing is if an earlier date exists in the TestDate field that is listed as anything other than "PSAT" then it just doesn't return a result at all. So, if I have a REJECT on 5/5/13 and then a PSAT on 5/8/13 under the same TP_NO then it just doesn't return a result insted of finding the next earliest PSAT.

Hope that makes sense...

Test Data sample.xlsx

Ok, here's a quick & dirty on the test sample data table and based on what I'm looking for, the results I need.

Originally Posted by Arkadi

xrajncajnx, did you try my last post... I think it might solve it.

Jackpot!!!


Works like a charm.

Originally Posted by Arkadi

You are OK with multiple results then for the same TP_No if the lowest date is in more than one record? Some results come up twice, with a different TWR_No.

Yes, multiple results are not an issue since the report I am dumping the data to has a vlookup fuction that is searching for that TP NO. As long as all the entries have the same date I don't have to worry with it just grabbing the first one it finds. If you can modify it where it just gives the first TWR number that would work too.

Originally Posted by Arkadi

First? or Lowest?

This gives the lowest TWR_No for the given criteria:

Please Login or Register  to view this content.

That works as well. Again, thanks a ton.