|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
February 28th, 2007, 12:26 AM
posted to microsoft.public.access.queries
|
|||
|
|||
MDB Queries
I have a query that shows the visits of my contacts. I would like to filter
it so that only one visit per contactID shows and is the most recent visit. right now all the visits of each contact show eg Contactid 1970 visitdate 02/14/2006 1970 03/14/2006 1970 04/14/2006 What I would like is just one contactid and just the most recent visit for each contact contactid 1970 visit date o4/14/2006. I have over 1000 visits in the query 
|
|
#2
February 28th, 2007, 03:16 AM
posted to microsoft.public.access.queries
|
|||
|
|||
|
MDB Queries
SELECT contactID, Max(visitdate)
FROM Contacts GROUP BY contactID ORDER BY contactID ; This will only work if the visitdate field is an actual Date/Time field. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "MUSIWA" wrote: I have a query that shows the visits of my contacts. I would like to filter it so that only one visit per contactID shows and is the most recent visit. right now all the visits of each contact show eg Contactid visitdate 1970 02/14/2006 1970 03/14/2006 1970 04/14/2006 What I would like is just one contactid and just the most recent visit for each contact contactid 1970 visit date o4/14/2006. I have over 1000 visits in the query
|
|
#3
February 28th, 2007, 07:03 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
MDB Queries
Thanks Jerry it worked very well. I was able to save the query and then add
other variables on it using joins with other tables. Well Done SIR!! "Jerry Whittle" wrote: SELECT contactID, Max(visitdate) FROM Contacts GROUP BY contactID ORDER BY contactID ; This will only work if the visitdate field is an actual Date/Time field. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "MUSIWA" wrote: I have a query that shows the visits of my contacts. I would like to filter it so that only one visit per contactID shows and is the most recent visit. right now all the visits of each contact show eg Contactid visitdate 1970 02/14/2006 1970 03/14/2006 1970 04/14/2006 What I would like is just one contactid and just the most recent visit for each contact contactid 1970 visit date o4/14/2006. I have over 1000 visits in the query
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
