Newbie question, looking for earliest test record for each s/n

[dan b in st. louis]

I'm new to SQL programming and haven't been able to incorporate the
suggestions I've found in other threads, so I apologize for asking a question
that's been answered before. I have a query that pulls data from 2 tables, a
serial number from 1 and a test date and status from another. But for some
serial numbers I've got more than 1 test record (first test was a failure).
I'm looking to keep only the first test record (by date/time) for each s/n.
I've been able to get the "first" test record, but I only get the 1 record,
not 1 for each s/n.

Here is my current query that pulls the s/n, test date and test status.

SELECT dbo_Unit.ModelNo, dbo_Unit.ProductCd, dbo_UnitTest.TestDate,
dbo_Unit.SerialNo, dbo_UnitTest.TestStatus
FROM dbo_UnitTest INNER JOIN dbo_Unit ON dbo_UnitTest.UnitId=dbo_Unit.UnitID
WHERE (((dbo_Unit.ModelNo)="408800-1"))
ORDER BY dbo_UnitTest.TestDate, dbo_Unit.SerialNo;

I appreciate your help in advance.

Dan

 

[Klatuu]

What you need is to get the Min of the test date. To do this, you will need
a Totals query. That is, on the menu bar in the query designer, click on the
Totals Icon. It looks like the Greek letter sigma. Then select Group By for
all except the test date and select Min for the test date.

 

[dan b in st. louis]

So here is my new query now:

SELECT dbo_Unit.ModelNo, dbo_Unit.ProductCd, Min(dbo_UnitTest.TestDate) AS
MinOfTestDate, dbo_Unit.SerialNo, dbo_UnitTest.TestStatus
FROM dbo_UnitTest INNER JOIN dbo_Unit ON dbo_UnitTest.UnitId = dbo_Unit.UnitID
GROUP BY dbo_Unit.ModelNo, dbo_Unit.ProductCd, dbo_Unit.SerialNo,
dbo_UnitTest.TestStatus
HAVING (((dbo_Unit.ModelNo)="408800-1"))
ORDER BY Min(dbo_UnitTest.TestDate), dbo_Unit.SerialNo;

But I'm still showing duplicate records by serial number.

dan

 

[Klatuu]

Try changing your join to a Left join.

 

[dan b in st. louis]

Dave, still the same results.

Dan

Try changing your join to a Left join.

 

[Dale Fye]

Dan,

Your Group By clause has multiple fields in it. Because of this, you will
get 1 record for each combination of values in these fields. My guess is
that the TestStatus field is what is probably causing the problem, since I
would expect the Model# and ProductCd to be unique for a particular SerialNo.

Try removing the TestStatus field from the the query, and see if you get
what you are looking for.

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[dan b in st. louis]

Dale, that did it, thanks. I had found a way to do it with 2 queries, but
this is much better, thanks again.

Dan

 

[Jerry Whittle]

Hi Dan,

I'm right across the river in Belleville. Anyway besides the sage advice
from Dave and Dale, there is another possibility. Either the
dbo_UnitTest.UnitId or dbo_Unit.UnitID fields need to be the primary key (or
at least unique) or you may have duplicates because of that. For example if
you have a two UnitID of "2" in both tables, you'll have 4 duplicate records
returned.