select accounts.account_number, collection_activities.status, accounts.datetime_last_modified,
       operators.first_name, operators.last_name,
       collectors.first_name, collectors.last_name
       from collection_activities, accounts, collectors, operators
where collection_activities.account_id = accounts.account_id
and collection_activities.collection_work_list_id = collectors.collector_id
and accounts.modified_by_operator_id = operators.operator_id
and collection_activities.status = 'u'
and accounts.datetime_last_modified > dateadd("d", -14, getdate())
order by accounts.datetime_last_modified desc, collection_activities.collection_activity_ts desc

There are four tables in a vendors database that I'm trying to pull info out of:

Accounts: Contains the field Account_Number and Account_ID, as well as the DateTime formatted field Datetime_Last_Modified.

Collections_Activity: Also contains Account_ID, and the Status field which I am interested in searching on obviously. Also contains the field Collection_Work_List_ID

Collectors: Basically a lookup table to hold the name and some other info on the person who is set as a "Collector" for a particular record, using Collector_ID which = Collection_Work_List_ID in Collections_Activity.

Operators: Same Idea as Collectors except for associating a name with the Datetime_Last_Modified.

OK, so here's the situation I get with the above query. The Collection Activities table might have multiple entries for a particular Account_ID. For example you'd get some results that look like this:

Act # Status     Datetime          Name     Name
55555   U   2008-01-01 12:00:00  Person A Person B
55555   U   2008-01-01 12:00:00  Person A Person C
55555   U   2008-01-01 12:00:00  Person A Person C

Person A = Last person to modify the record. Person B = Last Collector to enter collection information of some kind that would trigger an entry into the Collection_Activities table. Person C = Employee who hasn't been here for 6 months, but is associated with older entries in the Collection_Activities table that are showing up in my query. Which is obviously confusing since I'm only able to display the Datetime_Last_Modified from the Accounts table, it looks like these older entries in Collection_Activities by Person C are recent activity.

The main problem here is that Collection_Activities doesn't have any kind of datetime field associated with when entries in this table were made. It has a timestamp field, but as I understand it that's not super useful in determining an actual date and time the entry was made. That collection_activities.collection_activity_TS field I'm doing the second DESC sort on is that timestamp field, which is just basically putting the earliest timestamps at the top. What I'm wanting to do is find some way to eliminate the "old" entries from the final query results.

Off hand I cannot think of any way to do this, since the vendor did not give me any kind of datetime reference in this table to rely upon. Help? Is this bastard-spawn-of-hell of an Inner Join I'm doing to blame, better way to do this?