Sql select question

[scalda34]

This in Access 2003. Pardon me if this is in the wrong spot, but I hoped someone could help.

I have a table with multiple records that have a text represented date field. The date is in a format like: "06/01/2009" I need to select all the fields from the table but only the 6 oldest rows that fall in a range for each group of "DocType, PayTo, ContactName, ContactNumber, DocFooter, PQBName, LetterDate, RetireeFirstNam e, RetireeLastName , Address1, Address2, City, State, ZIP, PQBSSN, EmployerCode" ordered by the DateDue.

Like: Select only records with a date range 01/01/2009 - 12/01/2009, and of those only select the 6 oldest entries for each grouping.

I have monkeyed with this for a bit and am having no luck. I know this is pretty basic, but I just cant seem to make this work. Here is the SQL select I use to get the date from the table now.

SELECT c.DocType
     , c.PayTo
     , c.ContactName
     , c.ContactNumber
     , c.DocFooter
     , c.PQBName
     , c.LetterDate
     , c.RetireeFirstName
     , c.RetireeLastName
     , c.Address1
     , c.Address2
     , c.City
     , c.State
     , c.ZIP
     , c.PQBSSN
     , c.EmployerCode
     , c.AmountDue
     , c.DateDue
     , Right(c.[DateDue],4)+Left(c.[DateDue],2)+Mid(c.[DateDue],4,2) AS SORTDATE
  FROM COUPONS c
 ORDER
    BY c.DocType
     , c.PayTo
     , c.ContactName
     , c.ContactNumber
     , c.DocFooter
     , c.PQBName
     , c.LetterDate
     , Right(c.[DateDue],4)+Left(c.[DateDue],2)+Mid(c.[DateDue],4,2)

To restate the problem: the query is returning too many rows, I ONLY want six rows for each 'group' of distinct values for the seven columns in the order by...

Any help would be very appreciated!

[FishVal]

Hello, scalda.

Do you have PK field in your table or any unique field or fields combination?

P.S. I'm quite impressed with creativity of your approach towards making ordering criteria from text date. However, the same could be achieved with CDate() function since your field is very suitably formatted.

[Megalog]

Here's a simple example of what you need:

SELECT TOP 6 [your fields]
FROM [your tables]
ORDER BY [SortDate];

[NeoPa]

Hi Scalda. Welcome to Bytes!

I'm not sure why you see this as a simple problem. It is not remotely so. I suspect I can produce something using Subqueries in SQL, but I will need to do some checking first of the underlying concepts.

Congratulations on posting your SQL so readably by the way. Not everyone has the sense to do that. Noticed and appreciated :)

Please remember for the future though, to use the [ CODE ] tags.

PS. Tags are done as matching pairs where the opening one is surrounded by [...] and the closing one by [/...]. A set of buttons is available for ease of use in the Standard Editor (Not the Basic Editor). The one for the [ CODE ] tags has a hash (#) on it. You can choose which editor to use in your profile options (Look near the bottom of the page).

[NeoPa]

I tried, but I can't find a way to do this in SQL (so much for simple).

The TOP predicate waorks over the whole SELECT query it pertains to. Linking in a subquery, which itself uses TOP, fails as there is no way to tell it to rerun for every value(s) that you want to GROUP BY. Sorry.