Sort and display only the most recently added/updated records in a form based on contact name and date.

Assuming your table is table1 and fields are pername, updatedate something like this will do the trick:

SELECT * FROM table1
INNER JOIN
(
    SELECT pername, max(updatedate) as lastupdate FROM table1 GROUP BY pername
) as lastupdates ON table1.pername = lastupdates.pername

Select allOpen in new window

ok I tried what you posted and I keep getting this error when i load the form:

"You tried to execute a query that does not include the specified expression 'pername' as part of an aggregate function."

Any ideas why i am getting this and thanks for your help RusselR!

Also can you explain a little about what this queryy is doing exactly - thanks!

The query uses a subquery that returns the pername value and the latest date at which it was updated by using a group by clause.
The outer query selects all records from the table joining to the latestupdates table using the name and latestupdate date to choose the correct records only.

There was an error on my original query, this one should be better. Your error may have been caused by missing out the group by part which is essential.

Let me know how you get on!

SELECT * FROM table1
INNER JOIN
(
    SELECT pername, max(updatedate) as lastupdate FROM table1 GROUP BY pername
) as lastupdates ON table1.pername = lastupdates.pername AND table1.updatedate = lastupdates.lastupdate

Select allOpen in new window

Still getting:

"You tried to execute a query that does not include the specified expression 'pername' as part of an aggregate function."

I have just tried it in both sql server 2005 and ms access and both run it fine.

The error you are getting suggests you have missed out the "group by pername" part.

Can you confirm your database version etc... and copy + paste exactly what you are running in to here.

I am working with Access 2003 and here is my exact code!

SELECT * FROM [My Table Position - Linked to My Table Position Report]
INNER JOIN
(
    SELECT [Contactstr], max([RepRec_d/t]) as lastupdate FROM [My Table Position - Linked to My Table Position Report] GROUP BY [RepRec_d/t]
) as lastupdates ON [My Table Position - Linked to My Table Position Report].[Contactstr] = lastupdates.[Contactstr] AND [My Table Position - Linked to My Table Position Report].[RepRec_d/t] = lastupdates.[RepRec_d/t]

Oh wait - I have been reading other posts with this same error and i have a new question - Am I suppose to include ALL the field names that i want to be displayed in the form?

That would be a huge amount seeing that there is about 40-50 fields that display on this form that i want the querry to run on!

So how do i include them in the code -(what format) if this is the case?

Thanks again!

No you don't have to. The SELECT * in the outer query covers that for you.

Your ever is in re-writing the sub-query.

Where you have: SELECT [Contactstr], max([RepRec_d/t]) as lastupdate FROM [My Table Position - Linked to My Table Position Report] GROUP BY [RepRec_d/t]

It should be:
SELECT [Contactstr], max([RepRec_d/t]) as lastupdate FROM [My Table Position - Linked to My Table Position Report] GROUP BY [Contactstr]

OK RusselR It is now asking me to enter a parameter for :

lastupdates.RepRec_d/te

upon opening the form but hey at least we got rid of the other error - YAY! LOL

Any Ideas?

Thanks

Change the last bit from:

AND [My Table Position - Linked to My Table Position Report].[RepRec_d/t] = lastupdates.[RepRec_d/t]

to:

AND [My Table Position - Linked to My Table Position Report].[RepRec_d/t] = lastupdates.lastupdate

OK it loaded the form with about 992 records and i wont be sure this is correct until the actual contact name appears in the form correctly becuase right now thats all that is in the contact name field is this:

#Name?

What does this mean?

All other fields as far as i could see are loaded correctly!

The other thing I notice is that there are records in the resulting querried form which contain previous year dates (ie:2003,2004,2005,2006,...) How do I edit the supplied querry to exclude these years records from displaying in the form for the [RepRec_d/t] Field?

Is there a way I can make this an option when the form is opened:

weather to include:
Most recent (this years only)
all years
or all records where the record source is the table itself

thanks!

Corrr, you're making me work hard for this one!

#Name means there is something wrong with your access binding - i.e. the field name is wrong or the data type is incorrect etc...

To restrict the dates enter a where clause just before the GROUP BY.
e.g.  WHERE [RepRec_d/t] > '2008-01-01' GROUP BY [RepRec_d/t]

You can make it a filter option by placing a dropdownlist on the report with items in it with dates as the values and then use the filters part of the report system to filter your results.

I suggest you read the access docs for more info on that as it's a fairly large part of the software and not something I can easily cover here.

LOL sorry RusselR I really appreciate your help though!!

when i try what you gave me to exclude the date it says "the form action was closed"

so i put it back the way it was and now it at least opens the form and i fixed the missing contact name part - I had to re-establish a connection to the table.fieldname.

I will check access to do the other stuff i want but i still want to code where i can enter the years i want to exclude - something like this I guess:

WHERE [RepRec_d/t] > '2006-01-01','2005-01-01'', '2004-01-01'', '2003-01-01''  GROUP BY [Contactstr]

again when i tried your supplied code it said this:

"the form action was closed"

And wont even open the form.

Any Ideas?

Put your whole query in here...

SELECT * FROM [My Table Position - Linked to My Table Position Report]
INNER JOIN
(
    SELECT [Contactstr], max([RepRec_d/t]) as lastupdate FROM [My Table Position - Linked to My Table Position Report] WHERE [RepRec_d/t] > '2008-01-01' GROUP BY [Contactstr]
) as lastupdates ON [My Table Position - Linked to My Table Position Report].[Contactstr] = lastupdates.[Contactstr] AND [My Table Position - Linked to My Table Position Report].[RepRec_d/t] = lastupdates.lastupdate

ok that fixed it and how do i include more than one year? Like this:

WHERE [RepRec_d/t] > #2008-01-08#, #2007-01-01# GROUP BY [Contactstr]

or is there another way?

I also need to be able to add records to the form after it has opened and ran the querry - is this possible?

Thanks RullelR for your patience with me and I am continuously learning all this stuff as I am heading a project to turn 4 seperate elaborate excel spreadsheets into Access 2003 Db's. Thanks again!