Top 10 customer visit dates

Mik Mak
Mik Mak used Ask the Experts™
on
In Access 2003, I'm trying to make a query to show the 10 latest visit dates for all customers in a table.  A normal select query for that table (that lists all the customers visit) looks like the one shown below - how can I modify this to select only the 10 latest for each customer ?

please advice

SELECT tblVISIT.CustID
FROM tblVISIT
ORDER BY tblVISIT.CustID, tblVISIT.VDate DESC;
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
SELECT tblVISIT.CustID
FROM (SELECT TOP 10 * FROM tblVISIT ORDER BY tblVISIT.CustID)
ORDER BY tblVISIT.CustID, tblVISIT.VDate DESC;

im a little rusty on my access tho, i will have to try it myself for a sec
Mik MakConsultant

Author

Commented:
Thank you for the speedy reply - but that only returns the top 10 CustID's (with the most visits I guess) - but I need ALL customers AND their 10 latest visit dates

Like

CustID      VDate
112233     01-10-2012
112233     01-09-2012
112233     01-08-2012
998877     10-10-2012
998877     09-09-2012
Owner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
I have a function (see below) that I use for this.  To use this function, your query would look like:

SELECT tblVISIT.CustID
FROM tblVISIT
WHERE RankInGroup(tblVisit.CustID) <= 10
ORDER BY tblVISIT.CustID, tblVISIT.VDate DESC;

The function looks like:
Public Function RankInGroup(ParamArray Groups() As Variant) As Integer

    Dim intLoop As Integer
    Static intRank As Integer
    Static GroupValues() As Variant
    
    ReDim Preserve GroupValues(UBound(Groups()))
    
    For intLoop = LBound(Groups) To UBound(Groups)
        If GroupValues(intLoop) <> Groups(intLoop) Then
            GroupValues(intLoop) = Groups(intLoop)
            intRank = 0
            Exit For
        End If
    Next
    
    intRank = intRank + 1
    RankInGroup = intRank
    
End Function

Open in new window

Commented:
if thats what you want, i would have designed your database using 2 different tables. to split off the customers from the visit dates.

The database has not really been designed in normalization procedures i guess but alas we always have to work on work from other people who made those databases ;-)

if you do have designed this database (no offence meant) the following site will show you the basics on normalization:
http://databases.about.com/od/specificproducts/a/normalization.htm

I think the solution above is the workable solution. Since you will need to script the database going through the table, select the first customer and then select the top 10 visit dates on that customer, then select the second customer, search for its top 10 visits and add to your result and so on...

I might have a go at this but if the script above works ;-) thats your solution
Mik MakConsultant

Author

Commented:
Hi

I was hoping for a query based solution, as I hoped that would be the quickest running, when dealing with large number of visits.

The database is in 3rd normal form :) - but in order to relate a customer to a visit, the visit table must of course include the customer key also - no offence meant either.

Commented:
Function ScanVisits(lngID As Long) As Boolean
DIm strSQL As STring
Dim rst As REcordset
strSQL = "Select TOP 10 tblVISIT.CustID, tblVISIT.VDate From tblVISIT Where tblVISIT.CustID = " & _
lngID & " tblVISIT.VDate DESC"

Set rst = currentdb.openrecordset(strSQL,dbopensnapshot)
If rst.Recordcount > 0 then
rst.MoveFirst
Do while not rst.EOF

rst.MoveNext
Loop
Endif
End Function
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Are you using SQL Server as a backend, or Access?

If using SQL Server, you might find that the query based approach is quicker, but I would venture that the function will run as quick or quicker than a pure query based approach with an Access backend, especially as the backend grows.

Commented:
posted my script to quickly havent checked it vs your database or vs your version of access but it should get you going if you just paste the script in a module and go from there.

If you run into any issues let me know
You can try this query:
SELECT tblVISIT.CustID, tblVISIT.VDate
FROM tblVISIT
WHERE (((tblVISIT.VDate) In (SELECT TOP 10 t.VDate FROM tblVISIT AS t WHERE t.CustID = tblVISIT.CustID ORDER BY t.VDate DESC)))
ORDER BY tblVISIT.CustID, tblVISIT.VDate DESC;

Open in new window

Mik MakConsultant

Author

Commented:
Thanks Fyed - it works as intended, although not query based - but I think you're right about the speed :)

Commented:
its the results that matter alas ;-) gratz on fyed and good luck bojerne :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial