keeping the history of the data but also updating them

[webisti]

Hi I am trying to create a query (LATE_PO) that will pull out data from the db to show all opened PO for a given vendor
I want to populate a table (OPEN_PO) with the results of the query.
in a form I am using the table as a source to check these PO data and update comments from vendor when required.
This process will happen on a regular basis let`s say once per week.
How can I make it possible that when a PO is appearing more than once,(the query is an append query) to have it copied in the table and show in the form the lattest comments on it, not to have a double entry and to rewrite the comments again?


Can that be done via a VBA or it is needed a set of queries.
Thanks for your inputs

[aytee111]

Let me see if I have it right - there is a table containing PO's, one record per PO. There is a table containing comments with a date which will be a one-to-many relationship. You want there to be one record per PO with the latest comments in your append query.

Create a query that will contain the PO and the latest comment. Then bring that query in to your append query and join by PO, that way you will get only the latest comment.

[webisti]

Actually I solved it with index usage. (indexing the query with the table where I do have the records)
Works cool.