Why is it not showing last note

[richo]

I am running a query for my advertisers notes and wish to get the last note on each advertiser. In the design query l am using the totals button and selecting 'last' for note and 'max' for date and id no. The only problem being is that it occassionally is not showing the last note for an advertiser but an older note. The 'max' works fine gor the date and id Why is this doing this, any help would be greatly appreciatted as it is vert frustating.

Thanks

Richo

 

[JimHorton]

richo,
What are you grouping by? The Last and Max will be records *within* the group-by set that satisfy the criteria--max will be the highest date in the group-by, but Last is not necessarily based on any date or time, or even alpabetic order, but the random order in which the record appears within the group-by set--which may or may not be the order that the record was entered.
So, if you have several notes with the same date, there's no way to know which one was entered first or last. Even if you have a single note per ID or Date, and you select Max ID or Max Date, the 'Last' field will not necessarily be from the same record within the group by set. You'd need a subselect to get this accurately.
--Jim

 

[richo]

Thanks Jim,

I can see why it is not working, can you explain more about a subselect for me

Thanks

Richard

 

[JimHorton]

richo,
I have no idea of the structure of your tables, but here's a general example, using a structure that may be close to yours. There are 2 possibilities here--one easy--if the structure is done in a way that would facilitate this, the other is more complex.

First:
tblAdNotes
NoteID Autonumber 'Primary key, high#r is latest Note--this makes it easy
AdvertiserID Number 'Foriegn key to Advertiser table
Note Text or memo 'the actual note
NDate Date 'The date/time of note (If date only, it's not unique)

First SQL example:

SELECT tblAdNotes.NoteID, tblAdNotes.AdvertiserID, tblAdNotes.Note, tblAdNotes.NDate
FROM tblAdNotes
WHERE tblAdNotes.NoteID In (select max(noteid) as dt from tbladnotes B where B.advertiserid = tbladnotes.advertiserid)

The above assumes NoteID is sequential, and therefore highest one is latest note--this makes it easy to do a sub-select. Note we only alias the subselect table as B, but you can always alias both.

Second syntax. This would be needed if NoteID was, for some reason, not sequential, or in cases where, say, notes from previous dates were entered at a later time, getting a higher ID but lesser date. Also, in this case the Note Date may a date only, thus many 'last notes', which requires that we can only randomly choose one--even if it's date/time, it still may not be unique. So the First() is what randomly chooses a NoteID that is in the set of highest dates. It's the best we can do if these are the circumstances:
SELECT A.NoteID, A.AdvertiserID, A.Note, A.NDate
FROM tblAdNotes AS A
WHERE A.NoteID In (SELECT First(B.NoteID) AS FirstOfNoteID
FROM tblAdNotes B
WHERE B.AdvertiserID=A.ADVERTISERID
GROUP BY B.ndate,B.advertiserid
HAVING B.NDate In (select max(ndate) from tblAdNotes C
WHERE C.advertiserID = B.Advertiserid) )

Above the same table is here 3 times, aliased as A, B, and C. The performance may be slow if the table is large and not indexed well. Let me know if this helps,
--Jim