Constructing a User work queue. Help required.

[aexley]

I am currently in the process of altering my dbase so that when a user starts up it shows only those records which are due for action and which they have last worked on.

I have a query that selects the date filtered records fine. What I need is a query or code that looks at the [Comments History![Entered By:] field of my history table finds whoever worked on the record last and shows the appropriate records in the Site information form. My Site and History tables are linked by SiteID in a one-to-many relationship.

The only thing I have come up with so far is a query that selects all the Site records however many times that the current users name appears in the associated CommentHistory.EnteredBy field. This results in multiple copies of the same record including those in which the CurrentUser was not the last to use it.

I hope I'm making myself clear, it's seems a bit of a confused issue to me.

Thanks in advance for your help.

aexley

QFTD: "I loved her sturdy thighs, Her glassy eyes, I loved her permanent lok of surprise! She was my ru-uh-bber woman and I loved so." - Mitch Benn, Comedian

 

[MichaelRed]

I'm infering from your statemet that you save the list of users which access or modify a record in a field in the table.

Two issues come readily to mind.

1.[tab]You COULD use code to parse the list of user names and extract the last entry. This assumes that the names are stored in a test field with some common delimiter. I DO see some issues in this approach, one being the mumber of user names which may be stored in a text field and a second being the somewhat slow execution.

2.[tab]see faq181-291 (transaction log). This routine could be adapted to your use and provide the most recent User to modify a record through a select query. This also involves some issues. The code is from earlier ver of Ms. A. and probably needs some changes to work within your db. I am told that it is not the easiset piece of code to work with, so some degree of code work / ability would be needed. This version of the transaction log process rapidly generates LARGE numbers of records in the history table, so you also need to have some companion processes to prune the history table, and an awareness of the need to do so.



MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[aexley]

Thanks MichaelRed.

You're almost correct, my fault I should have made myself clearer. What I have is a 'Comments History' Table where users enter appropriate comments each time they work on the related record in my 'Site Information' table. Each comment record stores the username of the person who entered it as a record of who did what and when.

As far as recording user access to my main information tables - I don't. I do record changes to records in an Audit Trail table but that is general house-keeping more than anything.

What I need, and your second suggestion appears to approach this although I haven't looked at the FAQ yet, is to look at the most recent related comment for a particular 'Site Information' record, identify the user and if that matches the current user include that record in their work queue.

I know this is a bit back-asswards but I don't have a more reliable way of identifying who has worked on(and not just accessed)a particular record.

Just thinking on the fly - Would I need to group my comments by Site Information ID, then by Date, then by User in order to identify the appropriate associated record?

Thanks in advance.

aexley

QFTD:"When you get old you lie in bed all day, someone else makes all your food and your relatives never visit - So not all bad then" Jo Brand - Comedienne