Building Query that Selects Only Most Recent Entry 2

[Kerrikk]

I have a database that is used to manage trouble tickets. I am trying to build a report that shows all "open" tickets also showing only the latest journal entry for the ticket. There are numerous journal entries per ticket number, but the most recent is all I want on my report. I have tried building a query using the MAX function to select the most recent date but upon exectuting the query I get errors. For example "you tried to execute a query that does not contain the specified expression 'callID'as part of an aggregate function." Below is the SQL statement from my query.

SELECT dbo_Journal.CallID, dbo_Journal.EntryDate, dbo_Journal.EntryTime, dbo_Journal.EntryText, Max([dtlastmod]) AS expr1
FROM dbo_Journal;

I am sure it's something very obvious that I am missing, but I've been playing around with this for a few days to no avail. Any input would be greatly appreciated! Thanks! in advance. [sig][/sig]

 

[Phooey]

If you are using SQL view, change to design view and change the totals for each field except the MAX field. Grouped by should do the trick. [sig]<p>Phooey<br><a href=mailto:Andrew.j.harrison@capgemini.co.uk>Andrew.j.harrison@capgemini.co.uk</a><br>Otherwise known as Windy Bottom.[/sig]

 

[Kerrikk]

Thanks for your input Phooey! I tried that and I no longer get an error when running the query, but I am getting all journal entries for each trouble ticket not just the most recent. Any suggestions?

Thanks again! [sig][/sig]

 

[Phooey]

Have a look in the query porperties...
Change the Top Values property to 1. This will only display the top record..!!! [sig]<p>Phooey<br><a href=mailto:Andrew.j.harrison@capgemini.co.uk>Andrew.j.harrison@capgemini.co.uk</a><br>Otherwise known as Windy Bottom.[/sig]

 

[Kerrikk]

Thanks for all your help Phooey! This does not work either. Now I am getting 1 record only, the top ticket number. This does not give me the most recent entry &quot;dtlastmod&quot; (date last modified) for all ticket numbers. If you have any other suggestions, I'd definitely want to hear them.

Thanks again! [sig][/sig]

 

[Phooey]

Sort in Descending Date order or use

DMax("[dtlastmod]","dbo_Journal")

in the critera for the required date in the query.



Hope this helps...


[sig]<p>Phooey<br><a href=mailto:Andrew.j.harrison@capgemini.co.uk>Andrew.j.harrison@capgemini.co.uk</a><br>Otherwise known as Windy Bottom.[/sig]

 

[Kerrikk]

Well Phooey, sorted in decending order, I now get the last entry, but only the last entry. Not the last for all CallID's. Using the criteria that you suggested, the query will not run. I never get any error message, but access stops responding after I run the query. I've included the SQL for the query. Perhaps I have not done exactly as you advised. I'm no programmer, I'm network admin, but have been playing around with Access for 7 years and can usually muddle through it, but this one has me stumped.
Thanks again!! You are great to advise me on this.

SELECT dbo_Journal.CallID, dbo_Journal.EntryDate, dbo_Journal.EntryTime, dbo_Journal.EntryText, dbo_Journal.DTLastMod
FROM dbo_Journal
GROUP BY dbo_Journal.CallID, dbo_Journal.EntryDate, dbo_Journal.EntryTime, dbo_Journal.EntryText, dbo_Journal.DTLastMod
HAVING (((dbo_Journal.DTLastMod)=DMax(&quot;[dtlastmod]&quot;,&quot;dbo_Journal&quot));
[sig][/sig]

 

[LarryDeLaruelle]

If you are using a Totals query, have you tried a descending sort on the date field and selecting the Last record in that field (or, conversely) sorting ascending and selecting First record?

I have a similar situation where I want only the most recent record and this process has worked for me.

Hope this helps. [sig]<p>Larry De Laruelle<br><a href=mailto:larry1de@yahoo.com>larry1de@yahoo.com</a><br><a href= > </a><br> [/sig]

 

[Kerrikk]

Thanks Phooey and Larry for your help. Between what I learned from both of you, I now have a query that does what I want. YESSSSSSSSSSSSSS!!!! I tried my query including only the fields callID and dtlastmod. It worked. When I threw in any other field it gave me all 42,000 records instead of the 69 I am looking for. What worked for me was to group decending by callID then set sort at &quot;Last&quot; for all the other fields and BAM!! It worked!!! Don't think I would have ever figured out this one without your help. Thanks again guys!! (I'm saying guys because I'm assuming you are guys by your names. I had to say that because I am female and don't want anyone thinking I assume all IT people are guys. ) THANKS!! [sig][/sig]