I want to pull the last note for the notes table for a contact in a report.
I know I can use the Notes.timecol field to pull the date but what query should I write to pull up last notes for the contacts in Maximizer.

Any Ideas. please Help.

>I know I can use the Notes.timecol field to pull the date

Actually, no you can't. The TimeCol column only contains the time of the last note. The date portion is in DateCol. So even if you use

SELECT MAX(DateCol) AS MaxDate, MAX(TimeCol) AS MaxTime

you still won't get the result you want because MaxTime will return the highest time in that column regardless of the date. Therefore, if you have two notes

2004/12/03 11:53
2005/02/04 09:21

then the preceding query would return

2005/02/04 11:53

which does not correspond to either of those notes. In Pervasive.SQL the query you seek would look something like this:

SELECT 
   Client_Id,
   Contact_Number,
   MAX(
      TIMESTAMPADD(
         SQL_TSI_SECOND,
         (HOUR(TimeCol)*3600)+(MINUTE(TimeCol)*60)+SECOND(TimeCol),
         CONVERT(DateCol,SQL_TIMESTAMP)
      )
   ) AS LastNoteTimestamp
FROM "AMGR_Notes"
GROUP BY Client_Id, Contact_Number

HTH, Gord
Reporting Geek and author of the CreateUdfViews utility
http://www.regensys.com/CreateUdfViews

Actually I made a group based on the customer and used this in the group selection formulae:

{AMGR_Notes.TimeCol}= maximum ({AMGR_Notes.TimeCol},{AMGR_Client.Name}) and
{AMGR_Notes.DateCol}= Maximum ({AMGR_Notes.DateCol},{AMGR_Client.Name})

Question:

I also have UDF showing up in this report, now if I have two fileds in this UDF then I get a duplicate entry on the report.
Any suggestions

quote:

Question:

I also have UDF showing up in this report, now if I have two fileds in this UDF then I get a duplicate entry on the report.
Any suggestions

One approach is to move the fields from the detail band to a group footer and create an unbound field to concatenate the UDF values together. Something like this:

Global strPrevID as String, strList as String
WhileReadingRecords
If {AMGR_Client.Client_Id} <> strPrevID Then
    strList = {A_TESTUDF1.A_TESTUDF1}
Else
    strList = strList & ", " & {A_TESTUDF1.A_TESTUDF1}
End If
strPrevID = {AMGR_Client.Client_Id}
formula = strList