Greatest 3 records for each category

  • Thread starter Thread starter Jacinto Muchine
  • Start date Start date
J

Jacinto Muchine

Hello,

In an Access 2000 Db, I have two tables: tblSales
and tblSalesContacts. Where one record from tblSales can
have many records in tblSalesContacts. I would like to
create a query that only returns 3 most recent
salescontacts of each sale.

I have tried SELECT TOP but it does not return the top 3
of each sale, it only retuns the top 3 of the entire
tblSalescontacts.

The relevant fields in each table are:
tblSales:
SalesID - pk
SalesDescription

tblSalesContacts:
SalesContactsID - pk
SalesID - fk
ContactDetails
ContactDate

Thanks for any help
Jacinto
 
Dear Jacinto:

For maximum learning potential, I go about this in pieces.

First, we can get SalesContacts from tblSalesContacts for each SalesID
ordered by SalesContactID and by ContactDate:

SELECT SalesID, ContactDetails, ContactDate
FROM tblSalesContacts
ORDER BY SalesID, ContactDate

From this you want to see only 3 rows for each SalesID:

SELECT SalesID, ContactDetails, ContactDate
FROM tblSalesContacts SC
WHERE ContactDate IN (Select TOP 3 ContactDate
FROM tblSalesContacts SC1
WHERE SC1.SalesID = SC.SalesID
ORDER BY ContactDate)
ORDER BY SalesID, ContactDate

If you want to see and/or sort by SalesDescription, you need only join
tblSales to this:

SELECT S.SalesDescription, SC.SalesID, SC.ContactDetails,
SC.ContactDate
FROM tblSalesContacts SC
INNER JOIN tblSales S ON S.SalesID = SC.SalesID
WHERE SC.ContactDate IN (Select TOP 3 ContactDate
FROM tblSalesContacts SC1
WHERE SC1.SalesID = SC.SalesID
ORDER BY ContactDate)
ORDER BY SC.SalesID, SC.ContactDate

If there are multiple rows for the same SalesID and ContactDate in
tblSalesContacts, the meaning of TOP 3 can be ambiguous. For example,
if there are 4 records for today, then the TOP 3 will be all 4 of
these. You could have prevented this by having SalesID/ContactDate as
a unique index of tblSalesContacts if you don't wish to allow this.
You might want additional contacts on the same date entered by having
the user type in more ContactDetails in the existing row rather than
adding another. This may or may not be a reasonable extension of your
design. But, if ContactDate is not unique for each SalesID then you
can have ties (less likely if you're recording a time in each
ContactDate, but even then still possible).

Without uniqueness, TOP 3 may return more than 3. Of course, it can
also return fewer than 3 (if there aren't at least 3 rows in
tblSalesContacts for some SalesIDs). You could arbitrarily filter
this by choosing the lower numbered SalesContactsID values. Until
your TOP 3 is according to a unique combination of columns you cannot
ensure there won't be more than 3 rows that result.

Remember to consider that, if you arbitrarily choose to reduce such
"ties" to 3 rows, someone may complain that the results don't show a
very recent contact that should have been included. You might be
better off showing more than 3 rather than arbitrarily reducing the
results.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Hi Tom,

Thank you very much for your help!
You fished me out from hell...

Jacinto
 
Back
Top