Displaying Most Recent Record

[viper]

I have a database that is used to keep track of personnel. In this database I have a form that is a query of two tables lets call them "PersonneInfo" and "PersonnelNotes" for the sake of this question. PersonnelInfo contains demographic information about that person and the PersonnelNotes contains status notes organized by date entered. In my form I have displayed info from these tables for the purpose to create a continues list of personnel in the system. What I would like to do is add the DateEntered field from the PersonnelNotes table but only display the most recent entry not all entries. Any idea how this could be accomplished I have a feeling that this can be done in VB but I'm not sure how to put it together. Any help would be great and if you need further explanation please ask



Regards, Viper

[Rixxe]

I can't help with the showing a certain field in a form (as i don't really do VBA), however... to show the most recent records you could use this query:

 
Select top 5 MAX(Date) as MaxDate
From PersonnelNotes
Where MaxDate < Now()

It should display the top 5 records where the max date is not yet today, obviously you can change it around to allow it to be lesser or equal to today etc.

Good luck

[Rixxe]

You can also use this to get the record in the form:

http://allenbrowne.com/casu-15.html

[viper]

Where would you put this code in for a query? would this go right into the SQL statment of the PersonnelNotes query?

 
Select top 5 MAX(Date) as MaxDate
From PersonnelNotes
Where MaxDate < Now()

Thanks allot for the suggestion this might help but I'll have to try it out. However if any one else has a different route they would take I'd love to hear about it.

Regards, Viper

[Rixxe]

You can just run it as a single query, it's hard to tell not knowing the rest of what you are trying to accomplish...
Did you check out the link i posted in post #3?
It might help you adapt that in order to get what you want.

Hopefully someone else might be able to give you a straight answer.

Good luck

[viper]

You can just run it as a single query, it's hard to tell not knowing the rest of what you are trying to accomplish...

Ya sorry it’s a little hard to explain, but I have a form that combines multiple tables that shows a summery of the related records. To show you have personnel A-F in the system and I've picked out parts to these different forms (like First/Last name, ID #, current status and date updated). The problem is, and forgive me if I get the terminology crossed, but I have a one to many relationship between the personnel and the notes attached to them. I would like to show when the last date a note was add/updated on a particular person. Reason being that you have multiple notes on one person that can get rather lengthy, so I would want to only display the most recent note associated with that person.

Did you check out the link i posted in post #3?

It might help you adapt that in order to get what you want.

Hopefully someone else might be able to give you a straight answer.

Good luck

I did check it out, thanks for the post and your interest. I've never really worked with SQL so I'm at a loss when trying to use your code in SQL. Its quite different when compared to VBA. Again thanks for the help you've given and any other suggestions you might have.

Regards, Viper

[Rixxe]

Ya sorry it’s a little hard to explain, but I have a form that combines multiple tables that shows a summery of the related records. To show you have personnel A-F in the system and I've picked out parts to these different forms (like First/Last name, ID #, current status and date updated). The problem is, and forgive me if I get the terminology crossed, but I have a one to many relationship between the personnel and the notes attached to them. I would like to show when the last date a note was add/updated on a particular person. Reason being that you have multiple notes on one person that can get rather lengthy, so I would want to only display the most recent note associated with that person.

This makes more sense to me now, So all the notes are in a different table?
Or stored a different way?
If they are in a different table, you could just join the tables together and select the latest 'eventdate' that the note was entered.

Then you could pull which ever fields you want...

Or if you feel like posting the relevant parts of your code that might help.

[viper]

Yes, the Personnel_Info and the Personnel_Notes are two different tables. So that I can have multiple entries for personnel notes without having multiple personnel records.

Attached is the database with the related components of what i'm trying to do. If you need further detail just ask.

Thanks