[AccessD] Top values query problem

Check this out. It gives the top 6 values for each well. If a well has two
records for a date it only returns one record for that date. Let me know if
this does the trick.
Jim Hale

SELECT dbo_Prod_Tests.PT_Well, dbo_Prod_Tests.PT_Date,
dbo_Prod_Tests.PT_Status
FROM dbo_Prod_Tests INNER JOIN qryWells ON dbo_Prod_Tests.PT_Well =
qryWells.PT_Well
WHERE (((dbo_Prod_Tests.PT_Date) In (SELECT TOP 6 dbo_Prod_Tests.PT_Date
FROM dbo_Prod_Tests
 WHERE (dbo_Prod_Tests.PT_Well=qrywells.PT_well) ORDER BY
dbo_Prod_Tests.PT_Date DESC;)))
ORDER BY dbo_Prod_Tests.PT_Well, dbo_Prod_Tests.PT_Date;

where qryWells is a simple groupby query:

SELECT dbo_Prod_Tests.PT_Well
FROM dbo_Prod_Tests
GROUP BY dbo_Prod_Tests.PT_Well;

-----Original Message-----
From: Kaup, Chester [mailto:Chester_Kaup at kindermorgan.com]
Sent: Tuesday, May 09, 2006 4:35 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] Top values query problem


When I run the following query I get the top 6 values for only the first
PT_Well which makes sense the way the query grid makes the query. How do
I change the query to get the top 6 for each PT_Well? Thanks.

 

SELECT TOP 6 PT_Well, PT_Date, PT_Status

FROM dbo_Prod_Tests

GROUP BY PT_Well, PT_Date, PT_Status

ORDER BY PT_Well, PT_Date DESC;

 

Chester Kaup

Engineering Technician

Kinder Morgan CO2 Company, LLP

Office (432) 688-3797

FAX (432) 688-3799


***********************************************************************
The information transmitted is intended solely for the individual or
entity to which it is addressed and may contain confidential and/or
privileged material. Any review, retransmission, dissemination or
other use of or taking action in reliance upon this information by
persons or entities other than the intended recipient is prohibited.
If you have received this email in error please contact the sender and
delete the material from any computer. As a recipient of this email,
you are responsible for screening its contents and the contents of any
attachments for the presence of viruses. No liability is accepted for
any damages caused by any virus transmitted by this email.