[AccessD] Query question, choose TOP from Group?

Lawrence Mrazek lmrazek at lcm-res.com
Fri Jul 9 16:11:25 CDT 2010


Sigh, you're right, thanks Asger!

Larry Mrazek
ph. 314-496-1645
lmrazek at lcm-res.com
http://www.lcm-res.com


-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Asger Blond
Sent: Thursday, July 08, 2010 9:44 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Query question, choose TOP from Group?

Larry,

Don't see why you need to sort descending by PriceDate. Won't something like this do (since a max-aggregation on PriceDate should give you the most recent record)?

SELECT Company, MaterialName, Max(PriceDate) AS MaxPriceDate
FROM MyTable
GROUP BY Company, MaterialName

Asger

-----Oprindelig meddelelse-----
Fra: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] På vegne af Lawrence Mrazek
Sendt: 8. juli 2010 15:56
Til: 'Access Developers discussion and problem solving'
Emne: [AccessD] Query question, choose TOP from Group?

Hi folks:

I'd like to bind a listbox to use a query that returns the top record for
each item in a group, where the group is sorted by date descending.

Basically, I have:

Company
MaterialName
PriceDate

Basically, would like to group by Company, MaterialName, and sort desc by
PriceDate ... I only want to see the most recent record for each
Company/Material Name combination. Each Company / Material combo could have
a different date, so I can't limit it that way. 

Can I do this with a query? Another method? 

Thanks in advance!

Larry Mrazek
ph. 314-496-1645
lmrazek at lcm-res.com
http://www.lcm-res.com


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

No virus found in this incoming message.
Checked by AVG - www.avg.com 
Version: 9.0.830 / Virus Database: 271.1.1/2959 - Release Date: 07/08/10 01:36:00





More information about the AccessD mailing list