• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 268
  • Last Modified:

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

I am trying to make only the most up to date records display in a form for all the contact names in my DB based on Date.

example:

If there are 4 records with the same contact name in my database the query that loads the data into a form will need to do the following:

1) search the 4 records for the most recently added/updated record based on the date field.
2) Pull the found resulting record into the form.

This procedure must be repeated with all the records in the database until a complete list of only the most up to date records of contacts are loaded into the form.

I have been trying this and I cant seem to make it work - does anyone have any ideas?

Thanks for your help!
0
HudsonMarine
Asked:
HudsonMarine
  • 14
  • 10
2 Solutions
 
RusselRCommented:
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

Open in new window

0
 
HudsonMarineAuthor Commented:
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!
0
 
HudsonMarineAuthor Commented:
Also can you explain a little about what this queryy is doing exactly - thanks!
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
RusselRCommented:
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

Open in new window

0
 
HudsonMarineAuthor Commented:
Still getting:

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

0
 
RusselRCommented:
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.
0
 
HudsonMarineAuthor Commented:
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]
0
 
HudsonMarineAuthor Commented:
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!
0
 
RusselRCommented:
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]
0
 
HudsonMarineAuthor Commented:
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
0
 
RusselRCommented:
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
0
 
HudsonMarineAuthor Commented:
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!
0
 
HudsonMarineAuthor Commented:
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?
0
 
HudsonMarineAuthor Commented:
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!
0
 
RusselRCommented:
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]
0
 
RusselRCommented:
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.
0
 
HudsonMarineAuthor Commented:
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?
0
 
RusselRCommented:
Put your whole query in here...
0
 
HudsonMarineAuthor Commented:
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
0
 
RusselRCommented:
Where you put the date in to the query try putting it in ## instead of ''

e.g. #2008-01-01#

See below

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
0
 
HudsonMarineAuthor Commented:
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?
0
 
HudsonMarineAuthor Commented:
I also need to be able to add records to the form after it has opened and ran the querry - is this possible?
0
 
RusselRCommented:
You're clearly well outside of your knowledge base now: I'd suggest sitting down and learning about SQL before proceeding with the rest of your project.

To answer your questions though:
1) A "where" clause tells the query to only return those rows *where* a particular requirement is met.
WHERE [RepRec_d/t] > #2008-01-08# tells it only to return rows where your reprec date is greater than 8th jan 2008. If you wanted to include only those in 2008 you'd write two of those clauses:
WHERE [RepRec_d/t] >= #2008-01-01# AND [RepRec_d/t] < #2009-01-01#
This says, return records where your date is greater than or equal to 1st jan 2008 and where your date is less than 1st jan 2009 i.e. giving you only records from 2008.

2) This particular query shouldn't affect access's automated add records.

As I said: I suspect it's time for you to sit back and do the hard work of learning how databases and sql works as this "question" has already turned in to a "how to a write my software?" In the long run it will be quicker and vastly more beneficial for you to actually learn how it all works. You've not asked anything here that is beyond basic access knowledge that you should be able to teach yourself in a week or two.
0
 
HudsonMarineAuthor Commented:
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!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 14
  • 10
Tackle projects and never again get stuck behind a technical roadblock.
Join Now