Query finding earlist date among common numbers

[rajncajn]

I have a query set up to pull all data from a table that has a "SAT" result in the STATUS column. The results or columns I am pulling are the STATUS, TEST NUMBER, TEST DATE and TEST IDENTIFICATION NUMBER. The test numbers are often duplicated many times and what I am trying to find with the query is only the earliest dates the I recieved a SAT result for each test number.

This is an example of the results I'm getting with my query:

STATUS	TEST NO	TEST DATE	TEST IDENT #
sat	45-1	5/5/13	1
sat	45-1	9/10/13	2
sat	45-1	10/8/14	3
sat	48-3	1/2/15	4
sat	48-3	5/8/15	5
sat	48-3	7/30/15	6
sat	48-3	8/8/13	7
sat	92-8	7/2/12	8
sat	92-8	11/11/15	9
sat	92-8	1/8/12	10

This is an example of what I actually need:

STATUS	TEST NO	TEST DATE	TEST IDENT #
sat	45-1	5/5/13	1
sat	48-3	8/8/13	7
sat	92-8	1/8/12	10

[orange]

Try (untested)

SELECT  *
FROM Table
WHERE Table.status ="sat"  and 
testdate = (select Min(W.[testdate])   from  Table as W)

[rajncajn]

Try (untested)

SELECT  *
FROM Table
WHERE Table.status ="sat"  and 
testdate = (select Min(W.[testdate])   from  Table as W)

It's giving me a Syntax error for missing operator in the last two lines.

Here is my existing code:

SELECT ProcedureLog.Status, ProcedureLog.Test No, ProcedureLog.TestDate, ProcedureLog.Test Ident #
FROM ProcedureLog
WHERE (((ProcedureLog.Status)="SAT"))
ORDER BY ProcedureLog.Test No;

[JoeM]

If you have spaces or special characters like "#" in field names, you need to enclose the field name in square brackets, i.e.

SELECT ProcedureLog.Status, ProcedureLog.[Test No], ProcedureLog.TestDate, ProcedureLog.[Test Ident #]
FROM ProcedureLog
WHERE (((ProcedureLog.Status)="SAT"))
ORDER BY ProcedureLog.[Test No];

[rajncajn]

If you have spaces or special characters like "#" in field names, you need to enclose the field name in square brackets, i.e.

SELECT ProcedureLog.Status, ProcedureLog.[Test No], ProcedureLog.TestDate, ProcedureLog.[Test Ident #]
FROM ProcedureLog
WHERE (((ProcedureLog.Status)="SAT"))
ORDER BY ProcedureLog.[Test No];

While helpful it doesn't answer the original question or correct the syntax error. The actual header text in my table is TWR_NO, but in order to have it make sense for the purpose of this thread I changed it to Test Ident #. Where the syntax error is coming from is where I'm trying to incorporate the change orange suggested. The actual code reads:

SELECT ProcedureLog.Status, ProcedureLog.TP_No, ProcedureLog.TestDate, ProcedureLog.TWR_No
FROM ProcedureLog
WHERE (((ProcedureLog.Status)="PSAT"))
ORDER BY ProcedureLog.TP_No;

This is what I'm trying to change it to per the suggested code:

SELECT ProcedureLog.Status, ProcedureLog.TP_No, ProcedureLog.TestDate, ProcedureLog.TWR_No
FROM ProcedureLog
WHERE (((ProcedureLog.Status)="PSAT")) and
testdate = (select Min(W.[testdate])   from  Table as W)
ORDER BY ProcedureLog.TP_No;

[orange]

You will need a Group BY line before the Order By
Here is a query with similar construct (it uses max, not Min)

SELECT tblStatsProdSales.ProdCode
, Max(tblStatsProdSales.Sales) AS MaxOfSales
, tblStatsProdSales.Region
FROM tblStatsProdSales
WHERE (((tblStatsProdSales.[sales])=
           (select Max( sales) from  tblStatsProdSales as x
            where x.region = tblStatsProdSales.region)))
GROUP BY ProdCode, Region;

[rajncajn]

You will need a Group BY line before the Order By
Here is a query with similar construct (it uses max, not Min)

SELECT tblStatsProdSales.ProdCode
, Max(tblStatsProdSales.Sales) AS MaxOfSales
, tblStatsProdSales.Region
FROM tblStatsProdSales
WHERE (((tblStatsProdSales.[sales])=
           (select Max( sales) from  tblStatsProdSales as x
            where x.region = tblStatsProdSales.region)))
GROUP BY ProdCode, Region;

As much as I hate to be a pain I just can't seem to get this to work. Constantly getting errors trying to plug & play the info in my database into your example.

[orange]

Post a copy of the database --- compact and repair , then zip

[rajncajn]

Post a copy of the database --- compact and repair , then zip

Unfortunately I cannot because it is full of propietary information.

[orange]

Can you export some sample data to csv and post the data?
We can offer much if we can not see the table design, the data and the exact query you are using.
What can you supply to readers?

[rajncajn]

Can you export some sample data to csv and post the data?
We can offer much if we can not see the table design, the data and the exact query you are using.
What can you supply to readers?

Thankfully was able to find a solution earlier today with some additional help. I still want to thank you for trying to help me with this. You offered a great starting point which ultimately got my question answered. For reference, here is what I ended up with:

SELECT plog.Status, plog.TP_No, plog.TestDate, Min(plog.TWR_No) AS MinOfTWR_No
FROM (SELECT TP_No, min(TestDate) AS mindate FROM ProcedureLog WHERE Status='PSAT'
GROUP BY TP_No)  AS blog INNER JOIN ProcedureLog AS plog ON (blog.mindate=plog.TestDate) AND (blog.TP_No=plog.TP_no) GROUP BY plog.Status, plog.TP_No, plog.TestDate
HAVING (((plog.Status)='PSAT'))
ORDER BY plog.TP_No;

[orange]

I'm glad that you have it resolved. Good stuff!