Query finding earlist date among common numbers

[xrajncajnx]

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

[Arkadi]

I would think:

Please Login or Register  to view this content.

[xrajncajnx]

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.

[Arkadi]

Ok... how about:

Please Login or Register  to view this content.

Nevermind.... will still return all the records

[xrajncajnx]

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.

[Arkadi]

Please Login or Register  to view this content.

[xrajncajnx]

Please Login or Register  to view this content.

Still returning all the data.

[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. :/

[xrajncajnx]

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.

[xrajncajnx]

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...

[Arkadi]

Hmmm... I put the data you gave as an example into a table and I got the results you had expected... I assume your case now is a larger data set... any chance the testdate is null on the ones not coming down? can you give me some data that is not coming down as intended? I can try to duplicate the issue and troubleshoot.

[Arkadi]

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...

Yep makes sense... lemme try something...

[Arkadi]

Try:

Please Login or Register  to view this content.

[xrajncajnx]

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.

[Arkadi]

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

EDIT: only thing is... now we still get duplicates when the lowest TestDate is in more than one record... which result to return then? all? the only field that is not the same in those cases is TWR_No, don't know if you want them all, or just lowest or highest TWR?

[xrajncajnx]

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

Jackpot!!!


Works like a charm.

[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.

[xrajncajnx]

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.

[Arkadi]

First? or Lowest?

This gives the lowest TWR_No for the given criteria:

Please Login or Register  to view this content.

[xrajncajnx]

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.